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

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.

---

Originally Posted by p1tbullz

=MATCH(A11,metrics,0) comes back as #NA

what is the range of your 'metrics'?

---

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?

---

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
---