Autofill with INDIRECT and HLOOKUP Function

[lwjjsgd]

Could someone take a look on my formula please?

=INDIRECT("Liabilities!"&HLOOKUP($A$2,Liabilities!$E$1:$P$3,2,FALSE)&ROW(586))

I am trying to auto-fill the rows but get formula error message.

Please advise!

 

[platonic567]

What is the Hlookup returning? Indirect would need it to return a column name such as A, B, C, AB..etc

 

[lwjjsgd]

It is returning to a column that user specifies in A2, for example, month. When I took out "ROW", the formula works, but I need to auto-fill them. Please advise!

 

[jasonb75]

ROW(586) is the invalid part.

Which cell is the formula in?

 

[lwjjsgd]

There is a not a set cell. the Hlookup which helps users to select month, locates the column in the "Liabilities" worksheet, 586 is the row number in the "Liabilities" worksheet, these two together lock in a cell. Does that make sense? Sorry for the confusion.

 

[Gilliam]

Jasobb75 is right.

ROW(586) is the invalid part.

Which cell is the formula in?

put ROW(A586) instead...
however I suspect there is a better method of doing this with index and match, What exactly is the formula doing ( in english )?
Indirect is an equation only to be used when there is no other solution, it's very slow.

 

[jasonb75]

So you always want row 586?

What column references are returned from the lookup?

 

[lwjjsgd]

I only need row 586 once, that's why there is a need for auto-fill, so I can drag down and the formula will adapt to row 587, 588, etc.

 

[jasonb75]

however I suspect there is a better method of doing this with index and match, What exactly is the formula doing ( in english )?
Indirect is an equation only to be used when there is no other solution, it's very slow.

Exactly what I was thinking.

 

[lwjjsgd]

Using ROW(A586) works, thanks Gilliam! The formula is used to locate a specific cell in another worksheet. As being mentioned above, the HLOOKUP helps users select month, row is just where the number is located. Basically, I am using both column and row to locate a specific cell.

I appreciate your solution Gilliam!