+ Reply to Thread
Results 1 to 7 of 7

Linking Charts to rows on separate worksheet

  1. #1
    Registered User
    Join Date
    07-08-2011
    Location
    colorado
    MS-Off Ver
    Excel 2010
    Posts
    3

    Linking Charts to rows on separate worksheet

    My first worksheet has rows with each one being a summary of data gathered from a single chart in another worksheet. The second worksheet contains a number of charts each one is designated a different date. How do I tell one of the rows in the first worksheet to search for a chart in the second by date, and then in that chart choose a certain cell that is for example 3 colums over and 5 rows down from the cell where the date is located? Thank you much!

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    19,680

    Re: Linking Charts to rows on separate worksheet

    Welcome to the Forum!

    I think you're going to need to attach your workbook. I can't figure out what you mean by this:
    ...a summary of data gathered from a single chart in another worksheet
    I know of no way for a formula to refer to data in a chart. When you say "chart" do you mean data in tabular format, rather than a graph? If so, I think your problem is straightforward but we need to see the layout.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    07-08-2011
    Location
    colorado
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Linking Charts to rows on separate worksheet

    So, If you look at the sheet "Annual Summary" at cell B33, I need that to be the value in the sheet" Individual summary" Cell E3. The problem is however that for cell B35 in the first sheet I need the value of cell E20 of the next sheet. So what I want to do is have the cell in the first sheet search for the date of the charts in the second sheet. Once it finds the correct date I want it to look in that chart ie. 12/Jan/11 for the data labeled PZF etc. Once it finds PZF in the chart of 12/Jan/11 I want it to return the value in the column 4 over from PZF ie. the elevation of the PZF. This is a rather pedantic question, but I can't figure out how to find that garbage...Thank you so much!
    Attached Files Attached Files

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    19,680

    Re: Linking Charts to rows on separate worksheet

    The layout on IndividualSummary is not friendly to what you need to do. I would put everything blocked out under one set of columns, instead of spread around the page. You can do a search to match a date on a column, or in a row, but not anywhere in the sheet.

    There are two blocks that both are labeled as "F6 VG 12-Jan-11". They seem to have the same data. Then mmblop doesn't have any date at all. It is unclear why there would be a duplicate or what is intended if there is no date.

    Each block of data seems to have the same organization so I have made that an assumption, rather than doing a search on "RM3(X)". So the formula finds the date then returns the value two cells below.

    See attached, where I have changed the layout of IndividualSummary so that dates are all in the same column. I provided formulas for the two cells you mentioned in your descriptions (highlighted in red). However, there is not enough data to match all those dates so I'm not sure where you are going with this.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-08-2011
    Location
    colorado
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Linking Charts to rows on separate worksheet

    Thank you for the quick reply!
    What I need however is to find the chart such as 28-Nov-10 which you did,
    but then I want to find the elevations of RM3(X), RM4, Orifice etc. of 6.228, 6.210, and 0.854 respectively.
    There are inserts for these numbers on the first worksheet "annual summary".
    And repeat this for all charts. Thank you again

  6. #6
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    19,680

    Re: Linking Charts to rows on separate worksheet

    If you look at the formulas I provided you will see that they can be easily moved into other columns and any row. The MATCH function returns the position of the date, and the INDEX function moves down the appropriate number of cells from where the date was found to look up the appropriate value. You can copy this formula, changing the number in INDEX to match the value you are looking for.

    You may need to look up the Help pages for those functions if you are not already familiar with them.

    (A more sophisticated (and robust) solution would do a VLOOKUP on the block under where the date was found, using the column header to determine which row to use, but that may be overkill for your situation.)

  7. #7
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    19,680

    Re: Linking Charts to rows on separate worksheet

    By the way, you have this in the Programming >> Charting forum, but your workbook involves neither programming nor charting so it's pretty much buried from most of the people who would be most likely to help.

+ 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