VLOOKUP+INDIRECT( I think )question.

  • Thread starter Thread starter ferropgy
  • Start date Start date
F

ferropgy

Ihave a workbook that looks like this;

A B
1 10/30/2003
2
3 ABC =VLOOKUP($B$1,ABCdr,$K$554,FALSE)
4 DEF =VLOOKUP($B$1,DEFdr,$K$554,FALSE)
5 GHI =VLOOKUP($B$1,GHIdr,$K$554,FALSE)

These are dymanic ranges each on Separate worksheet.
ABCdr
DEFdr
GHIdr

There are about 50 rows of entries.
When I use the fill handle to copy the formula down the
column the sheet reference remains the same all the way
down the column.(ie. ABCdr down the whole column)
Instead of the sheet reference I think I need to use the
INDIRECT function,(set up a column,each cell refering to
a different worksheet)so I will be able to copy down the
column.
How do I go about setting this up, and what would the
formula look like?
Last but not least is this the best way to do this or
am I way off base?

Thanks in advance.
 
See if this helps
=IF(ISNUMBER(D5),VLOOKUP($A5,INDIRECT("Data!B1:X1000"),5,FALSE),"")
 
I am not sure why you used ISNUMBER at the begining of
this formula! What is in (D5)?
You have it looking up A5 not B1 ....Why?
It looks like to me that the INDIRECT is looking up a
range am I wrong please explain.
I am not sure about this formula at all could you walk me
through it?
Thanks
 
Modify to suit YOUR needs.

ferropgy said:
I am not sure why you used ISNUMBER at the begining of
this formula! What is in (D5)?
You have it looking up A5 not B1 ....Why?
It looks like to me that the INDIRECT is looking up a
range am I wrong please explain.
I am not sure about this formula at all could you walk me
through it?
Thanks
Click to expand...
 
SUCCESS!
Thanks Ron,I just had to work with your formula to
understand it and get it to work.
Thanks again.
 
Modify to suit YOUR needs.
Click to expand...

It's that 'go the extra mile' spirit that sets these Excel newsgroups apart from
other user-to-user forums!

Damn good question. It's impossible to tell what the hell Don meant by D5, and
it seems he's unwilling (or unable) to explain it. I'd bet on the latter because
it has no clear relevance to the question as originally posed.

Either same reason or Don just plain screwed up. In this case, it's pretty clear
it's the latter. Don: if you're only going to provide a template, use fake
tokens like =IF(ISNUMBER(x),VLOOKUP(x,INDIRECT(y),z,0),""). Using misleading
range addresses isn't helpful.
...

The call is INDIRECT("Data!B1:X1000"), which could/should be replaced by a
direct reference to Data!B1:X1000. Don's use of INDIRECT was pointless.
[from Don]
See if this helps [reformatted]
=IF(ISNUMBER(D5),VLOOKUP($A5,
INDIRECT("Data!B1:X1000"),5,FALSE),"")
A B
1 10/30/2003
2
3 ABC =VLOOKUP($B$1,ABCdr,$K$554,FALSE)
4 DEF =VLOOKUP($B$1,DEFdr,$K$554,FALSE)
5 GHI =VLOOKUP($B$1,GHIdr,$K$554,FALSE)

These are dymanic ranges each on Separate worksheet.
ABCdr
DEFdr
GHIdr
Click to expand...
Click to expand...
Click to expand...

These are dynamic ranges? Meaning they're derived using INDIRECT, INDEX or
INDIRECT calls? If so, INDIRECT won't help. INDIRECT can't dereference dynamic
defined names.

You have two alternatives. The first is a multistep process involving no VBA.
Enter the following formula in B3.

="=VLOOKUP($B$1,"&$A3&"dr,$K$554,0)"

Select B3 and fill down as far as needed so there's a formula for each entry in
column A. These formulas evaluate to text containing formulas with literal
referenced to these dynamic named ranges. Select the entire range of these
formulas, Edit > Copy, then Edit > Paste Special as values on top of itself.
Then with this range still selected, Edit > Replace, finding = and replacing
it with = . That is, replace the equal sign with itself. This converts all the
formula text into formulas.

The other alternative involves VBA. Here's my first pass at this. The function
name is an acronym for Indirect Done Right.


Function IDR(s As String) As Variant
On Error Resume Next

Set IDR = Application.Range(s)
If Err.Number = 0 Then Exit Function

Err.Clear
IDR = Evaluate(s)
If Err.Number = 0 Then Exit Function

Err.Clear
IDR = CVErr(xlErrRef)
End Function


Yes, for some unknowable reason VBA's Application.Range(s) can return what
Excel's own INDIRECT(s) can't when s is the name of a dynamic range. Finding
these examples of 'superior spreadsheet functionality' is one of the pleasures
of using Excel.
 
Back
Top