Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function Help
I am trying to write a function that will refernece to data valadation lists
then go and find the corresponding data. Below is an example of what I am tring to get. =IF(DATA!A2:A62000=India!C5,AND(IF(DATA!B2:B62000= India!C6,17,"No Data"))) IndiaC5 is a list of dates IndiaC6 is a list of names I want to find the first match of C5 and C6 then go over 17 rows and give me that data. I cannot use a pivot table because this is a template for survey results, and I am trying to pull just the contents from column R back into this cell. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function Help
Scott,
Your explanation does not make sense, at least not to me. If you want the data from Column R, (presumably in the Data sheet), where the date in Column A of the Data sheet matches the date in cell C5 of the India sheet and the date in Column B of the Data sheet matches the date in cell C6 of the India sheet then try: =IF(SUMPRODUCT((DATA!A2:A3001=India!C5)*(DATA!B2:B 3001=India!C6)*ROW(2:3001))=0,"No Data",INDEX(DATA!R2:R3001,SUMPRODUCT((DATA!A2:A300 1=India!C5)*(India!B2:B3001=C6)*ROW(2:3001)))) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Scott@CW" wrote in message ... I am trying to write a function that will refernece to data valadation lists then go and find the corresponding data. Below is an example of what I am tring to get. =IF(DATA!A2:A62000=India!C5,AND(IF(DATA!B2:B62000= India!C6,17,"No Data"))) IndiaC5 is a list of dates IndiaC6 is a list of names I want to find the first match of C5 and C6 then go over 17 rows and give me that data. I cannot use a pivot table because this is a template for survey results, and I am trying to pull just the contents from column R back into this cell. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function Help
I appreciate your help. I was able to make it work using a mutli vlookup.
To clarify cells C5 and C6 both contain a data valadation list of information. The "master sheet" allowed a user to select a name and date from those list and pull in survey data into the formated cells below. The problem I was having had to do with the last part of the data which was a comment section. All the comments are pasted into a cell in column R on the data sheet on the first row of each persons name. Thank you for your formula, I am going to try it as well. "Sandy Mann" wrote: Scott, Your explanation does not make sense, at least not to me. If you want the data from Column R, (presumably in the Data sheet), where the date in Column A of the Data sheet matches the date in cell C5 of the India sheet and the date in Column B of the Data sheet matches the date in cell C6 of the India sheet then try: =IF(SUMPRODUCT((DATA!A2:A3001=India!C5)*(DATA!B2:B 3001=India!C6)*ROW(2:3001))=0,"No Data",INDEX(DATA!R2:R3001,SUMPRODUCT((DATA!A2:A300 1=India!C5)*(India!B2:B3001=C6)*ROW(2:3001)))) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Scott@CW" wrote in message ... I am trying to write a function that will refernece to data valadation lists then go and find the corresponding data. Below is an example of what I am tring to get. =IF(DATA!A2:A62000=India!C5,AND(IF(DATA!B2:B62000= India!C6,17,"No Data"))) IndiaC5 is a list of dates IndiaC6 is a list of names I want to find the first match of C5 and C6 then go over 17 rows and give me that data. I cannot use a pivot table because this is a template for survey results, and I am trying to pull just the contents from column R back into this cell. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to combine an IF Function with a lookup function to determine | Excel Worksheet Functions | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions |