+ Reply to Thread
Results 1 to 2 of 2

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

  1. #1
    Registered User
    Join Date
    10-06-2012
    Location
    San Francisco
    MS-Off Ver
    Excel 2007
    Posts
    2

    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. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,771

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 4
    Last Post: 07-24-2015, 08:33 AM
  2. Automatically change a worksheet reference when a new worksheet is added
    By crayadder in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-11-2014, 12:23 PM
  3. [SOLVED] Equation That Will Automatically Change Cell Data
    By garrett.grillo in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-05-2012, 02:01 PM
  4. Replies: 2
    Last Post: 09-15-2010, 09:55 AM
  5. Referencing a Worksheet using a cell reference
    By speakers_86 in forum Excel General
    Replies: 5
    Last Post: 03-30-2007, 01:35 PM

Bookmarks

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