Row number problem,exact rownumbers to calculate subtraction from two sheets

[Dornish]

Hi,

I have a broad range of data of 600 columns and 2360 rows and I'd need to calculate the remainder of two values from different sheets (to third sheet). I know the row number for every column but the problem is that every column has a unique row number so it would take much time to manually change the right row number to the formula. Would it be possible that excel looks up the right row number for the formula. And is it possible without VBA-coding.


Example: First column (D)

Row number 2032

Calculation:
=Sheet1!D2032-Sheet2!D2032

Second column (E)

Row number 1903

Calculation:
=Sheet1!E1903-Sheet2!E1903

Third column (F)

Row number 1772

Calculation:
=Sheet1!F1772-Sheet2!F1772

 

[jarjarbingie]

say you have the row numbers in row 1:

D = indirect("Sheet1!"&address(D$1,column())) - indirect("Sheet2!"&address(D$1,column())) then drag the formula to the right..

 

[Dornish]

Thanks jarjarbingie, though I couldn't get it work since I am using a Finnish based excel. However, I have an excel example of the problem if it helps.

Thanks!

 

[jarjarbingie]

hmmm.. I haven't tried attaching a file here yet.. but if there's a way I could access the file, that would help a lot..

 

[Dornish]

Yes that would help alot, is it possible here to attach a file or should it be sent via email or..?

 

[s hal]

I'm guessing you have 2 sheets that have similar row labels but in a different order between the two sheets, yes?

You would want to use a combination of index and match and add them together (you could also use vlookup and counta() for the lookup position locking the beginning cell. The latter would assume that your columns are the same, which may not always be the case (which is why I lean towards index/match whenever I do these sorts of things. In any case, those look like the following:

=Index(values_array_sheet1,match(row_label_sheet3,row_label_array_sheet1,0),match(column_label_sheet3,column_label_array_sheet1,0)+Index(values_array_sheet2,match(row_label_sheet3,row_label_array_sheet2,0),match(column_label_sheet3,column_label_array_sheet2,0)

OR

=Vlookup(row_label_sheet3,row_labels_&_value_array_sheet1,counta($B$1:B$1),FALSE)+Vlookup(row_label_sheet3,row_labels_&_value_array_sheet2,counta($B$1:B$1),FALSE)

(and I dont know foreign language differences, so you might need to modify these to suit the version differences)