formulas between sheets

D

davidson191

this formula works "=VLOOKUP($I$4,'Data Entry'!$A:$AJ,36,FALSE)" but when the
36 is changed to any higher number it does not work. example
"=VLOOKUP($I$4,'Data Entry'!$A:$AJ,37,FALSE)". is 36 the max number of
'lookups' i can have?
 
G

Gord Dibben

What is "doesn't work"?

Error message? Nothing? Incorrect result?

The 36 is the column index number.

I tried with a table and got returns from up to 60 as a column index number.

Couldn't be bothered going any further just for testing.

Something else is going on.

Do you actually have a column 37 in your Lookup table?


Gord Dibben MS Excel MVP
 
D

davidson191

In The cell it has "#REF!", and yes i do have a column 37. it goes up to 60.
37 - 60 are showing the #REF!.
 
D

davidson191

i can send you a copy of the workbook, so you can look at what i messed up.
 
M

Max

Some thoughts on your orig. post ..
this formula works "=VLOOKUP($I$4,'Data Entry'!$A:$AJ,36,FALSE)"
but when the 36 is changed to any higher number it does not work.
Click to expand...

That's because the table array 'Data Entry'!$A:$AJ covers cols A to AJ only,
a total of 36 cols.
"=VLOOKUP($I$4,'Data Entry'!$A:$AJ,37,FALSE)".
is 36 the max number of 'lookups' i can have?
Click to expand...

Just extend the table array to say: 'Data Entry'!$A:$BZ and it'll work till
78
Eg: =VLOOKUP($I$4,'Data Entry'!$A:$BZ,37,FALSE)

Or, go the full show to cover all 256 cols, viz use:
=VLOOKUP($I$4,'Data Entry'!$1:$65536,37,FALSE)
 
D

davidson191

Max,
thank you both of your formulas worked. is there a way to have it so if on
the look up sheet there is a blank colum(colum 37 in one row does not have a
value) to have the other sheet show nothing instead of a "0" i dont know if
this makes sence.(2sd example. you have 10 lookups b2-b11 for one set of data
b6 is empty. on the formula sheet where that value is to show up it puts a
0.)
 
M

Max

One way is to use an IF construct:
=if(vlookup(...)=0,"",vlookup(...))

Eg:
=IF(VLOOKUP($I$4,'Data Entry'!$1:$65536,37,FALSE)=0,"",
VLOOKUP($I$4,'Data Entry'!$1:$65536,37,FALSE))

Another way, maybe easier, is to suppress the display of zeros in the sheet
via clicking: Tools > Options > View tab > Uncheck "Zero values" > OK
 
D

davidson191

thank you

Max said:
One way is to use an IF construct:
=if(vlookup(...)=0,"",vlookup(...))

Eg:
=IF(VLOOKUP($I$4,'Data Entry'!$1:$65536,37,FALSE)=0,"",
VLOOKUP($I$4,'Data Entry'!$1:$65536,37,FALSE))

Another way, maybe easier, is to suppress the display of zeros in the sheet
via clicking: Tools > Options > View tab > Uncheck "Zero values" > OK
Click to expand...
 
G

Gord Dibben

Good eyes Max.

AJ was overlooked by yours truly.

I guess that was the "something else" that was going on<g>


Gord
 
Top