Special Index/Match formula

[Todd Huttenstine]

Hey guys

I have a table that currently has 21 items in cells A8:A29
on worksheet "Work Orders". This range can vary in size.
It can have less items or it can have more items, but at
the very bottom of the table, it will say "Grand Total"(In
this case Grand Total is in cell A29 - This means all
Grand Total will be on row 29).

This table has names going accross row 7 in range B7:L7.
This table will always have names in row 7. This range
can vary in size as well, but I dont think that is going
to be an issue.

I have another worksheet called "Graph". The table on
worksheet "Graph" is setup in the exact same way as the
table on worksheet "Work Orders" is setup. All I am
interested in, is retrieving the grand total for each name
on worksheet "Work Orders". I am currently using the
following formula to retrieve the Grand Total for the name
in cell B9.

=INDEX('Work Orders'!29:29,MATCH(Graph!B9,'Work Orders'!
7:7,0))

In the formula above, the name in cell B9 on
worksheet "Graph" is "Todd Huttenstine". The formula goes
out to worksheet "Work Orders" and looks in row 7 for the
matching name (in this case the name is Todd
Huttenstine). When it finds the match, it looks on row 29
and returns the corresponding value(in this case its the
Grand total I am trying to retrieve). If the grand Total
under my name is 27, the formula will return the value 27.


This formula works great assuming Grand Total is ALWAYS on
row 29. If the table on worksheet "Work Orders" changes to
where Grand Total is on any other row other than row 29,
the formula will not work because it is still returning
the value in row 29.

What formula can I use to make it return the Grand Total
value regardless of what row Grand Total is on?


Thanks
Todd Huttenstine

 

[Guest]

You already have most of the answer! If you use the Match function in column
A, you should be able to find the row that says "Grand Total" - you can use
it along with the column number you found. However, rather than use the
INDEX function I think it is easier to use OFFSET, which you can base from a
fixed point (cell A1):
=OFFSET('Work Orders'!A1,MATCH('Work
Orders'!A:A,"GrandTotal",0)-1,MATCH(Graph!B9,'Work Orders'!7:7,0)-1)

 

[Todd Huttenstine]

hey

thanks so much for that formula. How does this formula
work exactly using A1?