Posts by JimFuller1

Re: Finding Duplicates

Nichola08,

Are you perhaps wanting to know what the changes are so you can make the same changes to your list? Is your concern that if you copy and paste the entire list, you will mess up some formulas? Perhaps you could use a dynamic range name to help with your list.

If you want a quick work around, try this on a clean sheet. Copy and paste both list in one contiguous list. Use the [Data][Filter][Advanced Filter...] command from the Excel menu to copy the unique records to another location.

Jim

Re: 2-dimensional Date Range With Dynamic Copy And Paste

Quote

I hate to say it, but I'm starting to think that the best way to do this...

Unless you want to use the Pivot Table.....

Good luck,
Jim

Re: Loop Through And Delete Row Where Match

w424637,

If you want a simple one time way:

1) add a helper column to the data
2) do a vlookup formula to find the ID in the short list like so:
=vlookup(lookup_value,table_array,col_index_num,FALSE)
3) sort the data by the helper column
4) delete the matches

Jim

Re: Loop Beetwin Textbox

HxF,

If you are saying that you have information stored in text boxes that you have to retrieve on a regular basis, you need to think about not storing your data in text boxes so you don't have this problem.

If you're saying you have a bunch of text boxes and you want to retrive the information once before you stop collecting data in text boxes, you could look at the link below. It may help you to understand how to loop through the text boxes.

http://www.ozgrid.com/forum/showthread.php?t=31825

Jim

Re: Retreiving Data With User Interface

Abi,

It kind of depends on how the data is layed out. If it's in database format (Columns for fields, Rows for records), you could probably use built in functionality to retrieve data. The example you gave could be easily achieved with Autofilter for example. Database functions are also useful in this situation but tend to be more complicated. Keep in mind that the assumption behind almost all of Excel's native functionality is that the data is laid out in database forma. This will give you and your users the richest set of features and shortest learning period.

If the data is not organized like a database, you will probably need a user form and some VBA code.

Jim

Re: Taking Next Highest Number

Muppley,

Hard to say how to go on this one. Are you saying that the "next highest number" is based on all dates in the first column of all sheets in the workbook - sort the dates and use up the numbers in the list - change the number returned when a date in between the dates already entered is entered? Does that last sentence even make sense? See why it's going to be hard to help you?

Maybe we should start with why you want to do this???

Jim

Re: Compare Cell Values Between Multiple Columns

grimnir,

I'm still a little fuzzy but, here's a couple of thoughts anyway.

The frequency (.FRQ) can be searched in a vlookup formula on the first sheet and the word two columns to the right can be returned like so:
=VLOOKUP(".FRQ",Sheet1!$A$1:$C$100,3,FALSE)

The result of this formula will tell you whether to look in the Quarterly place the Yearly place or the Monthly place. You can specify the places with a range name and that gets you the ability to refer to the place by finding the word ".FRQ" on the first sheet.

Does that help?

Jim

Re: Charts Fail To Update

cr07,

Welcome aboard!

It sounds like you're trying to create a workbook that has circular references in it thus the need for manual calculation. If not, then we need to remove the need for manual calculation if there are too many formulas, etc. Can you post a sample sheet that will show what you're really trying to do?

Jim

Re: Compare Cell Values Between Multiple Columns

grimnir,

I bet that makes perfect sense to you but, I can't quite get it. Can you send a sample sheet that has the expected results? It sounds like you want to identify the data range for a chart based on what format a cell uses. I'm guessing there's a better way to do it though so post a sample please.

Jim

Re: Chart Data Range

codyfairdark,

A non-macro solution would be to create a dynamic range name for the chart data. That way when you specify what range the chart uses, you put in the range name you created and the chart will automatically adjust from then on.

Jim

Re: Syntax Error

Joshua Ng,

Since it worked for me in part, try posting the whole code intact and let's see what we can see.

Jim

Re: Too Much Data Per Cell, Search/copy/paste Specific Values?

effang,

I thought I did let you know it wasn't clear. I can't open the file. I can't help until I see the data layout. Unless, have you tried [Data][Text To Columns...] from the Excel menus? Otherwise, repost the file.

Jim

Re: Syntax Error

Something strange may be happening that is affecting our communication. When I check the e-mail notification of your message, I see a different formula than when I look at the web site.

e-mail says:

ActiveCell.FormulaR1C1 = _
              "=VLOOKUP(RC[-8],MyName!rRange,2,FALSE)"

web says:

ActiveCell.FormulaR1C1 = _ 
"=VLOOKUP(RC[-8],MyName!MainRange,2,FALSE)"

So, which is it?

Re: Too Much Data Per Cell, Search/copy/paste Specific Values?

effang,

Welcome aboard. Your file is corupted or something and I can't get it to open. The extension is "xlsx". Can you post the file again. I think the solution will be pretty straightforward if the data has a predictable format.

Jim

Re: Autofilter Several Columns And Then Top 25

davidm,

If your code works on one sheet with autofilter, then you will never overcome the problem. You see, there can only be one instance of autofilter per sheet. This means that each line of code operates separately on the one instance of autofilter. After the code is executed, you have one set of data filtered by all four criteria.

To overcome this, copy the results of one autofilter to another sheet, filter that data, copy the results to another sheet, filter, copy, filter, etc. Until you are satisfied.

Jim

Re: Syntax Error

Joshua Ng,

If you are getting the error in the results of the formula in the spreadsheet, you may be trying to paste the formula in a column that is not 8 columns from column A. I used the formula you posted with success. Can you be more specific about what the error is?

Jim

Re: Name Of An Enumeration Value

joe31415,

I can't tell what you're talking about yet. Are you using terms you learned from another programming language such as "enumeration variable"? Are you writing a VBA macro? Do you want a formula for a spreadsheet?

Jim

Re: Measurement Conversion Format Cell

doyleday,

You may want to insert a helper column that contains a value for each customer related to how they want the value displayed. For example the value 1 could represent display it like this "00-00-0/8", 2 could represent "00Ft. 00In", etc. Then you can write the formula to display the results as an IF statement like so:

=IF($A$2=1,TEXT(INT(ROUND(A1*2/3,0)/8),"00-")&TEXT(MOD(ROUND(A1*8,0)/8,12),"00-0/8","the other format")

Jim

PS - Convert feet to inches by multiplying by 12.

Re: Protect Cells Based On Computer's Date

jj_88,

How do you feel about a macro?

Jim

Re: Measurement Conversion Format Cell

doyleday,

A custom format of "00-00-0/8" will get you a fraction for the last two places and the inches to the left in the first 00 place but, I don't think there is a way to tell Excel to only go up to 12 and then use feet to the left in another 00 place. Can you explain more about why such a formatting is desirable? For example, if you could use a formula or not?

Jim