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

#### Dornish

##### New Member
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

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
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..

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!

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..

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

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)

Replies
1
Views
183
Replies
7
Views
148
Replies
7
Views
135
Replies
2
Views
249
Replies
2
Views
489

1,203,754
Messages
6,057,155
Members
444,908
Latest member
Jayrey

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back