Hello,
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!$J$140+USB!$J$141-(USB!$J$147/100)*(USB!$J$140+USB!$J$141)-(USB!$J$150/100)*(USB!$J$140+USB!$J$141))/1000000
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
=(A1!$J$140+A1!$J$141-(A1!$J$147/100)*(A1!$J$140+A1!$J$141)-(A1!$J$150/100)*(A1!$J$140+A1!$J$141))/1000000
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.
Thanks!
Bookmarks