+ Reply to Thread
Results 1 to 11 of 11

Pulling detailed data from one sheet to another

Hybrid View

  1. 05-04-2009, 03:54 PM #1
    avidcat
    avidcat is offline
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346

    Pulling detailed data from one sheet to another

    I don't think this may even be possible, but what I am trying to do is pull only certain information from one worksheet to another based on whether there are entries on certain dates for employees.

    A sample is attached.

    On the first worksheet I have a drop down menu for all employees on the second sheet. (the drop down menu pulls the names from the second sheet)

    The second worksheet has all employees in Column A and to the right has all their variances by date. Most dates will be blank as they didn't have a variance.

    What I would like is on the first worksheet, is to select their name from the drop down menu and have all the dates that they had variances and the variances show up.

    If you take a look at the attached excel file it might explain what I am trying to do better.

    Thanks for any help.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 05-04-2009, 05:23 PM #2
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Pulling detailed data from one sheet to another

    Totally doable. Someone will most likely offer an array solution, which I prefer to avoid. So I added a "key search" row at row 25 to help this out, the rest is straightforward INDEX/MATCHING...very robust.
    Attached Files Attached Files
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  3. 05-04-2009, 05:47 PM #3
    avidcat
    avidcat is offline
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346

    Re: Pulling detailed data from one sheet to another

    Wow that is pretty cool how you did that. I will work on this over the next couple days and see if I need additional help intergrating into my main form.

    Thanks!
    Reply With QuoteRegister To Reply
  4. 05-04-2009, 06:21 PM #4
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Pulling detailed data from one sheet to another

    Sorry, I made an error on the Variance tracking sheet, the formula in B24 should be:

    =MATCH('Employee Search'!B2,'Variance Tracking'!A:A,0)
    Reply With QuoteRegister To Reply
  5. 05-07-2009, 12:58 PM #5
    avidcat
    avidcat is offline
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346

    Re: Pulling detailed data from one sheet to another

    Thanks again I have the formula working.

    I am trying to also have an absolute value "abs" of the total variances on the employee page below the net value but I am getting an error with the formula I am using:

    =SUM(ABS(B9:B200))

    I am getting #VALUE!

    When I tried this one:

    =ABS(SUM(B9:B200))

    It just gave me the NET total as a positive number, not sure why.

    Is there another way to get the ABS?

    Thanks
    Reply With QuoteRegister To Reply
  6. 05-07-2009, 01:11 PM #6
    windknife
    windknife is offline
    Forum Expert
    Join Date
    04-14-2009
    Location
    Taiwan
    MS-Off Ver
    Excel 2016,2019,O365
    Posts
    2,941

    Re: Pulling detailed data from one sheet to another

    =SUM(ABS(B9:B200))

    It's ok, but you must commit with Ctrl+Shift+Enter.
    Reply With QuoteRegister To Reply
  7. 05-07-2009, 01:29 PM #7
    avidcat
    avidcat is offline
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346

    Re: Pulling detailed data from one sheet to another

    I did do the Ctrl Shift Enter, but it still shows up #VALUE!

    I wonder if it has something to do with the formula that is being used in the cells with the amounts in column B?
    Reply With QuoteRegister To Reply
  8. 05-07-2009, 01:45 PM #8
    windknife
    windknife is offline
    Forum Expert
    Join Date
    04-14-2009
    Location
    Taiwan
    MS-Off Ver
    Excel 2016,2019,O365
    Posts
    2,941

    Re: Pulling detailed data from one sheet to another

    try this

    =SUM(ABS(IF(ISNUMBER(B9:B200),B9:B200)))

    Commit with Ctrl+Shift+Enter
    Reply With QuoteRegister To Reply
  9. 05-07-2009, 01:54 PM #9
    avidcat
    avidcat is offline
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346

    Re: Pulling detailed data from one sheet to another

    Thanks that worked.
    Reply With QuoteRegister To Reply
  10. 06-18-2009, 03:07 PM #10
    avidcat
    avidcat is offline
    Forum Contributor
    Join Date
    12-15-2005
    MS-Off Ver
    Office 2007
    Posts
    346

    Re: Pulling detailed data from one sheet to another

    I am coming back to this worksheet that a few people here helped me design. I have been using it and it has been working great.

    Something new I was wondering if it would work. Can a comment on a cell on one sheet be populated into a cell on another sheet as text?

    That is the best I can explain it. If you take a look at the working sample I have you can see what I am trying to do.

    On the attached example, cashier #6 had a variance on 03/02/2009 for $25.00 I place a comment on this cell. On the Employee Search page where the date and variance show up I would like the comment to be shown in column C next to the date/variance.

    The file attached is saved in Excel 2007.

    Thanks,
    Nick
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  11. 06-18-2009, 04:01 PM #11
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Pulling detailed data from one sheet to another

    Not as a builtin function, I don't believe. But others have faced and solved this in the past. Ozgrid.com lists a UDF that will do what you want:
    Function MyComment(rng As Range)
'ozgrid.com
    Application.Volatile
    Dim str As String
    str = Trim(rng.Comment.Text)
'// If you want to remove Chr(10) character from string, then
    str = Application.Substitute(str, vbLf, " ")
    MyComment = str
End Function
    (source)

    Install that into a regular module. Then you can use it in a cell to grab the "comment" from another cell, like so:

    =MyComment(A2)
    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