Hello everyone,
I am trying to automatically have units populate in the unit column based if the room is within a specific range. I have attached a small sample of what I am trying to do. Rooms 3000 – 3009 are unit 3N, 3010 – 3019 are for 3S, A120 – A129 are for unit 1E, M670 – M679 are for unit 6W and 8040 – 8050 are for unit 8NW. Thanks for the help!
Create named range "Room2Unit"
in D2
=IF(ISNUMBER($B2),VLOOKUP($B2,Room2Unit,2,1),VLOOKUP(--RIGHT($B2,3),Room2Unit,2,1))
copy dowm
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Could you elaborate on how this works? I'm putting different room numbers and I'm not getting units that correspond to the room number.
What is not working: I have tried various room number and get the correct results.
The table has to be in ascending order as in the file I posted as the VLOOKUP assumes data in ascending order. I "remove" the leading letter for "A" and "M" so it does a lookup on the numeric part.
Post file with errors
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks