+ Reply to Thread
Results 1 to 6 of 6

How to use the contents of a cell to direct a formula to a worksheet in another file

  1. #1
    Registered User
    Join Date
    06-11-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    3

    How to use the contents of a cell to direct a formula to a worksheet in another file

    For Example:

    In a spreadsheet whose file name is fruits.xls I have a series of worksheets with data sets for different things. One of these worksheets is called "apples" and another is called "pears." These sheets contain rows of data such as "color" and "weight."

    I have another spreadsheet that I use to generate reports that I send to customers. Let's call this one "reports"

    In Cell A1 of my "reports" spreadsheet I type "apples." I want cell A2 to display the color of the apples that I've recorded in cell B2 of fruits.xls "apples." That's easy. I know how to do that. However if I type "pears" in cell A1 I want cell A2 to instead call to the "pears" worksheet of fruits.xls.

    Therefore if I type "apples" in A1 then A2 would fill in with B2 fruits.xls "apples" which is a value of "red." If I change the value in A1 to "pears" then A2 instead fills in with fruits.xls "pears" B2 which is "green."

    Would this even be do-able with formulas?

    Thanks!

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to use the contents of a cell to direct a formula to a worksheet in another file

    Is "Reports" a worksheet within the file fruits.xls or is it a separate file?

    If it's a separate file then there is no EASY way to do this with formulas using only the built-in functions.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    06-11-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: How to use the contents of a cell to direct a formula to a worksheet in another file

    "Reports" and "fruit" would be separate excel files.

    I'm thinking of just condensing all of the data in "fruit" into one worksheet and doing an IF statement by date. It would end up being a monster worksheet eventually but I could probably just modify my formulas quarterly or something.

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to use the contents of a cell to direct a formula to a worksheet in another file

    You would be better off if you combined "Reports" and "fruits.xls" into one file then you could "easily" do what you wanted.

  5. #5
    Registered User
    Join Date
    06-11-2013
    Location
    Chicago
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: How to use the contents of a cell to direct a formula to a worksheet in another file

    Quote Originally Posted by Tony Valko View Post
    You would be better off if you combined "Reports" and "fruits.xls" into one file then you could "easily" do what you wanted.
    That will make my data management slightly less optimal but it's workable. I went ahead and did that.

    So now I have a "reports" worksheet and a "fruits" worksheet.

    In column B of the "fruits" worksheet I specify whether something is an apple or a pear or a banana or whatever. Column C is "color" column D is "weight" and so forth.

    In cell A1 of the reports worksheet I'm going to type "apple" or "pear" or whatever.

    I want cell A2 in "reports" to pull the value for color for "apple" out of its row. If I type "pear" in A1 I want cell A2 to instead update with the data for "pear." Basically it needs to go down column B in "fruits" until it finds a string that matches what I typed in A1 of "reports."

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to use the contents of a cell to direct a formula to a worksheet in another file

    See if this is what you had in mind:

    http://contextures.com/xlFunctions02.html

+ 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