Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.newusers
|
|||
|
|||
![]()
I have made a worksheet containing a a large number of products (product
list) and want to automatically populate another worksheet (order sheet)when the quantity column has a number. for example: sheet 1 a:1 a:2 a:3 xyz 2 $9.95 so that when a:2 (the quantity column) =1, the cooresponding info goes to the order sheet. Any ideas would be appreciated -- regards, Tom |
#2
![]()
Posted to microsoft.public.excel.newusers
|
|||
|
|||
![]()
One way which gets you there ..
Assuming source data in Sheet1 cols A to C (your product list), data from row2 down where the key col is col B = quantity In Sheet2 (your order sheet), Put in A2: =IF(Sheet1!B2="","",IF(Sheet1!B2=1,ROW(),"")) (Leave A1 blank) Put in B2: =IF(ROW(A1)COUNT($A:$A),"",INDEX(Sheet1!A:A,SMALL ($A:$A,ROW(A1)))) Copy B2 to D2. Select A2:D2, copy down to cover the max expected extent of data in Sheet1. Format col C as currency. Hide away col A. You'll get the required results returned in cols B to D, ie only the lines from Sheet1 where the quantity is =1, with all result lines all neatly bunched at the top. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "tom" wrote: I have made a worksheet containing a a large number of products (product list) and want to automatically populate another worksheet (order sheet)when the quantity column has a number. for example: sheet 1 a:1 a:2 a:3 xyz 2 $9.95 so that when a:2 (the quantity column) =1, the cooresponding info goes to the order sheet. Any ideas would be appreciated -- regards, Tom |
#3
![]()
Posted to microsoft.public.excel.newusers
|
|||
|
|||
![]()
Thanks Max.
-- regards, Tom "tom" wrote: I have made a worksheet containing a a large number of products (product list) and want to automatically populate another worksheet (order sheet)when the quantity column has a number. for example: sheet 1 a:1 a:2 a:3 xyz 2 $9.95 so that when a:2 (the quantity column) =1, the cooresponding info goes to the order sheet. Any ideas would be appreciated -- regards, Tom |
#4
![]()
Posted to microsoft.public.excel.newusers
|
|||
|
|||
![]()
Welcome, Tom.
Thanks for posting back. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "tom" wrote in message ... Thanks Max. -- regards, Tom |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Auto copy down depending on criteria | Excel Worksheet Functions | |||
Can not copy a worksheet to another workbook | Excel Discussion (Misc queries) | |||
Copy from one worksheet to another | Excel Discussion (Misc queries) | |||
How to Copy a Password Protected Excel Worksheet | Excel Worksheet Functions | |||
how do I make a copy of a worksheet and retain formulas but not data | Setting up and Configuration of Excel |