Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show a row of data in a different sheet if cell isn't null
If a cell is not blank or null (meaning I have used some of those items), I
need the row of data to copy to the next worksheet. How can I do this? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show a row of data in a different sheet if cell isn't null
One quick way to set it up dynamically
Assume source data in Sheet1, cols A to C, where the key col = col A In another sheet, In A2: =IF(Sheet1!A2<"",ROW(),"") Leave A1 blank. This is the criteria col, monitoring your line: If a cell is not blank or null ... In B2: =IF(ROWS($1:1)COUNT($A:$A),"",INDEX(Sheet1!A:A,SM ALL($A:$A,ROWS($1:1)))) Copy B2 to D2. Select A2:D2, copy down to cover the max expected extent of source data in Sheet1, say down to D100. Minimize col A. Cols B to D will return the desired results from Sheet1, all neatly packed at the top. -- Max Singapore http://savefile.com/projects/236895 Downloads:21,000 Files:370 Subscribers:66 xdemechanik --- "OCDinNC" wrote: If a cell is not blank or null (meaning I have used some of those items), I need the row of data to copy to the next worksheet. How can I do this? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show a row of data in a different sheet if cell isn't null
Thanks so much for your reply, Max. I am pretty new to using Excel, and am
trying to fit your example to my situation. The range I need to filter is H, so cells H2 through H145 on sheet 2 need to be scanned for the presence of a number, and if there is one, then copy the rows containing numbers to sheet 1. Can you elaborate? Thanks! "Max" wrote: One quick way to set it up dynamically Assume source data in Sheet1, cols A to C, where the key col = col A In another sheet, In A2: =IF(Sheet1!A2<"",ROW(),"") Leave A1 blank. This is the criteria col, monitoring your line: If a cell is not blank or null ... In B2: =IF(ROWS($1:1)COUNT($A:$A),"",INDEX(Sheet1!A:A,SM ALL($A:$A,ROWS($1:1)))) Copy B2 to D2. Select A2:D2, copy down to cover the max expected extent of source data in Sheet1, say down to D100. Minimize col A. Cols B to D will return the desired results from Sheet1, all neatly packed at the top. -- Max Singapore http://savefile.com/projects/236895 Downloads:21,000 Files:370 Subscribers:66 xdemechanik --- "OCDinNC" wrote: If a cell is not blank or null (meaning I have used some of those items), I need the row of data to copy to the next worksheet. How can I do this? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show a row of data in a different sheet if cell isn't null
Data in Sheet1 is assumed in row 2 down
-- Max Singapore http://savefile.com/projects/236895 Downloads:21,000 Files:370 Subscribers:66 xdemechanik --- |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show a row of data in a different sheet if cell isn't null
So your source data is in Sheet2, cols A to col H (say)
with key col = col H, data from row 2 down Assume you want to scan col H for the number: 77 (say) - the criteria In Sheet1, In A2: =IF(Sheet2!H2=77,ROW(),"") Leave A1 blank In B2: =IF(ROWS($1:1)COUNT($A:$A),"",INDEX(Sheet2!A:A,SM ALL($A:$A,ROWS($1:1)))) Copy B2 to I2. Select A2:I2, copy down to cover the max expected extent of source data in Sheet2, say down to I200? Minimize col A. Cols B to I will return the desired results from Sheet2, all neatly packed at the top. -- Max Singapore http://savefile.com/projects/236895 Downloads:21,000 Files:370 Subscribers:66 xdemechanik --- "OCDinNC" wrote: Thanks so much for your reply, Max. I am pretty new to using Excel, and am trying to fit your example to my situation. The range I need to filter is H, so cells H2 through H145 on sheet 2 need to be scanned for the presence of a number, and if there is one, then copy the rows containing numbers to sheet 1. Can you elaborate? Thanks! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show a row of data in a different sheet if cell isn't null
Thanks, I'll try that.
"Max" wrote: Data in Sheet1 is assumed in row 2 down -- Max Singapore http://savefile.com/projects/236895 Downloads:21,000 Files:370 Subscribers:66 xdemechanik --- |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Show a row of data in a different sheet if cell isn't null
I've responded further with the amended steps to suit your specific set-ups
in the other branch. You might wish to try that instead. It should work fine. Pl press the YES buttons below from where you're reading this. -- Max Singapore http://savefile.com/projects/236895 Downloads:21,000 Files:370 Subscribers:66 xdemechanik --- "OCDinNC" wrote: Thanks, I'll try that. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If Date Null Show Nothing | Excel Worksheet Functions | |||
Show Null As %0 | Excel Worksheet Functions | |||
How to show data from other sheet depend on value?? | Excel Worksheet Functions | |||
cell value based on null/not null in another cell | Excel Worksheet Functions | |||
Referencing cell in another sheet yields null? | Excel Worksheet Functions |