| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 --- |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 --- |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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.) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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.) |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formulas between sheets
You're welcome!
-- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "davidson191" wrote: thank you |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to Evaluate Dynamic DDE Formulas | Excel Worksheet Functions | |||
| insert Rows with Formulas in Place on Multiple Sheets? | Excel Discussion (Misc queries) | |||
| Printing formulas | Excel Discussion (Misc queries) | |||
| Formulas referencing other sheets | Excel Discussion (Misc queries) | |||
| calculating formulas for all workbooks in a folder | Excel Worksheet Functions | |||
Linear Mode
