Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching through multiple worksheets
I'm sure the answer is in here somewhere but I've been reading posts that I
just find confusing and I feel like there must be a simple answer that I'm missing. I have a workbook with multiple worksheets and I have created a list of the worksheet names (I named it sheetnames) and put it into a dropdown list in cell C4 of my summary worksheet. I would like to find a formula that will list cells b:4 to B100 from whichever worksheet name is chosen in cell C4. Is this possible? Thanks very much |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching through multiple worksheets
With C4 as the DV cell containing the sheetnames
select a desired result range, eg select D4:D100 (with D4 active), then paste this into the formula bar: =IF(C4="","",INDIRECT("'"&C4&"'!B4:B100")) then press CTRL+SHIFT+ENTER to array-enter the formula You should get the returns you seek in D4:D100 based on the sheetname selected in C4 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Klee" wrote: I'm sure the answer is in here somewhere but I've been reading posts that I just find confusing and I feel like there must be a simple answer that I'm missing. I have a workbook with multiple worksheets and I have created a list of the worksheet names (I named it sheetnames) and put it into a dropdown list in cell C4 of my summary worksheet. I would like to find a formula that will list cells b:4 to B100 from whichever worksheet name is chosen in cell C4. Is this possible? Thanks very much |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching through multiple worksheets
Wow that works great! Thanks so much.
I've never used Indirect or Ctrl+Shift+Enter before so I'm not sure how it works with If's. I wanted to figure out how to search in the worksheets without confusing the reader but what I really need the formula to do is this... Like I said, the worksheet name is in cell C4 of the summary page (in a drop down list) and the information that I need to compare is in cells B:4:B:100 of each worksheet. I have a list on my summary sheet in cells C100;C150. I would like the formula to return the names in the list (C100:C150) only if they are not found in the worksheet named in cell C4 in cells B4:B100 Is there a way to add this into the formula that you already provided =IF(C4="","",INDIRECT("'"&C4&"'!B4:B100")) or have I just entirely confused you. Thanks so much for the help! "Max" wrote: With C4 as the DV cell containing the sheetnames select a desired result range, eg select D4:D100 (with D4 active), then paste this into the formula bar: =IF(C4="","",INDIRECT("'"&C4&"'!B4:B100")) then press CTRL+SHIFT+ENTER to array-enter the formula You should get the returns you seek in D4:D100 based on the sheetname selected in C4 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Klee" wrote: I'm sure the answer is in here somewhere but I've been reading posts that I just find confusing and I feel like there must be a simple answer that I'm missing. I have a workbook with multiple worksheets and I have created a list of the worksheet names (I named it sheetnames) and put it into a dropdown list in cell C4 of my summary worksheet. I would like to find a formula that will list cells b:4 to B100 from whichever worksheet name is chosen in cell C4. Is this possible? Thanks very much |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching through multiple worksheets
Wow that works great! Thanks so much.
No prob, welcome .. but what I really need the formula to do is this... Ah so, the *real* question surfaces. This is a different ballgame Try this non-array pitch: In C4 is DV for the sheetnames as before In D4: =IF($C$4="","",IF(C100="","",IF(ISNUMBER(MATCH(C10 0,INDIRECT("'"&$C$4&"'!B4:B100"),0)),"",ROW()))) In E4: =IF(ROWS($1:1)COUNT($D$4:$D$54),"",INDEX($C$100:$ C$150,SMALL($D$4:$D$54,ROWS($1:1))-3)) Select D4:E4, copy down to E54 to return the required results, all neatly bunched at the top, viz as per your specs: .. to return the names in the list (C100:C150) only if they are not found in the worksheet named in cell C4 in cells B4:B100 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Klee" wrote: Wow that works great! Thanks so much. I've never used Indirect or Ctrl+Shift+Enter before so I'm not sure how it works with If's. I wanted to figure out how to search in the worksheets without confusing the reader but what I really need the formula to do is this... Like I said, the worksheet name is in cell C4 of the summary page (in a drop down list) and the information that I need to compare is in cells B:4:B:100 of each worksheet. I have a list on my summary sheet in cells C100;C150. I would like the formula to return the names in the list (C100:C150) only if they are not found in the worksheet named in cell C4 in cells B4:B100 Is there a way to add this into the formula that you already provided =IF(C4="","",INDIRECT("'"&C4&"'!B4:B100")) or have I just entirely confused you. Thanks so much for the help! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching through multiple worksheets
Wow that works great! Thanks so much.
No prob, welcome .. but what I really need the formula to do is this... Ah so, the *real* question surfaces. This is a different ballgame Try this non-array pitch: In C4 is DV for the sheetnames as before In D4: =IF($C$4="","",IF(C100="","",IF(ISNUMBER(MATCH(C10 0,INDIRECT("'"&$C$4&"'!B4:B100"),0)),"",ROW()))) In E4: =IF(ROWS($1:1)COUNT($D$4:$D$54),"",INDEX($C$100:$ C$150,SMALL($D$4:$D$54,ROWS($1:1))-3)) Select D4:E4, copy down to E54 to return the required results in col E**, all neatly bunched at the top, viz as per your specs: .. to return the names in the list (C100:C150) only if they are not found in the worksheet named in cell C4 in cells B4:B100 **Minimize/Mask D4:D54 for a neater appearance -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Klee" wrote: Wow that works great! Thanks so much. I've never used Indirect or Ctrl+Shift+Enter before so I'm not sure how it works with If's. I wanted to figure out how to search in the worksheets without confusing the reader but what I really need the formula to do is this... Like I said, the worksheet name is in cell C4 of the summary page (in a drop down list) and the information that I need to compare is in cells B:4:B:100 of each worksheet. I have a list on my summary sheet in cells C100;C150. I would like the formula to return the names in the list (C100:C150) only if they are not found in the worksheet named in cell C4 in cells B4:B100 Is there a way to add this into the formula that you already provided =IF(C4="","",INDIRECT("'"&C4&"'!B4:B100")) or have I just entirely confused you. Thanks so much for the help! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Searching through multiple worksheets
The last para should read as:
Select D4:E4, copy down to E54 to return the required results in col E**, all neatly bunched at the top, viz as per your specs: .. to return the names in the list (C100:C150) only if they are not found in the worksheet named in cell C4 in cells B4:B100 **Minimize/Mask D4:D54 for a neater appearance -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching Worksheets | Excel Discussion (Misc queries) | |||
Searching for multiple strings and return multiple solutions | Excel Worksheet Functions | |||
Searching Across Worksheets: Please help! | New Users to Excel | |||
vba searching for strings in multiple worksheets | Excel Discussion (Misc queries) | |||
searching multiple worksheets | Excel Worksheet Functions |