VLOOKUP quickstep

  • Thread starter The Great Attractor
  • Start date
T

The Great Attractor

Instead of pointing to a specific sheet and data range for the second
VLOOKUP particular, I found that if one merely highlights the data range
in the "lookup sheet", then enters a name for that data range in the
upper left corner of the sheet while it is highlighted, then all that
"sheetname!A:B" etc. stuff can be replaced simply by the new tagname you
gave to that data range highlighted selection as in the example:

Lookup cell is: A1 (replace with your cell)
Tagged range name is: looktable (replace with your tag name)
grabbed data column is: 3 (replace with your data column number)

so:

VLOOKUP (A1,looktable,3,FALSE)

no more bangs or cell range calls!

This would be for fixed range selections given a tag name. If your
lookup table continually expands, you'll have to expand the range given
to the tag name, or tag it each time before applying the lookup. (I
think)
 
D

Dave Peterson

Depending on your layout, you may find it easier just make sure that the range
includes the whole column (select A:E, then name that, for example.)

Alternatively, you may be able to use a dynamic range that grows and contracts
with your data.

See Debra Dalgleish's site for more information:
http://contextures.com/xlNames01.html#Dynamic
 
T

T. Valko

You can give just about every range you need to refer to like that a name.
Some people like to name their ranges, I normally don't, but that's just my
preference.

If you have a range of data that expands you can also name that range. It's
called a dynamic range. There are instructions here:

http://contextures.com/xlNames01.html#Dynamic

Biff

"The Great Attractor"
 
T

T. Valko

Some people like to name their ranges, I normally don't

Here's why:

=VLOOKUP(Sales,Table,2,0)

Looking at that formula I don't know where either Sales or Table is located.
Of course I can find them if I look for 'em.

=VLOOKUP(A1,Sheet2!A1:B100,2,0)

Looking at that formula I know where everything is without having to look
for it!

The only time I use names is to shorten a really long complex formula.

But, that's just my preference.

Biff
 
G

Gord Dibben

Makes sense to me Biff.

I rarely use rangenames unless I need to like in DV list source.


Gord
 
T

T. Valko

I rarely use rangenames unless I need to like in DV list source.

Yeah, I use them in those cases also. I should rephrase my statement:

The only time I use names is if I have to or to shorten a really long
complex formula.

Biff
 
T

The Great Attractor

OK. I have a VB code segment now that will place a parenthesized image
filename into a cell as a graphic.

How do I inject that filename by way of a lookup?

Like, the cell location where the image is displayed is one item, but
the lookup that grabs the filename out of my "Image_Pointer" sheet
needs to make that lookup into a variable that the VB code uses to place
the image at the cell location.

I don't know how to perform a lookup, then declare the results as a
variable, then have the VB code use that variable data as the filename.

I have done some hunting, but still am a bit confused.

I loved the Paradox API back in the 286 days. :-], but it has also
been about that long since I did any of this stuff.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top