+ Reply to Thread
Results 1 to 10 of 10

Formula to get data from other sheet

  1. #1
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Formula to get data from other sheet

    Hello:

    Please refer to attached file.
    Sheet2 has Profit and Loss Data for Stores
    Store name is in column B of Steet1
    Category is in column C of Sheet1 for each month.

    I need a formula to get some of the important data in Sheet2 by selecting the store from cell A2 dropdown list.
    The data I need i listed in column B of Sheet2.
    Once i get this, then i can create a graph to quick view.

    Let me know if you have any questions.
    Thanks.

    Riz
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Formula to get data from other sheet

    Hello:

    I am re-uploaded the excel file.
    Please use this attached file.
    Sorry.
    Thanks
    Riz
    Attached Files Attached Files

  3. #3
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    2016
    Posts
    2,760

    Re: Formula to get data from other sheet

    I'm not a big formula guy, but I would think that a formula solution would need the same words on both sheets.
    You have Dairy Queen on the first sheet and DQ on the second.
    Maybe not, but I just thought I'd bring it up.
    Maybe you want to address the question as to whether either of those can be changed?
    Click the * Add Reputation button in the lower left hand corner of this post to say thanks.

    Don't forget to mark this thread SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.

  4. #4
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Formula to get data from other sheet

    Hi Skywriter and others:

    Yes you are correct, both sheets would need the same words.
    I have corrected in attached sheet.
    I have also highlighted yellow Sheet1 column A

    Please let me know if you have any questions.
    Thanks.

    Riz
    Attached Files Attached Files

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula to get data from other sheet

    and your categories dont all containthe same items, do they?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Formula to get data from other sheet

    Hi FDibbins:

    The Catagories in both sheet will be the same.
    Basically i was thinking of using VLOOKUP based on the store name.
    Please let me know if you have any questions.
    Thanks.

    Riz

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula to get data from other sheet

    Try this approach.

    1. Change the headings on sheet2 from month names, to real dates that match the dates in sheet1
    2. Create a named range for each "district", but only use the range in column C, and give it the same name as the district
    eg Name = Fayetteville range = C4:C65
    3. Use this formulain sheet2 C6, copied down and across...
    =INDEX(Sheet1!$D:$P,MATCH($B6,INDIRECT($A$2),0)+MATCH($A$2,Sheet1!$B:$B,0),MATCH(C$3,Sheet1!$D$1:$P$1,0))

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula to get data from other sheet

    Quote Originally Posted by rizmomin View Post
    Hi FDibbins:

    The Catagories in both sheet will be the same.
    Basically i was thinking of using VLOOKUP based on the store name.
    Please let me know if you have any questions.
    Thanks.

    Riz
    No, I meant the amount of categories per district.
    fayette has 60, but Riverdale only has 55 etc

  9. #9
    Valued Forum Contributor
    Join Date
    01-18-2007
    Location
    Georgia
    MS-Off Ver
    2010
    Posts
    4,434

    Re: Formula to get data from other sheet

    Hi Fdibbins:

    Please refer to attached file.
    Your solution would also work.
    However, I have used Vlookup with some modification and works great.
    Once again thanks for great help.
    Let me know if you have any questions.
    Thanks.

    Riz
    Attached Files Attached Files

  10. #10
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formula to get data from other sheet

    Nicely done.

    A slight modification to your VLOOKUP to make the copy easier...
    =VLOOKUP($B6,Sheet1!$B$3:$P$303,COLUMN(),0)
    This will change the "3" to "4" etc as you copy across

+ 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. Formula to pull data from one sheet to a new sheet based on date range
    By lisajolley11 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-15-2015, 08:38 PM
  2. Replies: 4
    Last Post: 08-21-2014, 07:34 PM
  3. VBA formula to copy paste selected data from main sheet to new sheet
    By vbanoob123 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-24-2014, 12:28 PM
  4. Replies: 4
    Last Post: 02-25-2014, 10:26 PM
  5. [SOLVED] Formula to pull data from one sheet and column for another sheet.
    By Dena in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-05-2013, 06:20 PM
  6. Replies: 1
    Last Post: 03-01-2011, 03:31 PM
  7. formula for automatic moving of data from sheet to sheet
    By kdalton1964 in forum Excel General
    Replies: 0
    Last Post: 02-27-2005, 02:23 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