Invalid Formula with the use of Indirect

  • Thread starter Thread starter 0-0 Wai Wai ^-^
  • Start date Start date
0

0-0 Wai Wai ^-^

Hi.
I'm not sure why it causes the error.

The formula is:
=VLOOKUP(A1, INDIRECT($C$15), 2, 1)
=VLOOKUP(A2, INDIRECT($C$15), 2, 1)
=VLOOKUP(A3, INDIRECT($C$15), 2, 1)

Cells A1 to A3 contain the values to lookup.
Cell C15 contains
=ADDRESS(ROW(C5),COLUMN(C5), 4) &":"& ADDRESS(ROW(C14),COLUMN(C14), 4)
=C5:C14 [answer display]

It won't work! :(
Error: #REF!

I need to use the INDIRECT function because I need a dynamic reference (the
table).
The table is being constantly updated.

How to solve this problem?
Or is there any alternative to do the same thing?
 
Your range needs to be at least two columns to work within a VLOOKUP
formula, i.e. rather than C5:C14 you need C5:D14 or similar
 
daddylonglegs said:
Your range needs to be at least two columns to work within a VLOOKU
formula, i.e. rather than C5:C14 you need C5:D14 or similar
Click to expand...

Sorry, I should rephrase that...

The range needs to be at least as many columns wide as the thir
argument, as you have a third argument of 2 your range needs to be a
least 2 columns wide. You either need to change the range, as indicate
above, or change the column index number (third argument) to 1
Obviously the solution is dependant on exactly want you need to do, ar
you trying to return a value from column D or column C
 
Perhaps you can create a dynamic named range, as described here:

http://www.contextures.com/xlNames01.html

then reference that range in the VLookup formulas.

If you provide more detail about the table, and the sheet layout, and
what you need to do, someone may be able to suggest alternatives.

0-0 Wai Wai ^-^ said:
Hi.
I'm not sure why it causes the error.

The formula is:
=VLOOKUP(A1, INDIRECT($C$15), 2, 1)
=VLOOKUP(A2, INDIRECT($C$15), 2, 1)
=VLOOKUP(A3, INDIRECT($C$15), 2, 1)

Cells A1 to A3 contain the values to lookup.
Cell C15 contains
=ADDRESS(ROW(C5),COLUMN(C5), 4) &":"& ADDRESS(ROW(C14),COLUMN(C14), 4)
=C5:C14 [answer display]

It won't work! :(
Error: #REF!

I need to use the INDIRECT function because I need a dynamic reference (the
table).
The table is being constantly updated.

How to solve this problem?
Or is there any alternative to do the same thing?
Click to expand...
 
Oh! My silly mistake.
It's because I'm working with different forumlas, some of them use "data set"
column only, some of them (like vlookup) need use the whole table but I forget
it.

So I should create a cell which gets C5:D14.
Then I use indirect to call this cell.

Thanks for your help.
 
As to dynamic named range, the problme is I can't change the reference style of
that named range into absolute (eg $A$1) or relative (eg A1) or partially
relative.

So it's not flexible enough.

Thanks for your help!

Debra Dalgleish said:
Perhaps you can create a dynamic named range, as described here:

http://www.contextures.com/xlNames01.html

then reference that range in the VLookup formulas.

If you provide more detail about the table, and the sheet layout, and
what you need to do, someone may be able to suggest alternatives.

0-0 Wai Wai ^-^ said:
Hi.
I'm not sure why it causes the error.

The formula is:
=VLOOKUP(A1, INDIRECT($C$15), 2, 1)
=VLOOKUP(A2, INDIRECT($C$15), 2, 1)
=VLOOKUP(A3, INDIRECT($C$15), 2, 1)

Cells A1 to A3 contain the values to lookup.
Cell C15 contains
=ADDRESS(ROW(C5),COLUMN(C5), 4) &":"& ADDRESS(ROW(C14),COLUMN(C14), 4)
=C5:C14 [answer display]

It won't work! :(
Error: #REF!

I need to use the INDIRECT function because I need a dynamic reference (the
table).
The table is being constantly updated.

How to solve this problem?
Or is there any alternative to do the same thing?
Click to expand...
Click to expand...
 
Back
Top