+ Reply to Thread
Results 1 to 7 of 7

How to create formula that links to another sheet based on user input

  1. 01-08-2009, 10:40 AM #1
    gshock
    gshock is offline
    Forum Contributor
    Join Date
    03-04-2008
    Location
    Ohio
    MS-Off Ver
    Office 2010
    Posts
    208

    How to create formula that links to another sheet based on user input

    I'm trying to create a formula similar to this:

    =Calculations!(Indirect("N"&A2)-Indirect("AB"&A2)+Indirect("AA"&A2)

    The idea is that the user will enter a value in cell A2. That number will determine the appropriate rows in the formula above. I'm not even sure if

    Thanks in advance.

    Regards,

    -gshock
    Last edited by gshock; 01-08-2009 at 11:34 AM.
    Reply With QuoteRegister To Reply
  2. 01-08-2009, 10:49 AM #2
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Rewrite it as:

    =INDIRECT("Calculations!N"&A2)-INDIRECT("Calculations!AB"&A2)+INDIRECT("Calculations!AA"&A2)
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  3. 01-08-2009, 10:50 AM #3
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582
    Please Login or Register  to view this content.
    Does that work for you?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
    Reply With QuoteRegister To Reply
  4. 01-08-2009, 10:59 AM #4
    gshock
    gshock is offline
    Forum Contributor
    Join Date
    03-04-2008
    Location
    Ohio
    MS-Off Ver
    Office 2010
    Posts
    208

    That will do!!!

    Quote Originally Posted by NBVC View Post
    Rewrite it as:

    =INDIRECT("Calculations!N"&A2)-INDIRECT("Calculations!AB"&A2)+INDIRECT("Calculations!AA"&A2)
    I had that originally, and it didn't work. For some reason, in our company version of Excel 2000, we have to put the sheet names in single quotes first.

    The formula works now, but looks like this...
    =INDIRECT("'Calculations!'N"&A2)-INDIRECT("'Calculations!'AB"&A2)+INDIRECT("'Calculations!'AA"&A2).

    I have to call the sheet like this: 'Calculations!'

    Thanks for the help. Much appreciated.

    Best regards,

    -gshock
    Reply With QuoteRegister To Reply
  5. 01-08-2009, 11:01 AM #5
    NBVC
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Don't recall if 2000 is any different... but usuallly you only need the apostrophe's if you have spaces or special characters in the sheet names... but it is good practice to always put them anyways

    Please mark your thread as Solved.
    Reply With QuoteRegister To Reply
  6. 01-08-2009, 01:52 PM #6
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582
    I am on 2000 and do not need the single quotes unless (like NBVC said) there are spaces.
    Reply With QuoteRegister To Reply
  7. 01-08-2009, 02:07 PM #7
    gshock
    gshock is offline
    Forum Contributor
    Join Date
    03-04-2008
    Location
    Ohio
    MS-Off Ver
    Office 2010
    Posts
    208
    Quote Originally Posted by ChemistB View Post
    I am on 2000 and do not need the single quotes unless (like NBVC said) there are spaces.
    Some of the tabs have spaces, and others have special characters, periods, dashes, etc. Wasn't my idea, b/c I didn't create the workbook, but I'm stuck with it and it's too big for me to "re-build" on my own. Such is life.

    Thanks for the help.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1