returning cell value based upon 3 criteria
 

Wondering if anyone can help with this.

I need to return a cell value fom a different sheets

the sheet with the values in is laid out as follows.

column A column B Columns C-Z
Persons Name Date work Done Quantities of work done


Each Person has a sheet named after themselves and this is returned is cell
A3 on the individuals sheet.
On the individuals sheet the dates are in column B, but the columns C-Z are
in a different order.

So I need to match the persons name (columnn A) with the date (column B)
with a column heading (C-Z)

The make things worse, on the individuals sheets I'll actually be looking
for data from two differemt sheets.
(work is completed by two different people and I'm responsible for combining
it.)

I know its a combination of index, match and sumproduct (I think) but I
can't get it to work.

Heres how I would return it using one criteria, (in this case the persons
name.)
=INDEX(quantity!1:65536,MATCH(A3,quantity!A:A,0),5 )

Any help greatly appreciated.

cheers

Andy

returning cell value based upon 3 criteria
 

You may find some help here
http://www.cpearson.com/excel/topic.htm

Mike


"Andy Healey" wrote in message
...
Wondering if anyone can help with this.

I need to return a cell value fom a different sheets

the sheet with the values in is laid out as follows.

column A column B Columns C-Z
Persons Name Date work Done Quantities of work done


Each Person has a sheet named after themselves and this is returned is
cell
A3 on the individuals sheet.
On the individuals sheet the dates are in column B, but the columns C-Z
are
in a different order.

So I need to match the persons name (columnn A) with the date (column B)
with a column heading (C-Z)

The make things worse, on the individuals sheets I'll actually be looking
for data from two differemt sheets.
(work is completed by two different people and I'm responsible for
combining
it.)

I know its a combination of index, match and sumproduct (I think) but I
can't get it to work.

Heres how I would return it using one criteria, (in this case the persons
name.)
=INDEX(quantity!1:65536,MATCH(A3,quantity!A:A,0),5 )

Any help greatly appreciated.

cheers

Andy

returning cell value based upon 3 criteria
 

=Index(Indirect(cell with name & "A1:Z32"),Match(Cell with
Date,Indirect(Cell with name & "A2:A32"),0),Match(Cell with Column
Label,Indirect(cell with name & "B1:Z1"),0))

sumproduce wouldn't be involved. for two people, use the same formula
twice; combine with a plus sign
--
Regards,
Tom Ogilvy

Andy Healey wrote in message
...
Wondering if anyone can help with this.

I need to return a cell value fom a different sheets

the sheet with the values in is laid out as follows.

column A column B Columns C-Z
Persons Name Date work Done Quantities of work done


Each Person has a sheet named after themselves and this is returned is
cell
A3 on the individuals sheet.
On the individuals sheet the dates are in column B, but the columns C-Z
are
in a different order.

So I need to match the persons name (columnn A) with the date (column B)
with a column heading (C-Z)

The make things worse, on the individuals sheets I'll actually be looking
for data from two differemt sheets.
(work is completed by two different people and I'm responsible for
combining
it.)

I know its a combination of index, match and sumproduct (I think) but I
can't get it to work.

Heres how I would return it using one criteria, (in this case the persons
name.)
=INDEX(quantity!1:65536,MATCH(A3,quantity!A:A,0),5 )

Any help greatly appreciated.

cheers

Andy