Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Moving Information
I have a "shopping list" which a user fills out. I need that information to move to a seperate sheet only if the quantity is changed from a 0 (zero) to a 1 or higher. For example: Item Qty Total Chair 0 0 Table 1 50 I need the information with regards to the table to move to a seperate sheet. (It needs to include the name(table) quantity (1) and Total (50)) As the chair quantity remains as 0 I do not want that information on the seperate sheet. Any assistance will be appreciated! -- Anupkumar ------------------------------------------------------------------------ Anupkumar's Profile: http://www.excelforum.com/member.php...o&userid=35152 View this thread: http://www.excelforum.com/showthread...hreadid=549173 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Moving Information
One play which might suffice ..
Assume source table below is in sheet: X, cols A to C, data from row2 to a max expected row100 (say) Item Qty Total Chair 0 0 Table 1 50 In another sheet: Y (say) with the same col headers in A1:C1, viz.: Item, Qty, Total Put in A2: =IF(ISERROR(SMALL($D:$D,ROW(A1))),"",INDEX(X!A:A,M ATCH(SMALL($D:$D,ROW(A1)),$D:$D,0))) Copy A2 to C2 Put in D2: =IF(X!B20,ROW(),"") (Leave D1 empty) Then select A2:D2, fill down to D100 (cover the max expected extent of data in X) Cols A to C will return the required results from X, ie only the lines with Qty 0, with all results neatly bunched at the top (Hide away the criteria col D, if desired) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Anupkumar" wrote: I have a "shopping list" which a user fills out. I need that information to move to a seperate sheet only if the quantity is changed from a 0 (zero) to a 1 or higher. For example: Item Qty Total Chair 0 0 Table 1 50 I need the information with regards to the table to move to a seperate sheet. (It needs to include the name(table) quantity (1) and Total (50)) As the chair quantity remains as 0 I do not want that information on the seperate sheet. Any assistance will be appreciated! -- Anupkumar ------------------------------------------------------------------------ Anupkumar's Profile: http://www.excelforum.com/member.php...o&userid=35152 View this thread: http://www.excelforum.com/showthread...hreadid=549173 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Moving Information
What would be the formula to rather than referencing a cell but make it go on the line below. For example: The list below is the items available to order: Chair 1 $100 Table 0 $0 PC 2 $500 Below is the final out come as it currently stands: Chair 1 $100 PC 2 $500 I do not want the empty line between the two items. I need the PC to go directly under the Chairs. Thanks once again! -- Anupkumar ------------------------------------------------------------------------ Anupkumar's Profile: http://www.excelforum.com/member.php...o&userid=35152 View this thread: http://www.excelforum.com/showthread...hreadid=549173 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Moving Information
"Anupkumar" wrote:
.. I do not want the empty line between the two items. The above won't happen if the suggested construct was implemented correctly. Try it again .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Moving Information
"Max" wrote: "Anupkumar" wrote: .. I do not want the empty line between the two items. The above won't happen if the suggested construct was implemented correctly. Try it again .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- Yes this is true. It does not happen when I input the formula above. Although it still includes the items with the 0 quantity, along with placing it in order. I unfortunatly do not want it in chronological order. The items are listed in a specific order on the "shopping list" and need to maintain that order, if that makes sense. I am just trying to change the above formula to not place the items in order, along with making it not pick up the items with 0 quantity. Thank you for all of your help!!! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Moving Information
"Andy" wrote:
Yes this is true. It does not happen when I input the formula above. Although it still includes the items with the 0 quantity, along with placing it in order. I unfortunately do not want it in chronological order. The items are listed in a specific order on the "shopping list" and need to maintain that order, if that makes sense. I am just trying to change the above formula to not place the items in order, along with making it not pick up the items with 0 quantity. Not sure what's happening over there <g, but here's a sample construct to illustrate: http://www.savefile.com/files/6089699 Extract Lines with Qty more than zero in other sht Although it still includes the items with the 0 quantity, along with placing it in order. I unfortunately do not want it in chronological order... The results extracted in Y do not include items with zero quantity (this is the criteria !), and extracted items are in the same relative order that they appear originally in X (there is no chronological ordering) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Moving Information
Firstly I would like to thank you Max for your assistance! We are now trying
to take this one step further. I have numerous departments which will be completing the workbook, also the workbook has numerous worksheets such as Furniture, Equipment, Computers etc. Once they have completed all the different worksheets and the information is processed using the formula provided above, and I have all the items ordered on a seperate worksheet, I need to group the information. An example would be: Administration completing the "shopping list" and Marketing completing their "shopping list". I need to place all the Furniture ordered by the two departments on one Final worksheet and all the Equipment ordered on to one worksheet. (I hope you guys get the idea) Basically I have tried copying and pasting the information but it does not work. I have tried to tweak the formula by changing the A1 to A80 and its not working because the cells are not matching up I guess. (A80 is where the information for the second department would start.) Thank you guys once again! "Max" wrote: "Andy" wrote: Yes this is true. It does not happen when I input the formula above. Although it still includes the items with the 0 quantity, along with placing it in order. I unfortunately do not want it in chronological order. The items are listed in a specific order on the "shopping list" and need to maintain that order, if that makes sense. I am just trying to change the above formula to not place the items in order, along with making it not pick up the items with 0 quantity. Not sure what's happening over there <g, but here's a sample construct to illustrate: http://www.savefile.com/files/6089699 Extract Lines with Qty more than zero in other sht Although it still includes the items with the 0 quantity, along with placing it in order. I unfortunately do not want it in chronological order... The results extracted in Y do not include items with zero quantity (this is the criteria !), and extracted items are in the same relative order that they appear originally in X (there is no chronological ordering) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Moving Information
"Andy" wrote:
Firstly I would like to thank you Max for your assistance! Welcome, Andy ! We are now trying to take this one step further.... You could hang around awhile for responses from others. But I would suggest you put your new query in as a new post, for better radar/exposure to all the responders out there. This thread is quite dated & deep .. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving information to a different page | Excel Worksheet Functions | |||
Importing information from 2 workbooks into a 3rd one | Excel Worksheet Functions | |||
Importing information from 2 workbooks into a 3rd one | Excel Discussion (Misc queries) | |||
Switching Information | Excel Discussion (Misc queries) | |||
Moving a line of data when information is entered in a specific cell. | Excel Discussion (Misc queries) |