+ Reply to Thread
Results 1 to 8 of 8

Need to fill cells by utilizing data from two different worksheets

  1. #1
    Registered User
    Join Date
    08-10-2012
    Location
    Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    9

    Need to fill cells by utilizing data from two different worksheets

    Hi guys,

    I have a sample dataset that contains investment returns from 20 different projects. The twenty projects are listed vertically. Each project lasted anywhere between 3 to 9 days. The dates are listed on the top row.

    -In my first worksheet, "active days", I have data that tells me which days the investment was active. If the project was inactive on that date, the cell says "NA." If it was active, then it lists the "nth" day which it's been active.

    -In my second worksheet, "gross return", I have data that tells me what the gross return was, for specific dates.

    -In my third worksheet, "Return Standardized to Duration", I am trying to standardize my return data according to duration, so I can compare all the "Day 1 return" together vertically, and then compare the "Day 2 return" vertically, etc.

    Essentially, for each cell in the third sheet, I want Excel to:
    1. read the "Investment Location," in the first column
    2. go to the first (leftmost) worksheet and grab the relevant "nth day" info
    3. then I want it to use the "nth day" information, along with the corresponding specific date, (i.e. 1/5/1993) go to the second worksheet
    4. grab the gross return that corresponds to the date

    In my sample spreadsheet, third worksheet, I've filled in two rows of data by hand, Atlanta and San Paolo, as examples. (they're highlighted in light orange) Ideally, I would like a smart formula to do this because my real data set is really really big.

    I've been trying to do a combination of hlookup with vlookup, with little success.

    I do not know how to code or do macros. I would appreciate any ideas.

    Thank you.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

    Re: Need to fill cells by utilizing data from two different worksheets

    Hello there,

    I'm sorry, I'm a little confused as to what you are doing I get where and why you are pulling the information in the third worksheet from cells F2:H2 but am unsure where or why the remainder of the columns are populated.

    Could you please explain, a little more.

    Thanks!

  3. #3
    Registered User
    Join Date
    08-10-2012
    Location
    Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Need to fill cells by utilizing data from two different worksheets

    In the 3rd worksheet, In rows 2 and 16, colulmns F2:O2, I entered those by hand.

    I would like to find a way to NOT do it by hand.

  4. #4
    Valued Forum Contributor
    Join Date
    05-08-2012
    Location
    Georgia, USA
    MS-Off Ver
    Excel 2003, 2010
    Posts
    811

    Re: Need to fill cells by utilizing data from two different worksheets

    This formula works:

    IF(ISERROR(VLOOKUP($A2,'gross return'!$A$2:$S$21,MATCH(F$1,'active days'!$F2:$S2,0)+5)),"NA",VLOOKUP($A2,'gross return'!$A$2:$S$21,MATCH(F$1,'active days'!$F2:$S2,0)+5))


    Please Login or Register  to view this content.
    Attached is your spreadsheet with the formula
    Attached Files Attached Files
    Last edited by K m; 08-23-2012 at 04:41 PM.
    Click on star (*) below if this helps

  5. #5
    Registered User
    Join Date
    08-10-2012
    Location
    Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Need to fill cells by utilizing data from two different worksheets

    Thank you. What is the purpose of the "+5" in the MATCH function?
    Last edited by Cutter; 08-23-2012 at 07:45 PM. Reason: Removed whole post quote

  6. #6
    Valued Forum Contributor
    Join Date
    05-08-2012
    Location
    Georgia, USA
    MS-Off Ver
    Excel 2003, 2010
    Posts
    811

    Re: Need to fill cells by utilizing data from two different worksheets

    Look at your data. Vlookup is looking for the correct column. I added 5 to the column number to fit your data

    Did the data work for you?

  7. #7
    Registered User
    Join Date
    08-10-2012
    Location
    Washington DC
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: Need to fill cells by utilizing data from two different worksheets

    Yes, thank you very much K m.

    It definitely works. I'm just slowing absorbing the logic behind it.

    Have a good one.

  8. #8
    Valued Forum Contributor
    Join Date
    05-08-2012
    Location
    Georgia, USA
    MS-Off Ver
    Excel 2003, 2010
    Posts
    811

    Re: Need to fill cells by utilizing data from two different worksheets

    =MATCH(F$1,'active days'!$F2:$S2,0) This equation finds out what column there is a MATCH
    Since your data table starts on column F, we need to add 5 columns ie (Column F - Column A) = 5

    There is a check to see if there is an error and if there is return a "NA"

    There rest of the equation is a simple VLOOKUP where it matchs the place and returns the information from the column from MATCH+5

+ 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