Autofill with INDIRECT and HLOOKUP Function

lwjjsgd

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

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
What is the Hlookup returning? Indirect would need it to return a column name such as A, B, C, AB..etc

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!

ROW(586) is the invalid part.

Which cell is the formula in?

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.

Jasobb75 is right.
ROW(586) is the invalid part.

Which cell is the formula in?

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.

So you always want row 586?

What column references are returned from the lookup?

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.

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.

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.

Replies
3
Views
197
Replies
2
Views
671
Replies
6
Views
210
Replies
4
Views
168
Replies
33
Views
842

1,196,268
Messages
6,014,341
Members
441,816
Latest member
Klingon1960

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.

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