Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula not working when letter A is used in a named range
I have a named ranged that has section numbers or letters in the first column
and Command Names in the second column ie 1 = Command 1 2 = Command 1 BNRP = Command 3 ACM = Command 4 I am using a formula that when the user types a member of staffs payroll number their relevant section appears in one column ie 1,2,BNRP or ACM and the corresponding command name is displayed in another column. This works perfectly for all sections with numbers or letters except for section ACM when N/A is displayed in the cell. If the A is removed from the section name and just CM is left the formula works. Put the A back in and the formula stops working. The formula I'm using is =if(L8="","",if(L8<1,"",vlookup(L8,Command,2))) Any help would be appreciated. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula not working when letter A is used in a named range
Try
=if(L8="","",if(L8<1,"",vlookup(L8,Command,2,False ))) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "jimar" wrote in message ... I have a named ranged that has section numbers or letters in the first column and Command Names in the second column ie 1 = Command 1 2 = Command 1 BNRP = Command 3 ACM = Command 4 I am using a formula that when the user types a member of staffs payroll number their relevant section appears in one column ie 1,2,BNRP or ACM and the corresponding command name is displayed in another column. This works perfectly for all sections with numbers or letters except for section ACM when N/A is displayed in the cell. If the A is removed from the section name and just CM is left the formula works. Put the A back in and the formula stops working. The formula I'm using is =if(L8="","",if(L8<1,"",vlookup(L8,Command,2))) Any help would be appreciated. Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula not working when letter A is used in a named range
=if(L8="","",if(L8<1,"",vlookup(L8,Command,2)))
Omitting the range lookup, as above, requires the table array: Command to be sorted by the 1st col (lookup col) in ascending order. That's why you hit the error for the "ACM" since Command wasn't sorted (as per post). To avoid ambiguity in this kind of instance, try instead an exact* match for the vlookup: =IF(L8="","",IF(L8<1,"",VLOOKUP(L8,Command,2,0))) *Use zero (or FALSE) as the 4th param (range lookup) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "jimar" wrote: I have a named ranged that has section numbers or letters in the first column and Command Names in the second column ie 1 = Command 1 2 = Command 1 BNRP = Command 3 ACM = Command 4 I am using a formula that when the user types a member of staffs payroll number their relevant section appears in one column ie 1,2,BNRP or ACM and the corresponding command name is displayed in another column. This works perfectly for all sections with numbers or letters except for section ACM when N/A is displayed in the cell. If the A is removed from the section name and just CM is left the formula works. Put the A back in and the formula stops working. The formula I'm using is =if(L8="","",if(L8<1,"",vlookup(L8,Command,2))) Any help would be appreciated. Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula not working when letter A is used in a named range
OT: Bob, think you might have missed my follow up in:
http://tinyurl.com/mpclp Your pick (Italy) is still well & truly on track .. <g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula not working when letter A is used in a named range
Max,
If you recall, Italy was my original pick, and then I tipped France before the Brasil game, so not bad eh? After the quarters and semis, it looks like Italy to me, but if Zidane can turn it on again, if Henry can stop pouting and run at the Italian defence, in Viera toughs it in midfield, France could do it, but it looks like too many Ifs to me. Forza Italia! -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Max" wrote in message ... OT: Bob, think you might have missed my follow up in: http://tinyurl.com/mpclp Your pick (Italy) is still well & truly on track .. <g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula not working when letter A is used in a named range
"Bob Phillips" wrote:
Max, If you recall, Italy was my original pick, and then I tipped France before the Brasil game, so not bad eh? Like I said in the follow up there... Simply amazing, your spot on reading / prediction <g ! After the quarters and semis, it looks like Italy to me, but if Zidane can turn it on again, if Henry can stop pouting and run at the Italian defence, if Viera toughs it in midfield, France could do it, but it looks like too many Ifs to me. Forza Italia! Shaping up to be a close, tight, and exciting game. Anyway, going by the odds-makers here, Italy is the favourite to lift the World Cup, at 1.73 vs 1.95 (France). cheers! -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula not working when letter A is used in a named range
Should be good, the quarters were a bunch of petulant affairs, but the semis
have been fine. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Max" wrote in message ... "Bob Phillips" wrote: Max, If you recall, Italy was my original pick, and then I tipped France before the Brasil game, so not bad eh? Like I said in the follow up there... Simply amazing, your spot on reading / prediction <g ! After the quarters and semis, it looks like Italy to me, but if Zidane can turn it on again, if Henry can stop pouting and run at the Italian defence, if Viera toughs it in midfield, France could do it, but it looks like too many Ifs to me. Forza Italia! Shaping up to be a close, tight, and exciting game. Anyway, going by the odds-makers here, Italy is the favourite to lift the World Cup, at 1.73 vs 1.95 (France). cheers! -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula not working when letter A is used in a named range
"Bob Phillips" wrote:
Should be good, the quarters were a bunch of petulant affairs, but the semis have been fine. Agreed ! -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you Identify text as a named range in excel | Excel Discussion (Misc queries) | |||
Need formula to lookup a named range | Excel Discussion (Misc queries) | |||
Array Formula Not Working with Range with Formulas | Excel Discussion (Misc queries) | |||
Using a formula to create named range reference | Excel Worksheet Functions | |||
How to dynamically reference a dynamic named range | Excel Worksheet Functions |