Hey Guys,
First time poster so i apologise if i'm doing this incorrectly/breaking any rules.
Basically, i've got the below code and it works just fine but it's not the most elegant solution and i was wondering if any of you had any bright ideas on how to tidy this up a little?
Basically, i have three columns, A & B are data validation lists. B is a dynamic list depending on what is next to it in A. The code below sits in column C.
On another sheet i have a bunch of 2-column tables. List A displays all of the tables and list B will then list the rows entries of one of those two columns of the table you select with list A. The code below will then display whatever is in the second column (of the table and row you select with list A and List B).
=IFERROR(INDEX((IF(T15='Business Units'!B$2,CorpBU,(IF(T15='Business Units'!B$3,DMSBU,(IF(T15='Business Units'!B$4,ESBU,(IF(T15='Business Units'!B$5,ESSBU,(IF(T15='Business Units'!B$6,JOBBU,(IF(T15='Business Units'!B$7,NDISBU,(IF(T15='Business Units'!B$8,NPABU,(IF(T15='Business Units'!B$9,OHBU,(IF(T15='Business Units'!B$10,PEBU,(IF(T15='Business Units'!B$11,PHaMsBU,(IF(T15='Business Units'!B$12,RASBU,(IF(T15='Business Units'!B$13,WCBU,(IF(T15='Business Units'!B$14,WFDBU,(IF(T15='Business Units'!B$15,YEPBU,"")))))))))))))))))))))))))))),MATCH($P15,(IF(T15='Business Units'!B$2,Corp,(IF(T15='Business Units'!B$3,DMS,(IF(T15='Business Units'!B$4,ES,(IF(T15='Business Units'!B$5,ESS,(IF(T15='Business Units'!B$6,JOB,(IF(T15='Business Units'!B$7,NDIS,IF(T15='Business Units'!B$8,NPA,(IF(T15='Business Units'!B$9,OH,(IF(T15='Business Units'!B$10,PE,(IF(T15='Business Units'!B$11,PHaMs,(IF(T15='Business Units'!B$12,RAS,(IF(T15='Business Units'!B$13,WC,(IF(T15='Business Units'!B$14,WFD,(IF(T15='Business Units'!B$15,YEP,""))))))))))))))))))))))))))),0),1),"")
Note: in the above code, entries that have the suffix BU (i.e CorpBU) are a table and anything that doesn't have 'BU' (i.e. Corp) is a named range. These named ranges are a column in each of the 2-column tables. For example; The 2-column table called 'CorpBU' has two columns, One called 'GL code' and one called 'Finance Code'. The named Range 'Corp' refers to the 'Finance Code' column of 'CorpBU'. These tables are business units.
I tried playing around with index & match but i found i couldn't figure out a way to dynamically change the array variable.
For example what i would have liked is =index(~dynamic variable from List A/a business unit table name~,Match(cell referencing a business unit table name,~dynamic variable from list B~,0),1).
Clear as mud?
I appreciate that sounds really confusing but i hope you get where im going with that.
Like i said, the code works but ideally i would like to simplify that code and even more ideally, have that code more dynamic because as it stands, if i add another business unit table, all that code will have to be altered for every cell that code is situated in.
EDIT: I've attached a cleansed version of the data and what I have. It works but i would like a more elegant way to get the data in column Q on the 'startrack' sheet.
Thanks in advance.
Bookmarks