| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
named ranges in multiple criteria formula
I'm not sure what formula to use . I have 10 columns of numbers each with
their own name as a heading. SICU is a named range for room numbers 300-350 .....c2:c51 MICU is a named range for room numbers 400-476......d2:d77 ORTHO is a named range for room numbers 500-583......e2:e84 I want to enter a room number in B2 and have the name of the floor populate A2 So if I enter room number 410 in B2 the answer MICU would fill in A2. Is this possible? Thank you in advance for any help you can provide Ferde |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
named ranges in multiple criteria formula
put this in F2 and copy down
=C2&" "&D2&" "&E2 put this in A2 =INDEX(C1:E1,MATCH(B2,INDIRECT("C"&MATCH("*"&B2&"* ",F1:F84,0)):INDIRECT("E"&MATCH("*"&B2&"*",F1:F84, 0)),0)) "ferde" skrev: I'm not sure what formula to use . I have 10 columns of numbers each with their own name as a heading. SICU is a named range for room numbers 300-350 .....c2:c51 MICU is a named range for room numbers 400-476......d2:d77 ORTHO is a named range for room numbers 500-583......e2:e84 I want to enter a room number in B2 and have the name of the floor populate A2 So if I enter room number 410 in B2 the answer MICU would fill in A2. Is this possible? Thank you in advance for any help you can provide Ferde |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
named ranges in multiple criteria formula
Thank you so much for the response. This formula works great for A2 . I'm
trying to copy down from A2 but it wont work except in A2. I'm guessing because of cell reference errors . Since I dont have a clue on how you figured this formula out... I dont know how to fix it so it will copy down about 50 rows in column A Hope this makes sense... thank you for your time and effort. "excelent" wrote: put this in F2 and copy down =C2&" "&D2&" "&E2 put this in A2 =INDEX(C1:E1,MATCH(B2,INDIRECT("C"&MATCH("*"&B2&"* ",F1:F84,0)):INDIRECT("E"&MATCH("*"&B2&"*",F1:F84, 0)),0)) "ferde" skrev: I'm not sure what formula to use . I have 10 columns of numbers each with their own name as a heading. SICU is a named range for room numbers 300-350 .....c2:c51 MICU is a named range for room numbers 400-476......d2:d77 ORTHO is a named range for room numbers 500-583......e2:e84 I want to enter a room number in B2 and have the name of the floor populate A2 So if I enter room number 410 in B2 the answer MICU would fill in A2. Is this possible? Thank you in advance for any help you can provide Ferde |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
named ranges in multiple criteria formula
oh ok i just se now u write 10 columns, i was exspected 3 :-)
anyway if u paste whitch columns and the range of theese ill try fix it "ferde" skrev: Thank you so much for the response. This formula works great for A2 . I'm trying to copy down from A2 but it wont work except in A2. I'm guessing because of cell reference errors . Since I dont have a clue on how you figured this formula out... I dont know how to fix it so it will copy down about 50 rows in column A Hope this makes sense... thank you for your time and effort. "excelent" wrote: put this in F2 and copy down =C2&" "&D2&" "&E2 put this in A2 =INDEX(C1:E1,MATCH(B2,INDIRECT("C"&MATCH("*"&B2&"* ",F1:F84,0)):INDIRECT("E"&MATCH("*"&B2&"*",F1:F84, 0)),0)) "ferde" skrev: I'm not sure what formula to use . I have 10 columns of numbers each with their own name as a heading. SICU is a named range for room numbers 300-350 .....c2:c51 MICU is a named range for room numbers 400-476......d2:d77 ORTHO is a named range for room numbers 500-583......e2:e84 I want to enter a room number in B2 and have the name of the floor populate A2 So if I enter room number 410 in B2 the answer MICU would fill in A2. Is this possible? Thank you in advance for any help you can provide Ferde |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
named ranges in multiple criteria formula
With a little more work on my part, I figured it out for all 10 columns. My
spreadsheet is working GREAT now . Thank you so much for the great formula. Ferde "excelent" wrote: oh ok i just se now u write 10 columns, i was exspected 3 :-) anyway if u paste whitch columns and the range of theese ill try fix it "ferde" skrev: Thank you so much for the response. This formula works great for A2 . I'm trying to copy down from A2 but it wont work except in A2. I'm guessing because of cell reference errors . Since I dont have a clue on how you figured this formula out... I dont know how to fix it so it will copy down about 50 rows in column A Hope this makes sense... thank you for your time and effort. "excelent" wrote: put this in F2 and copy down =C2&" "&D2&" "&E2 put this in A2 =INDEX(C1:E1,MATCH(B2,INDIRECT("C"&MATCH("*"&B2&"* ",F1:F84,0)):INDIRECT("E"&MATCH("*"&B2&"*",F1:F84, 0)),0)) "ferde" skrev: I'm not sure what formula to use . I have 10 columns of numbers each with their own name as a heading. SICU is a named range for room numbers 300-350 .....c2:c51 MICU is a named range for room numbers 400-476......d2:d77 ORTHO is a named range for room numbers 500-583......e2:e84 I want to enter a room number in B2 and have the name of the floor populate A2 So if I enter room number 410 in B2 the answer MICU would fill in A2. Is this possible? Thank you in advance for any help you can provide Ferde |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Use named ranges in array formula | Excel Worksheet Functions | |||
| using named ranges for formula abbreviations | Excel Worksheet Functions | |||
| Sumproduct using named ranges and multiple criteria | Excel Discussion (Misc queries) | |||
| Range matching multiple named Ranges | Excel Discussion (Misc queries) | |||
| How to Link named ranges from multiple Workbooks into a single Wo. | Excel Discussion (Misc queries) | |||
Linear Mode
