Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Returning cell value if 2 criteria are met | New Users to Excel | |||
Returning a value in one cell based on the criteria of another cel | Excel Worksheet Functions | |||
Returning all values based on 2 criteria... | Excel Discussion (Misc queries) | |||
Returning Results Based on Two Criteria | Excel Worksheet Functions | |||
returning a value based on mulitple criteria | Excel Worksheet Functions |