Calculating And Comparing Values In Any Two Columns Selected

[Pejayuk]

Hi,

I'm not sure if it's possible, but this is what I'm trying to achieve.

I have a sheet with around 40 columns. Each column has a heading (name of a different company). Going down the first column are the following labels (Gas: Unit Price, Cost Monthly, Quarterly, Annually). This is repeated for Electricity and Standing Charges. The final 3 rows add the total cost (monthly, quarterly, annually).

I'm not sure if I need something like a pivot table, but I would like to select any two columns and the differences between them for each value would be calculated and displayed.

Any suggestions much appreciated.

 

[onlyadrafter]

Hello,

One way would be the following:

On another sheet in B1 have DATA, VALIDATION with a LIST, and select source sheet cells B1:AN1 (the company names).

In Cell B2 have the following formula:

=INDEX(Sheet1!$A$1:$AN$16,ROW(),MATCH(Sheet2!B$1,Sheet1!$A$1:$AN$1,0))

change Col AN to last required column.

you can then copy these cells over into Col C.

You can then compare the two columns in Col D, with whatever formulae you need.

Is this of any use?

 

[sandy666]

it's possible to see here structure of your data? (in table format)

 

[Pejayuk]

Thanks for the replies. Having a few problems with the formula you suggested. Tried on a new sheet but getting error "You may not use references to other worksheets and workbooks for data validation criteria". Maybe because I am using an old version of excel.

I then tried changing your formula a bit so I could keep it on the original sheet with extra columns at the end. This keeps giving #REF !

I then tried you exact formula on a new sheet (entering company names without using validation) and still getting #REF !

Lastly I tried formula on a very simple test sheet with just 4 columns and 10 rows (no blank rows) and same error.

Not sure if I am doing something wrong but I will keep trying.

 

[Pejayuk]

Hi,

Just wanted to say thanks again for the method. It's working perfectly now. I guessed I was doing something wrong and finally noticed I had got the row numbers mixed up!

Also got round the data validation problem by linking the company names from the first sheet to the second and now the drop-down works a treat.