Header row is not row 1...how to look up by name listed in another row?

**soundengineer** · 12-21-2008, 12:23 PM

I have a data file that basically has some misc. info along with some data markers in the top few rows...
My header row sometimes ends up row 8 sometimes row 12....sometimes row ?

so I want to know how to look up the title of the header...and then tell it to use the data below it...

in my example...I want to lookup the times tamp on sheet1 and move it to a different page converted to a decimal

**Ron Coderre** · 12-21-2008, 01:13 PM

Using your posted workbook.....Try this:

On the Get Data sheet

Please Login or Register  to view this content.

Copy that formula down as far as you need.

Is that something you can work with?

**soundengineer** · 12-21-2008, 01:40 PM

Originally Posted by Ron Coderre

Using your posted workbook.....Try this:

On the Get Data sheet

Please Login or Register  to view this content.

Copy that formula down as far as you need.

Is that something you can work with?

that works if its always in row B
the problem I have is that other things I need to reference are in different rows...
the data is from a program that the user can add or subtract different variables to log data from...
because of that some of the other data I use ends up in different columns...
one log might ave MPH in column [C] while another might have it in Column [F]

is there a way to look it up and not have to have it land in a specific column on my data sheet?

**Ron Coderre** · 12-21-2008, 05:05 PM

OK....I understand.

First
...."rows" go across from left to right and are numbered
...."columns" go up/down and are lettered

Next....I attached an edited version of your workbook that demonstrates
an approach to locating the data and retrieving values.

On the Get Data sheet
Assuming ALL data is driven by a Frame reference, this formula
determines which row contains "Frame"

Please Login or Register  to view this content.

Row_3, which will ultimately be hidden, contains that ACTUAL COLUMN HEADINGS, from the data.

Row_4 contains formulas that determine which columns in the data...and...
on the C1 row, contain the headings from Row_3. I entered various
headings in random order on the workbook I attached.

So....This formula returns the first data item for "Time" (which is in
the first column of my sample sheet)

Please Login or Register  to view this content.

That formula is copied to C4 and the *86400 is removed (unnecessary)

Please Login or Register  to view this content.

The formulas can be copied across and down (adjusted as necessary for
other calculations).

Does that get you headed in the right direction?

**soundengineer** · 12-21-2008, 11:11 PM

can you help me understand that....
I will have to repeat it on another page....and I dont get what I am looking at..

the "MATCH" thing I get thats easy
What does the "ROWS" function do exactly?
why does it get to change from ($4:4) to ($4:5)
I tried to look it up in excel help...but the help file is not any help explaining...

**Ron Coderre** · 12-22-2008, 10:18 AM

The formula in C1 determines which row contains the report headings.

The ROWS($4:4) function counts the number of rows in Row_4 (one row).
The formula adds 1 to C1....telling the function to read the 1st data row.
If C1 returns 9, then 9+1=10...the 1st row below the headings.

When copied down one row, ROWS($4:4) becomes ROWS($4:5) and
the counts the number of rows in Rows 4 and 5 (two!). Now, when
added to the heading row in C1, the formula returns information from
the second data row. Again, if C1 returns 9, then 9+2=11...the 2nd row
below the headings.

Does that help?

**soundengineer** · 12-22-2008, 11:24 AM

Originally Posted by Ron Coderre

The formula in C1 determines which row contains the report headings.

The ROWS($4:4) function counts the number of rows in Row_4 (one row).
The formula adds 1 to C1....telling the function to read the 1st data row.
If C1 returns 9, then 9+1=10...the 1st row below the headings.

When copied down one row, ROWS($4:4) becomes ROWS($4:5) and
the counts the number of rows in Rows 4 and 5 (two!). Now, when
added to the heading row in C1, the formula returns information from
the second data row. Again, if C1 returns 9, then 9+2=11...the 2nd row
below the headings.

Does that help?

are you using the rows function so it will auto incriment when copied?
and why isnt it ROWS(1:1) and then (1:2) and so forth??
why start wih (4:4)

**Ron Coderre** · 12-22-2008, 11:43 AM

Since we're only counting rows...not referencing row numbers...it makes
more sense to reference the row the formula is on, instead of another row.
That makes the formula more durable against row deletions above the
formula range.

Example:
A10: =ROWS($10:10)
returns: 1

If we delete Row_1...the formula becomes:
A9: =ROWS($9:9)
and still returns 1

BUT....if the original formula was:
A10: =ROWS($1:1)
and we delete Row_1...the formula now changes to
A9: =ROWS(#REF!)
and returns an error (instead of the 1 we were looking for)

**soundengineer** · 12-22-2008, 09:40 PM

so now that I've made a ton of these boxes reading with lots of index and row and match functions.....an entire sheet worth....from top to bottom and several columns wide worth
how do I make it quicker??
cause now its painfully slow for me...

I am using office 2007 on vista