formulas with dynamic references to cells

**excelnerd1** · 02-06-2009, 05:52 PM

Hello all,

Below is my problem. My spreadsheet contains two sheets. One sheet has 3 columns with "name" "number" and "product" in columns A,B,C respectively. Sheet2 contains "name" and "amount in" columns A,B respectively. I have formula in sheet1 of column c to multiply the value in sheet1!B with Sheet2!B. The problem is the column amount in sheet2 dosn't always have to be in column B. next time the data is refreshed it can be in column C. The only common thing will be that column "name" and "amount" will be next to each other and the column names will always stay the same. How can i acheive this using excel formulas. Macros will be my last option. I have attached a sample spreadsheet.

Any help is greatly appreciated.

**DonkeyOte** · 02-06-2009, 06:01 PM

Are the headers always in the same row on Sheet2 ?

**excelnerd1** · 02-06-2009, 06:05 PM

yes, the headers will always remain the same.

**DonkeyOte** · 02-06-2009, 06:07 PM

so in what row will they appear ?

**DonkeyOte** · 02-06-2009, 06:14 PM

If we assume headers always appear in row 2

Please Login or Register  to view this content.

Then assuming you're not deleting columns etc on Sheet2 you could use:

Please Login or Register  to view this content.

Not very elegant but it should work... alter ranges as required.

**excelnerd1** · 02-06-2009, 06:28 PM

sorry,

i misunderstood your question. The header "name" will always be the same in both sheets.

**excelnerd1** · 02-06-2009, 06:35 PM

if you don't mind can you please post an example sheet?, the row header will always remain the same, only the column order changes.

**DonkeyOte** · 02-06-2009, 06:44 PM

Just insert the formulas provided into your sample file as advised.

If you change the headers in row 2 on Sheet2 you should find the values on Sheet1 update accordingly.