| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to have Sheet1 list Sheet2 rows matching text?
I would like to have sheet1 list all rows in sheet2 matching "TEXT" in column
sheet2,A. Big TIA! -JS |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to have Sheet1 list Sheet2 rows matching text?
Hi,
Could you give an example -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "DevourU" wrote in message ... I would like to have sheet1 list all rows in sheet2 matching "TEXT" in column sheet2,A. Big TIA! -JS |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to have Sheet1 list Sheet2 rows matching text?
I was about to do it.
Sheet1 is blank. show only rows from sheet2 in sheet1 that have "TEXT" in column sheet2!A:A Actually I will want to return a few columns from the actual row, but I hope to do that myself once I can get past this. TIA! "Ashish Mathur" wrote: Hi, Could you give an example -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "DevourU" wrote in message ... I would like to have sheet1 list all rows in sheet2 matching "TEXT" in column sheet2,A. Big TIA! -JS |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to have Sheet1 list Sheet2 rows matching text?
Hi
Take a look at advanced Filter There are some good examples here http://www.contextures.com/xladvfilter01.html#ExtractWs -- Regards Roger Govier DevourU wrote: I was about to do it. Sheet1 is blank. show only rows from sheet2 in sheet1 that have "TEXT" in column sheet2!A:A Actually I will want to return a few columns from the actual row, but I hope to do that myself once I can get past this. TIA! "Ashish Mathur" wrote: Hi, Could you give an example -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "DevourU" wrote in message ... I would like to have sheet1 list all rows in sheet2 matching "TEXT" in column sheet2,A. Big TIA! -JS |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to have Sheet1 list Sheet2 rows matching text?
Maybe this may help, I know it's wrong, but get us on the same page.
sheet1A:A=If('Sheet2'!A:A="TEXT", 'Sheet2'!H:H, next row) "Ashish Mathur" wrote: Hi, Could you give an example -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "DevourU" wrote in message ... I would like to have sheet1 list all rows in sheet2 matching "TEXT" in column sheet2,A. Big TIA! -JS |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to have Sheet1 list Sheet2 rows matching text?
To return all rows with "text" in colA with all columns..
Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" Apply this formula in Sheet2 cell A1 and copy/drag down and to the right as required. =IF(COUNTIF(Sheet1!$A$1:$A$1000,"test")<ROW(A1),"" , INDEX(Sheet1!A$1:A$1000,SMALL(IF(Sheet1!$A$1:$A$10 00="test", ROW($A$1:$A$1000)),ROW(A1)))) -- Jacob (MVP - Excel) "DevourU" wrote: I would like to have sheet1 list all rows in sheet2 matching "TEXT" in column sheet2,A. Big TIA! -JS |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to have Sheet1 list Sheet2 rows matching text?
Thankx for the reply. Holy crap. Ya think it could be easier to
match/list/index rows from sheet2 matching "text" in sheet2!A:A into blank sheet1. Damn. I'll check it out tomorrow. -JS "Jacob Skaria" wrote: To return all rows with "text" in colA with all columns.. Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" Apply this formula in Sheet2 cell A1 and copy/drag down and to the right as required. =IF(COUNTIF(Sheet1!$A$1:$A$1000,"test")<ROW(A1),"" , INDEX(Sheet1!A$1:A$1000,SMALL(IF(Sheet1!$A$1:$A$10 00="test", ROW($A$1:$A$1000)),ROW(A1)))) -- Jacob (MVP - Excel) "DevourU" wrote: I would like to have sheet1 list all rows in sheet2 matching "TEXT" in column sheet2,A. Big TIA! -JS |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
How to have Sheet1 list Sheet2 rows matching text?
Try this: Copy sheet (Bottom tab) and delete figure columns
-- Naftoli "DevourU" wrote: Thankx for the reply. Holy crap. Ya think it could be easier to match/list/index rows from sheet2 matching "text" in sheet2!A:A into blank sheet1. Damn. I'll check it out tomorrow. -JS "Jacob Skaria" wrote: To return all rows with "text" in colA with all columns.. Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" Apply this formula in Sheet2 cell A1 and copy/drag down and to the right as required. =IF(COUNTIF(Sheet1!$A$1:$A$1000,"test")<ROW(A1),"" , INDEX(Sheet1!A$1:A$1000,SMALL(IF(Sheet1!$A$1:$A$10 00="test", ROW($A$1:$A$1000)),ROW(A1)))) -- Jacob (MVP - Excel) "DevourU" wrote: I would like to have sheet1 list all rows in sheet2 matching "TEXT" in column sheet2,A. Big TIA! -JS |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| copy data from sheet2 to sheet1 when sheet2 has variable # of rows | Excel Discussion (Misc queries) | |||
| +/- in sheet2 based on conditions and dynamic rows in sheet1 | Excel Discussion (Misc queries) | |||
| DataVaildationList works in Sheet1, but does not in Sheet2; Why | Excel Discussion (Misc queries) | |||
| Display Rows From Sheet1 In Sheet2 (Import) | Excel Worksheet Functions | |||
| How do I list all sums from sheet1 to sheet2? | Excel Worksheet Functions | |||
Hybrid Mode
