# How to Change Worksheet Reference in an Equation Automatically (Referencing a Cell?)

1. ## How to Change Worksheet Reference in an Equation Automatically (Referencing a Cell?)

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!

2. ## Re: How to Change Worksheet Reference in an Equation Automatically (Referencing a Cell?)

Make use of Sumif(s) or Vlookup functions

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1