using vlookup

S

sma

I have a spreadsheet that was done by a former employee. it has five
worksheets. on it it uses vlookup. One of the cells has this formula in it.
=IF($B42="","",VLOOKUP($B42,table,2,FALSE)) I cannot locate the table to make
changes. How do I find out where the vlookup is pulling the information from?
 
D

David Biddulph

Insert/ Name/ Define... will let you see which cells are defined by the
name "table".

Alternatively the name box immediately above the top left-hand coner of your
sheet (just to the left of the formula box) will let you type in "table", or
select from the drop down arrow, and that will select the table.
 
G

Gizmo63

It's worth noting at this point that any named ranges which are of
automatically variable size will NOT show in the drop down list.

Giz
 
A

Arun

Gizmo,
What do you mean by "automatically variable size"? Can you give me an
example?
AD
 
G

Gizmo63

Hi Arun,

Instead of defining a fixed area you can offset from a fixed starting point.
E.g:
=OFFSET(VizMiz!$C$4,1,0,COUNTA(VizMiz!$C:$C)-1,1)

Assumes C4 as the fixed start, counts how many cells have data in column C,
deducts 1 for the column header and then defines the table accordingly.
In the run up to using this table I have a macro importing the data and
removing any blank rows (essential!).

This has to be setup through the 'Insert - Names - Define' menu.

HTH
Giz
 
Top