+ Reply to Thread
Results 1 to 5 of 5

Referencing text within a formula to locate the data used within that formula

  1. #1
    Registered User
    Join Date
    10-26-2012
    Location
    Barrow-in-Furness, England
    MS-Off Ver
    Excel 2010
    Posts
    18

    Referencing text within a formula to locate the data used within that formula

    Hi,

    I was wondering whether there was a way that I could do the following:

    Assume I have data separated into worksheets by calendar month, i.e. worksheet 1 is January, worksheet 2 is February etc. The format of these worksheets are all the same.

    If on worksheet three, I wanted to use any of the data, I would usually reference the actual worksheet, for example FORMULA('January!'D2) etc however if I wanted to change the figures from the January data to the February data, I would have to manually adjust all the formula's that reference these sheets.

    Is there a way I could have a cell, eg A1, that I manually type in January, February etc and then the formula's use the inputted data in cell A1 as a location reference to the relevant worksheet?

    A simple example would be (which isn't a working formula before anyone moans): =SUM(A1'B2+H3)

    A1 being January in this instance
    B2 is from the January worksheet
    H3 is from the current worksheet (Worksheet 3).

    So the formula is asking to add B2 from January worksheet (which is referenced by A1) with H3 from worksheet 3.

    If I were to then change cell A1 to February, the formula would automatically update and use the figures from February worksheet rather than January.

    Is this possible?

    Thanks,
    Last edited by bishbash89; 11-01-2012 at 06:39 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    08-14-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    573

    Re: Referencing text within a formula to locate the data used within that formula

    Hi bishbash89, you can use indirect formula for that.
    [FORMULA]=sum(indirect(A1&"!"&B2)+H3)[FORMULA]

  3. #3
    Registered User
    Join Date
    10-26-2012
    Location
    Barrow-in-Furness, England
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Referencing text within a formula to locate the data used within that formula

    I've tried that but it doesn't appear to work.

  4. #4
    Valued Forum Contributor
    Join Date
    08-14-2012
    Location
    India
    MS-Off Ver
    Excel 2007
    Posts
    573

    Re: Referencing text within a formula to locate the data used within that formula

    Try this..
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    10-26-2012
    Location
    Barrow-in-Furness, England
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: Referencing text within a formula to locate the data used within that formula

    Brilliant, that works! Thank you very much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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