+ Reply to Thread
Results 1 to 9 of 9

referencing data on another sheet using row and column headers from current sheet

  1. #1
    Registered User
    Join Date
    02-01-2007
    Posts
    5

    Thumbs up referencing data on another sheet using row and column headers from current sheet

    I am a newbie and have a feeling this isn't that difficult however have been searching the forums and the similar items are a little more complex then what i'm trying to do but i can't get it to work.

    here is the scenerio:

    I have a summary sheet in which the column header is the name of another worksheet that my data is in. The row name on the summary sheet is the name of the data field in the data sheet i want to get pull.

    My intent is on the summary sheet to use the column name to go to the "data" sheet and the row name to find the cell with the data i need on that sheet.

    I am able to use indirect to go to the data sheet and in the correct location but don't know how to include a search using the row to find the data point.

    here is my example

    Worksheet1 is named-> Summary
    Column Name in b10 is "Data"
    Row Name in A11 is "total"

    Worksheet2 is named -> Data
    Cell E1 contains "Total"
    Cell F1 contains the value i want to reference on the summary sheet in cell b11

    I was able to use =INDIRECT(B10&"!f1") to get the value i want on the sheet but it is hard coded to that cell.

    I would like to be able to use the summary sheet row name "total" to find the location on the data sheet which is in f1.

    I was thinking match would accomplish this but i can't seem to make it work. here was my first try which was very unsucessful (I named the range with all the value on the data sheet metrics) metrics = $E$1:$L$4

    =MATCH(A11,INDIRECT(B10&"!metrics"),0)

    any help?

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote Originally Posted by p1tbullz
    I am a newbie and have a feeling this isn't that difficult however have been searching the forums and the similar items are a little more complex then what i'm trying to do but i can't get it to work.

    here is the scenerio:

    I have a summary sheet in which the column header is the name of another worksheet that my data is in. The row name on the summary sheet is the name of the data field in the data sheet i want to get pull.

    My intent is on the summary sheet to use the column name to go to the "data" sheet and the row name to find the cell with the data i need on that sheet.

    I am able to use indirect to go to the data sheet and in the correct location but don't know how to include a search using the row to find the data point.

    here is my example

    Worksheet1 is named-> Summary
    Column Name in b10 is "Data"
    Row Name in A11 is "total"

    Worksheet2 is named -> Data
    Cell E1 contains "Total"
    Cell F1 contains the value i want to reference on the summary sheet in cell b11

    I was able to use =INDIRECT(B10&"!f1") to get the value i want on the sheet but it is hard coded to that cell.

    I would like to be able to use the summary sheet row name "total" to find the location on the data sheet which is in f1.

    I was thinking match would accomplish this but i can't seem to make it work. here was my first try which was very unsucessful (I named the range with all the value on the data sheet metrics) metrics = $E$1:$L$4

    =MATCH(A11,INDIRECT(B10&"!metrics"),0)

    any help?
    Hi,

    if metrics = $E$1:$E$4

    then

    =MATCH(A11,metrics,0)

    will return the increment at which A11 appears within the range.

    ---
    Si fractum non sit, noli id reficere.

  3. #3
    Registered User
    Join Date
    02-01-2007
    Posts
    5
    =MATCH(A11,metrics,0) comes back as #NA

  4. #4
    Registered User
    Join Date
    02-01-2007
    Posts
    5
    and wouldn't I need to add 1 column to the right of the location that would be indicated by the match locations

  5. #5
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote Originally Posted by p1tbullz
    =MATCH(A11,metrics,0) comes back as #NA
    what is the range of your 'metrics'?

    ---

  6. #6
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote Originally Posted by p1tbullz
    and wouldn't I need to add 1 column to the right of the location that would be indicated by the match locations
    the Match is to determine whereabouts, within the stated range, the item exists.

    What is the purpose of this column?

    ---

  7. #7
    Registered User
    Join Date
    02-01-2007
    Posts
    5
    The Range of metrics on is $E$1:$L$4

    Within that range i have a counts of different factors within the data sheet. In the E column is the field name which is the same as on the summary sheet, then in the column imediately right (F) is the actual count that i want to display on the summary sheet...

    maybe match isn't the right formula..is there a way i could change that metrics table and use a Vlookup.

  8. #8
    Registered User
    Join Date
    02-01-2007
    Posts
    5
    I just realized that if i simply name each cell by the metric that it is referencing and when i have the multiple data sheets using indirect will work.

    Didn't think it would cary the named ranges with it.

    I should of remembered KISS

  9. #9
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote Originally Posted by p1tbullz
    I just realized that if i simply name each cell by the metric that it is referencing and when i have the multiple data sheets using indirect will work.

    Didn't think it would cary the named ranges with it.

    I should of remembered KISS
    with just a single Metrics named range, you could use

    =VLookup(A1,Metrics,2,False)

    to get your count from the 'next' column.

    hth
    ---

+ 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