ExcelBanter - formulas between sheets

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - formulas between sheets (https://www.excelbanter.com/excel-worksheet-functions/109033-formulas-between-sheets.html)

davidson191

formulas between sheets

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?

Gord Dibben

formulas between sheets

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

On Thu, 7 Sep 2006 19:12:01 -0700, davidson191
wrote:

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?

davidson191

formulas between sheets

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!.

"Gord Dibben" wrote:

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

On Thu, 7 Sep 2006 19:12:01 -0700, davidson191
wrote:

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?

davidson191

formulas between sheets

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

"davidson191" wrote:

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!.

"Gord Dibben" wrote:

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

On Thu, 7 Sep 2006 19:12:01 -0700, davidson191
wrote:

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?

Max

formulas between sheets

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.

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?

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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

davidson191

formulas between sheets

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.)

"Max" wrote:

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.

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?

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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Max

formulas between sheets

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"davidson191" wrote:
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.)

davidson191

formulas between sheets

thank you

"Max" wrote:

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"davidson191" wrote:
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.)

Gord Dibben

formulas between sheets

Good eyes Max.

AJ was overlooked by yours truly.

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

Gord

On Fri, 8 Sep 2006 15:46:24 +0800, "Max" wrote:

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.

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?

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)

Max

formulas between sheets

Pleasure` Gord. Just happened to notice that bit on the table array <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Gord Dibben" wrote:
Good eyes Max.

AJ was overlooked by yours truly.

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

Gord

Max

formulas between sheets

You're welcome!
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"davidson191" wrote:
thank you

All times are GMT +1. The time now is 10:46 AM.