Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I want to use a link to return all data matching a criteria
I have 2 worksheets, one with time used and the second with parts used. They
are tied together with an ID number. I want to create a link on the first spreadsheet that returns all the values that match on the second spreadsheets. I would like the matching results displayed in a third worksheet. I know I can do this using SQL and ASP, but the request is that I distribute this is an Excel SS. I am using Excel2003. Is this too much to hope for? Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I want to use a link to return all data matching a criteria
This is going to be a long one.
First of all sort your list of creat a list as ID, time, and part. Sort this list on ID. On the results worksheets, say your ID is in column A at A2. (Row 1 is for heading). type this formula in B2 for time- =IF(ISERROR(OFFSET(Sheet2!A1,MATCH(A2,Sheet2!$A2:$ A$8,0),1)),"",OFFSET(Sheet2!A2,MATCH(A2,Sheet2!$A2 :$A$8,0),1)) All that I am using is the offset() and match() formula here. This formula will give a corresponding value is the second column if the match is found. Moreover, the match function will always return the next instance of the ID number as you may notice the starting of the serach range for MATCH is not absolute. -- Pranav Vaidya VBA Developer PN, MH-India If you think my answer was useful, please rate it. "Dave" wrote: I have 2 worksheets, one with time used and the second with parts used. They are tied together with an ID number. I want to create a link on the first spreadsheet that returns all the values that match on the second spreadsheets. I would like the matching results displayed in a third worksheet. I know I can do this using SQL and ASP, but the request is that I distribute this is an Excel SS. I am using Excel2003. Is this too much to hope for? Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
I want to use a link to return all data matching a criteria
Am I allowed to send a spreadsheet?
"Pranav Vaidya" wrote: This is going to be a long one. First of all sort your list of creat a list as ID, time, and part. Sort this list on ID. On the results worksheets, say your ID is in column A at A2. (Row 1 is for heading). type this formula in B2 for time- =IF(ISERROR(OFFSET(Sheet2!A1,MATCH(A2,Sheet2!$A2:$ A$8,0),1)),"",OFFSET(Sheet2!A2,MATCH(A2,Sheet2!$A2 :$A$8,0),1)) All that I am using is the offset() and match() formula here. This formula will give a corresponding value is the second column if the match is found. Moreover, the match function will always return the next instance of the ID number as you may notice the starting of the serach range for MATCH is not absolute. -- Pranav Vaidya VBA Developer PN, MH-India If you think my answer was useful, please rate it. "Dave" wrote: I have 2 worksheets, one with time used and the second with parts used. They are tied together with an ID number. I want to create a link on the first spreadsheet that returns all the values that match on the second spreadsheets. I would like the matching results displayed in a third worksheet. I know I can do this using SQL and ASP, but the request is that I distribute this is an Excel SS. I am using Excel2003. Is this too much to hope for? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sorting and Matching criteria across rows to return a result in the last cell | Excel Worksheet Functions | |||
Return across Row Numeric Values Matching EXACT Month & Year for Criteria | Excel Worksheet Functions | |||
Return Numeric Values Matching EXACT Date for Criteria | Excel Worksheet Functions | |||
Return Matching Data Help Needed | Excel Discussion (Misc queries) | |||
return multiple records matching multiple criteria | Excel Worksheet Functions |