How Do I make a formula look up a column by its Header Row??

[soundengineer]

Heres the Excel file I am using

http://www.audiohelix.com/excel%20fo...Calibrator.xls

I need to make this formula in the "Converter Efficiency" box on the first page

=(AVERAGEIF('Data Logged'!E:E,"=100",'Data Logged'!S:S))/(AVERAGEIF('Data Logged'!E:E,"=100",'Data Logged'!C:C)

replace the 'Data Logged'!E:E with a way to lookup and find "Throttle Position (SAE) %" on the "Data Logged" page

the problem is that the Data Logged Page is an end user "cut and paste from another program" page..
and the different types of data(represented by the first line of each column) may end up in a different location for each user

what I want to do is make it so that if "Throttle Position (SAE) %" ends up in column Q ...that my formulas still work...by making it search for "Throttle Position (SAE) %" column instead of having to say 'Data Logged'!E:E(which is absolute positioning)

I will eventually do this for all of my formula's..but if somebody can help me with the first one..then I should be able to do the rest

[mikerickson]

It sounds like you could use named ranges.

If you are sure that the user will put their headers in row 1, then
MATCH("Throttle Position (SAE) %",DataLogged!$1:$1,0) will return the column number of that header.


=OFFSET(DataLogged!$A$1,0,MATCH("Throttle Position (SAE) %",DataLogged!$1:$1,0),1000,1) will be the top 1000 cells in the Throttle Postion column.

If you Insert Menu - Name... - Define...

name=Throttle
refers to: =OFFSET(DataLogged!$A$1,0,MATCH("Throttle Position (SAE) %",DataLogged!$1:$1,0),1000,1)

The name Throttle will always refer to the appropriate column.

[soundengineer]

It sounds like you could use named ranges.

If you are sure that the user will put their headers in row 1, then
MATCH("Throttle Position (SAE) %",DataLogged!$1:$1,0) will return the column number of that header.


=OFFSET(DataLogged!$A$1,0,MATCH("Throttle Position (SAE) %",DataLogged!$1:$1,0),1000,1) will be the top 1000 cells in the Throttle Postion column.

If you Insert Menu - Name... - Define...

name=Throttle
refers to: =OFFSET(DataLogged!$A$1,0,MATCH("Throttle Position (SAE) %",DataLogged!$1:$1,0),1000,1)

The name Throttle will always refer to the appropriate column.

so make that formula I posted there read like you are trying to explain so I understand...cause I'm not 100% sure I get what I should put in there..

[soundengineer]

Ok..so I tried

name=Throttle
scope= Data Logged
refers to: =OFFSET(DataLogged!$A$1,0,MATCH("Throttle Position (SAE) %",DataLogged!$1:$1,0),65535,1)

in place of one of the 'Data Logged'!E:E in the formula
so now it reads
=(AVERAGEIF(Throttle,"=100",'Data Logged'!S:S))/(AVERAGEIF('Data Logged'!E:E,"=100",'Data Logged'!C:C))

I get back a #NAME?

[mikerickson]

Use a larger scope. The scope must include the sheet on which the name appears.

[soundengineer]

Use a larger scope. The scope must include the sheet on which the name appears.

I use the workbook as the scope and it asks me for a file??? what file should I use??

[soundengineer]

Use a larger scope. The scope must include the sheet on which the name appears.

so obviously I'm not feeling smart enough to make this work cause nothing I try works...

can somebody please make it happen and repost the file or even pm me and I'll send you an e-mail adress or something.... or walk me through it step by step

I am using Excel 2007
and doing this in 97-03 compatibility so that it works for more people

[soundengineer]

Bump cause I need more help since I cant seem to make it work and dont know what I'm doing wrong

[soundengineer]

anybody??
can anybody make it work??
cause I cant and dont know what I'm doing wrong..
I tried a bigger "scope"
but I dont know what to do when it propmts for a file??

and I cant figure this out without the help of others