|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
Hi. I use VLOOKUP formula's every single day - I use them A LOT !
My current machine has Ms Excel 2003.
A normal Vlookup formula looks like :
=VLOOKUP(A2,Sheet1!$A$16:$B$21,16,FALSE)
Prior to Ms Excel 2003, I have NEVER had a problem with Vlookup formula's.
HOWEVER.....Some "Genius" Programmer at Excel has bugged the Worksheet
command.
Now, when I go to type "FALSE" at the end of a Vlookup command, Excel
suddenly opens a Dialog Box looking to "Identify Label"....
If I try to close this box or cancel it - Excel crashes with the loss of all
unsaved work.
There does not seem to be any means of turning this damned function off
either.
It seems to me a classic case of somebody trying to 'Fix' Excel when it's
not broken.
PLEASE PLEASE....If there's any download, bug fix or <ANYTHING> I can do to
stop this damned dialog box opening up and crashing Excel - I would LOVE to
know what it is.
Thanks,
A.
|
|
I do not have 2003, but I notice people using an "0", iso FALSE. Maybe try
and replace your FALSE with a zero?
"andy_suffers_Excel_2003_over-engineering" wrote:
[Quoted Text] > Hi. I use VLOOKUP formula's every single day - I use them A LOT ! > My current machine has Ms Excel 2003. > > A normal Vlookup formula looks like : > > =VLOOKUP(A2,Sheet1!$A$16:$B$21,16,FALSE) > > Prior to Ms Excel 2003, I have NEVER had a problem with Vlookup formula's. > > HOWEVER.....Some "Genius" Programmer at Excel has bugged the Worksheet > command. > > Now, when I go to type "FALSE" at the end of a Vlookup command, Excel > suddenly opens a Dialog Box looking to "Identify Label".... > > If I try to close this box or cancel it - Excel crashes with the loss of all > unsaved work. > > There does not seem to be any means of turning this damned function off > either. > > It seems to me a classic case of somebody trying to 'Fix' Excel when it's > not broken. > > > PLEASE PLEASE....If there's any download, bug fix or <ANYTHING> I can do to > stop this damned dialog box opening up and crashing Excel - I would LOVE to > know what it is. > > Thanks, > A. |
|
Hi Andy
Don't think its a bug!!!
Your data range is 2 columns wide A:B and you are trying to use an
offset of 16!!
--
Regards
Roger Govier
"andy_suffers_Excel_2003_over-engineering"
<andysuffersExcel2003overengineering[ at ]discussions.microsoft.com> wrote in
message news:D939DB9E-B861-461B-8494-1DFEC9DC60D4[ at ]microsoft.com...
[Quoted Text] > Hi. I use VLOOKUP formula's every single day - I use them A LOT ! > My current machine has Ms Excel 2003. > > A normal Vlookup formula looks like : > > =VLOOKUP(A2,Sheet1!$A$16:$B$21,16,FALSE) > > Prior to Ms Excel 2003, I have NEVER had a problem with Vlookup > formula's. > > HOWEVER.....Some "Genius" Programmer at Excel has bugged the Worksheet > command. > > Now, when I go to type "FALSE" at the end of a Vlookup command, Excel > suddenly opens a Dialog Box looking to "Identify Label".... > > If I try to close this box or cancel it - Excel crashes with the loss > of all > unsaved work. > > There does not seem to be any means of turning this damned function > off > either. > > It seems to me a classic case of somebody trying to 'Fix' Excel when > it's > not broken. > > > PLEASE PLEASE....If there's any download, bug fix or <ANYTHING> I can > do to > stop this damned dialog box opening up and crashing Excel - I would > LOVE to > know what it is. > > Thanks, > A.
|
|
I don't have XL2003, so I haven't come across this, but I do find all
the green triangles and "helpful" features to be a bind on the
occasions when I use this version on others' machines.
However, the formula you posted is trying to get data from column 16 of
a range which is only 2 columns wide. I appreciate that you might have
just typed the formula directly into your post so you weren't too
concerned about the syntax, but if this is what you had in your
worksheet then maybe one of Excel's "helpful" features discovered your
error and was trying to alert you to it (using a message which means
nothing to anyone except the programmer who dreamt it up!).
Hope this helps.
Pete
andy_suffers_Excel_2003_over-engineering wrote:
[Quoted Text] > Hi. I use VLOOKUP formula's every single day - I use them A LOT ! > My current machine has Ms Excel 2003. > > A normal Vlookup formula looks like : > > =VLOOKUP(A2,Sheet1!$A$16:$B$21,16,FALSE) > > Prior to Ms Excel 2003, I have NEVER had a problem with Vlookup formula's. > > HOWEVER.....Some "Genius" Programmer at Excel has bugged the Worksheet > command. > > Now, when I go to type "FALSE" at the end of a Vlookup command, Excel > suddenly opens a Dialog Box looking to "Identify Label".... > > If I try to close this box or cancel it - Excel crashes with the loss of all > unsaved work. > > There does not seem to be any means of turning this damned function off > either. > > It seems to me a classic case of somebody trying to 'Fix' Excel when it's > not broken. > > > PLEASE PLEASE....If there's any download, bug fix or <ANYTHING> I can do to > stop this damned dialog box opening up and crashing Excel - I would LOVE to > know what it is. > > Thanks, > A.
|
|
Oops, missed that one! You are so very right and correct!
"Roger Govier" wrote:
[Quoted Text] > Hi Andy > > Don't think its a bug!!! > Your data range is 2 columns wide A:B and you are trying to use an > offset of 16!! > > -- > Regards > > Roger Govier > > > "andy_suffers_Excel_2003_over-engineering" > <andysuffersExcel2003overengineering[ at ]discussions.microsoft.com> wrote in > message news:D939DB9E-B861-461B-8494-1DFEC9DC60D4[ at ]microsoft.com... > > Hi. I use VLOOKUP formula's every single day - I use them A LOT ! > > My current machine has Ms Excel 2003. > > > > A normal Vlookup formula looks like : > > > > =VLOOKUP(A2,Sheet1!$A$16:$B$21,16,FALSE) > > > > Prior to Ms Excel 2003, I have NEVER had a problem with Vlookup > > formula's. > > > > HOWEVER.....Some "Genius" Programmer at Excel has bugged the Worksheet > > command. > > > > Now, when I go to type "FALSE" at the end of a Vlookup command, Excel > > suddenly opens a Dialog Box looking to "Identify Label".... > > > > If I try to close this box or cancel it - Excel crashes with the loss > > of all > > unsaved work. > > > > There does not seem to be any means of turning this damned function > > off > > either. > > > > It seems to me a classic case of somebody trying to 'Fix' Excel when > > it's > > not broken. > > > > > > PLEASE PLEASE....If there's any download, bug fix or <ANYTHING> I can > > do to > > stop this damned dialog box opening up and crashing Excel - I would > > LOVE to > > know what it is. > > > > Thanks, > > A. > > > |
|
I would just go to Tools>Options>Calculation and uncheck "Accept labels in
formulas"
Does that rectify the problem?
If so, check out and see where you may have conflicting ranges with label names.
This KB article for excel 97 gives this bit of info. Could be it also applies
to newer vesrions but article not updated.
http://support.microsoft.com/kb/161881/en-us
If multiple tables on the same worksheet contain identical labels, the table
Microsoft Excel uses is determined by the location of the cell that contains the
formula. In general, the table that is referenced is to the left and/or above
the cell that contains the formula.
For example, if a worksheet contains a table in cells A1:E10 and a table in
cells A15:E25, if you type a formula in cell A30, Microsoft Excel uses the
labels in the table in cells A15:E25. However, if you type the same formula in
cell A12, Microsoft Excel uses the first table in cells A1:E10. If Microsoft
Excel is unable to determine to which table you are referring, it displays the
"Identify Label" dialog box. This dialog box prompts you to select the cell that
contains the label you want to use.
Gord Dibben MS Excel MVP
On Thu, 21 Sep 2006 08:52:02 -0700, andy_suffers_Excel_2003_over-engineering
<andysuffersExcel2003overengineering[ at ]discussions.microsoft.com> wrote:
[Quoted Text] >Hi. I use VLOOKUP formula's every single day - I use them A LOT ! >My current machine has Ms Excel 2003. > >A normal Vlookup formula looks like : > >=VLOOKUP(A2,Sheet1!$A$16:$B$21,16,FALSE) > >Prior to Ms Excel 2003, I have NEVER had a problem with Vlookup formula's. > >HOWEVER.....Some "Genius" Programmer at Excel has bugged the Worksheet >command. > >Now, when I go to type "FALSE" at the end of a Vlookup command, Excel >suddenly opens a Dialog Box looking to "Identify Label".... > >If I try to close this box or cancel it - Excel crashes with the loss of all >unsaved work. > >There does not seem to be any means of turning this damned function off >either. > >It seems to me a classic case of somebody trying to 'Fix' Excel when it's >not broken. > > >PLEASE PLEASE....If there's any download, bug fix or <ANYTHING> I can do to >stop this damned dialog box opening up and crashing Excel - I would LOVE to >know what it is. > >Thanks, >A.
|
|
|