CODEWITHSUNDEEP
excelfunction
Indrid
Indrid

Reputation: 1192

Excel formula use variable as row reference in other sheet

Would appreciate any assistance with this.

I have formula:

=INDEX('data'!$10:$10,(ROW(1:1)+4))

This is referencing my data sheet row 10 and column 5 ( The $10:$10 is a reference to ROW and the (ROW(1:1)+5) is providing the reference to column 5.

To make things scale more easily I would very much like to replace the $10:$10 part (the row) with the value from a cell which is on the current sheet, not in the data sheet. The reason for this is that I am able to calculate the co-ordinates from another formula on the existing sheet.

So, in summary instead of:

=INDEX('data'!$10:$10,(ROW(1:1)+4))

I would like to use something like:

=INDEX('data'!(REFERENCE TO A VALUE IN A LOCAL SHEET CELL),(ROW(1:1)+4))

The value in the local cell is a valid integer which represents the correct row the formula should point to in the 'data' sheet. Hope this makes sense.

Many thanks!

Upvotes: 1

Views: 121

Answers (1)

Andreas
Andreas

Reputation: 23958

You need to use INDIRECT() and concatenate.

=INDEX(INDIRECT(concatenate ("'data'!$", A1, ":$", A1)),(ROW(1:1)+4))

If A1 is where the row number is

Upvotes: 1

Related Questions