How to Change Worksheet Reference in an Equation Automatically (Referencing a Cell?)
Here is the situation:
I have 50 banks financial data, and each bank has it's own data worksheet in the same Excel file. There is a separate summary tab for a variety of calculations for each of these 50 banks, one row for each bank. The equations on this summary tab reference the other 50 individual tabs.
When I try to fill out the Summary tab by copying the equations for each bank in the new row, I am currently manually changing the worksheet reference for each equation. For example:
USB is referring to US Bank. Now when I copy this equation down for Bank of America, I need to change each instance where it says USB, to become BoA (in order for it to now pull data from the BoA worksheet).
My initial thought is I could have a column that has all the worksheet names, and have the equation automatically pull from this cell. So the above equation would then look like
with A1 being a cell that says "USB".
This did not work however, and got a #REF! error.
Does anyone have any suggestions on how to do this? The purpose is to make replication for each bank faster instead of just manually going in and replacing each instance of the worksheet reference.
Re: How to Change Worksheet Reference in an Equation Automatically (Referencing a Cell?)
Make use of Sumif(s) or Vlookup functions
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *
below to Add Reputation
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
By RoseMV in forum Excel General
Last Post: 07-24-2015, 08:33 AM
By crayadder in forum Excel Formulas & Functions
Last Post: 06-11-2014, 12:23 PM
By garrett.grillo in forum Excel Formulas & Functions
Last Post: 11-05-2012, 02:01 PM
By accola0003 in forum Excel General
Last Post: 09-15-2010, 09:55 AM
By speakers_86 in forum Excel General
Last Post: 03-30-2007, 01:35 PM
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1