indirect range reference

Question

I have a worksheet on which I want to show particular differences between two sheets having similar, but not the same, data. Simplified example:

Sheet1 has, by row: "Apples", 15; "Pears", 17; "Peaches", 6.

Sheet2 has, by row: "Apples", 10; "Bananas", 8; "Peaches", 5.

On Sheet 3, I want to show the difference between the sheets for all items on Sheet1. This would be:

Apples, 5; Pears, 17; Peaches 1.

Sheet3 can explicitly match the row identification from Sheet1 for its numbers, but must look up the row for the corresponding data in Sheet2 using the value in Column A. That lookup is giving me trouble, particularly since I want to be able to identify a different 'Sheet2' to compare a different set of data.

When the row position in the selected Sheet2 is known, I can use tailored formulas like this for the entry in row 5 of Sheet1,

='Sheet1'!B5 - INDIRECT("'"&$F$1&"'!B<y>"),

where F1 contains the name of the 'Sheet2' being compared and <y> is the pre-known row number on that sheet corresponding to the data on row 5 of Sheet1.

VLOOKUP would work nicely if its range argument could be dynamically identified. Is there a way????

Answer 1

You lost me.

Nothing Left to Lose