Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Simplifications Using Nested If, Indirect & Address Functi
After reviewing some previous posts, I've come up with the following
function. Ideally, I would like to give it a Name reference, but it is too long. Any suggestions for simplifying and shortening? The formula looks at the left adjacent cell for location codes that correspond to location descriptions. The column locations have the potential of changing so I needed a solution that could dynamically change without a fixed reference point. Let me know if you need any other information. Thank you in advance for any assistance. =IF(INDIRECT(ADDRESS(ROW(),COLUMN()-1))="O","Regional / Highland Ave",IF(INDIRECT(ADDRESS(ROW(),COLUMN()-1))="OMV","Maryville / Armory Place",IF(INDIRECT(ADDRESS(ROW(),COLUMN()-1))="OWK","West / Cedar Bluff",IF(INDIRECT(ADDRESS(ROW(),COLUMN()-1))="OSV","Sevierville / Bradford Square",INDIRECT(ADDRESS(ROW(),COLUMN()-1)))))) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Simplifications Using Nested If, Indirect & Address Functi
Select B1 and then use this formula in the Insert|Name|Define dialog:
=IF(A1="O","Regional / Highland Ave", IF(A1="OMV","Maryville / Armory Place", IF(A1="OWK","West / Cedar Bluff", IF(A1="OSV","Sevierville / Bradford Square",A1)))) Then give it a nice name like ExpandName and test it. =expandname In any cell you want (not in column A). And the type 0 or OMV or ... in the cell to the left. lmavroff wrote: After reviewing some previous posts, I've come up with the following function. Ideally, I would like to give it a Name reference, but it is too long. Any suggestions for simplifying and shortening? The formula looks at the left adjacent cell for location codes that correspond to location descriptions. The column locations have the potential of changing so I needed a solution that could dynamically change without a fixed reference point. Let me know if you need any other information. Thank you in advance for any assistance. =IF(INDIRECT(ADDRESS(ROW(),COLUMN()-1))="O","Regional / Highland Ave",IF(INDIRECT(ADDRESS(ROW(),COLUMN()-1))="OMV","Maryville / Armory Place",IF(INDIRECT(ADDRESS(ROW(),COLUMN()-1))="OWK","West / Cedar Bluff",IF(INDIRECT(ADDRESS(ROW(),COLUMN()-1))="OSV","Sevierville / Bradford Square",INDIRECT(ADDRESS(ROW(),COLUMN()-1)))))) -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Simplifications Using Nested If, Indirect & Address Functi
Based on what I understand the following formula will work for you.
=IF(ISNA(MATCH(B10,$D$4:$D$7,0)),B10,INDEX($E$4:$E $7,MATCH(B10,$D$4:$D$7,0))) This assumes the formula is in cell c10 This assumes the "Codes of "O" thru "OSV" are in cells D4:D7 This assumes the Description of these codes are in cells E4:E7 You can copy the above equation to the right of any cell that has the "Code" you want to evaluate and it will do it correctly. If you add to the "Code" you will have to expand the ranges (of D4:D7 and E4:E7) appropriately (do you know what that means?) -- Wag more, bark less "lmavroff" wrote: After reviewing some previous posts, I've come up with the following function. Ideally, I would like to give it a Name reference, but it is too long. Any suggestions for simplifying and shortening? The formula looks at the left adjacent cell for location codes that correspond to location descriptions. The column locations have the potential of changing so I needed a solution that could dynamically change without a fixed reference point. Let me know if you need any other information. Thank you in advance for any assistance. =IF(INDIRECT(ADDRESS(ROW(),COLUMN()-1))="O","Regional / Highland Ave",IF(INDIRECT(ADDRESS(ROW(),COLUMN()-1))="OMV","Maryville / Armory Place",IF(INDIRECT(ADDRESS(ROW(),COLUMN()-1))="OWK","West / Cedar Bluff",IF(INDIRECT(ADDRESS(ROW(),COLUMN()-1))="OSV","Sevierville / Bradford Square",INDIRECT(ADDRESS(ROW(),COLUMN()-1)))))) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Simplifications Using Nested If, Indirect & Address Functi
Hi
Create 2 named ranges Codes with the values O, OMV,OWK and OSV Names as 2 columns including the column for Codes and a column alongside with the required values for each code Then use =IF(COUNTIF(codes,INDEX($1:$65536,ROW(),COLUMN()-1)), VLOOKUP(INDEX($1:$65536,ROW(),COLUMN()-1),Names,2,0), INDEX($1:$65536,ROW(),COLUMN()-1)) A bit shorter, not as many function calls and no use of volatile functions, so it should be quicker. You could created named formula called Result, and paste the above formula into it. On your sheet, then just use =result -- Regards Roger Govier lmavroff wrote: After reviewing some previous posts, I've come up with the following function. Ideally, I would like to give it a Name reference, but it is too long. Any suggestions for simplifying and shortening? The formula looks at the left adjacent cell for location codes that correspond to location descriptions. The column locations have the potential of changing so I needed a solution that could dynamically change without a fixed reference point. Let me know if you need any other information. Thank you in advance for any assistance. =IF(INDIRECT(ADDRESS(ROW(),COLUMN()-1))="O","Regional / Highland Ave",IF(INDIRECT(ADDRESS(ROW(),COLUMN()-1))="OMV","Maryville / Armory Place",IF(INDIRECT(ADDRESS(ROW(),COLUMN()-1))="OWK","West / Cedar Bluff",IF(INDIRECT(ADDRESS(ROW(),COLUMN()-1))="OSV","Sevierville / Bradford Square",INDIRECT(ADDRESS(ROW(),COLUMN()-1)))))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions | Excel Worksheet Functions | |||
Sum(indirect(Address...... | Excel Worksheet Functions | |||
INDIRECT and ADDRESS | Excel Worksheet Functions | |||
using INDIRECT(ADDRESS(...)) | Excel Discussion (Misc queries) | |||
nested Indirect to other WS | Excel Worksheet Functions |