I posted this before, but this time I have more details to help show what I am looking for.
I have and excel workbook that pulls financial data (GL Type) from another workbook. I currently use a vlookup to pull the data into my other work book.
My problem: Every time I add a new market (I add them alphabetically) to the original book, it throws all the references (after the inserted market) off in my book that I am pulling into. So every time I do this I have to spend time going through and updating tons of cell references.
What I am looking to do is make my second workbook "smarter". If I can find a way to reference the market name and then pull all the columns across associated with that market name instead of going and updating cell references every time I add a new market, it would save me tons of time.
I think an index match may work, but I have not used index match very much.
any help or advice on this is much appreciated.
Work Book Examples:
<tbody>
</tbody>
I have and excel workbook that pulls financial data (GL Type) from another workbook. I currently use a vlookup to pull the data into my other work book.
My problem: Every time I add a new market (I add them alphabetically) to the original book, it throws all the references (after the inserted market) off in my book that I am pulling into. So every time I do this I have to spend time going through and updating tons of cell references.
What I am looking to do is make my second workbook "smarter". If I can find a way to reference the market name and then pull all the columns across associated with that market name instead of going and updating cell references every time I add a new market, it would save me tons of time.
I think an index match may work, but I have not used index match very much.
any help or advice on this is much appreciated.
Work Book Examples:
Book1 (what I am pulling from) | ||||
North AZ | South AZ | East TX | West TX | |
Revenue | ||||
Expense 1 | ||||
Expense 2 | ||||
Expense 3 | ||||
Expense 4 | ||||
Expense 5 | ||||
Expense 6 | ||||
Expense 7 | ||||
Income | ||||
Pulling into Book2. | ||||
Book2 | (Tabs for every market) | |||
Jan | Feb | Mar | ||
Revenue | ||||
Expense 1 | ||||
Expense 2 | ||||
Expense 3 | ||||
Expense 4 | ||||
Expense 5 | ||||
Expense 6 | ||||
Expense 7 | ||||
Income |
<tbody>
</tbody>