VLOOKUP
I have a Vlookup in a sheet like this:
=VLOOKUP($B3,Events!$A$2:$C$21,3,0) where $B3 is a value from 01 to 21 (entered by user) In the remote sheet I create the lookup number with code like this: =IF(A10+1<10,CONCATENATE("0",A10+1),A10+1) to get a text value (ie 01,02,03...21) The problem - for all values less than 10, it works fine. For values of 10 and above I get #N/A However, when I change the formula (in the remote sheet) for 10 (or above) to hard coded text (for 10 it would be '10), I get the correct return The cell type is the same for source and remote (text). Anybody know why? Is there a workaround besides hard coding the 10 values? Glen |
VLOOKUP
=Concatenate("0",A10+1) returns a string.
If your data should be two character strings, you could use this: =text(a10+1,"00") If your data should be numeric (but show two characters), then just: =a1+1 but format|cells|number tab|custom In the type box: 00 It looks like you'll want the last option--since your value in B3 looks like it's a number (not string). Glen Mettler wrote: I have a Vlookup in a sheet like this: =VLOOKUP($B3,Events!$A$2:$C$21,3,0) where $B3 is a value from 01 to 21 (entered by user) In the remote sheet I create the lookup number with code like this: =IF(A10+1<10,CONCATENATE("0",A10+1),A10+1) to get a text value (ie 01,02,03...21) The problem - for all values less than 10, it works fine. For values of 10 and above I get #N/A However, when I change the formula (in the remote sheet) for 10 (or above) to hard coded text (for 10 it would be '10), I get the correct return The cell type is the same for source and remote (text). Anybody know why? Is there a workaround besides hard coding the 10 values? Glen -- Dave Peterson |
All times are GMT +1. The time now is 01:58 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com