formulas between sheets

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
 
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
 
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!.
 
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!.
 
i can send you a copy of the workbook, so you can look at what i messed up.
 
i can send you a copy of the workbook, so you can look at what i messed up.
 
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)

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

---
 
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,
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.)
 
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
 
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
 
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...
 
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...
 
Good eyes Max.

AJ was overlooked by yours truly.

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


Gord
 
Good eyes Max.

AJ was overlooked by yours truly.

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


Gord
 
Pleasure` Gord. Just happened to notice that bit on the table array <g>
 
You must log in or register to reply here.

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

Similar Threads

Double click fails to run the code, just selects the cell 12
How to vlookup and summing value? 4
Searching the formula (not the value) for a text string 1
EXCEL - IF(IFERROR(VLOOKUP question 0
Curly Brackets 2
Need VLOOKUP to Work Two Ways 0
Match with 3 criteria 3
What is wrong with this formula? 6

Back
Top