Linking items GREATER THAN O on another worksheet in the same Work

I have a workbook that has about four worksheets …

I want to create a list of items that will automatically be included on 
worksheet tab 4 from Worksheet tab 1 if the quantity for the item listed on 
the first worksheet has been changed from 0 to any other number (i.e. 1 or 10 
etc).

I have a list of equipment with pricing on worksheet tab 1 and the list is 
long. Many of the items will have quantities of zero and will not increase as 
they will not be selected for the project being created. We have additional 
columns for cost, extended cost, labor hours etc. If the quantity changes for 
a part on any particular row, the extended cost changes from 0 to x and the 
labor hours change from 0 to x.  Once the project is approved, there is an 
individual who must order all equipment that has a quantity greater than 0. 
It would be ideal for that list to automatically be created on the worksheet 
tab #4 so that this individual does not have to sort through the entire list 
of equipment that could have been selected for the project (but was not). How 
does one go about automatically creating this new worksheet (tab or sheet #4).

Thanks!!!

Eddie


0
Shapiro (3)
12/1/2004 4:33:07 AM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
362 Views

Similar Articles

[PageSpeed] 48

Hi!

Assume the items on Sheet 1 are in column A starting in A2=20
and the quantity is in the corresponding cell in column B.

For this example the total range is A2:B21.

In Sheet 4 in whatever cell, enter this array formula with=20
the key combo of CTRL,SHIFT,ENTER and copy down until you=20
get #NUM! errors which means there is no more data that=20
meets the condition:

=3DINDEX(Sheet1!$A$2:$A$21,SMALL(IF(Sheet1!$B$2:$B$21>0,ROW
($A$1:$A$20)),ROW(1:1)))

Biff

>-----Original Message-----
>I have a workbook that has about four worksheets =E2?=A6
>
>I want to create a list of items that will automatically=20
be included on=20
>worksheet tab 4 from Worksheet tab 1 if the quantity for=20
the item listed on=20
>the first worksheet has been changed from 0 to any other=20
number (i.e. 1 or 10=20
>etc).
>
>I have a list of equipment with pricing on worksheet tab=20
1 and the list is=20
>long. Many of the items will have quantities of zero and=20
will not increase as=20
>they will not be selected for the project being created.=20
We have additional=20
>columns for cost, extended cost, labor hours etc. If the=20
quantity changes for=20
>a part on any particular row, the extended cost changes=20
from 0 to x and the=20
>labor hours change from 0 to x.  Once the project is=20
approved, there is an=20
>individual who must order all equipment that has a=20
quantity greater than 0.=20
>It would be ideal for that list to automatically be=20
created on the worksheet=20
>tab #4 so that this individual does not have to sort=20
through the entire list=20
>of equipment that could have been selected for the=20
project (but was not). How=20
>does one go about automatically creating this new=20
worksheet (tab or sheet #4).
>
>Thanks!!!
>
>Eddie
>
>
>.
>
0
biffinpitt (3172)
12/1/2004 7:29:50 AM
One way ..

In Sheet1
------------
Assume the sample table below
is in cols A to D, data in row2 down
with the quantity col in col C (Qty)

Part# Price Qty Cost
Item1 $100.00 1 $100.00
Item2 $200.00 0 $0.00
Item3 $300.00 2 $600.00
Item4 $200.00 0 $0.00
Item5 $100.00 3 $300.00
etc

In an empty col to the right, say col X

Put in X2: =IF(OR(C2="",C2=0),"",ROW())

Copy down as many rows as data is expected
in the table, say down to C2000?
(can copy down ahead of expected data input)

This will set it up nicely for extract in Sheet4

In Sheet4
-------------
Paste the same headers into A1:D1, viz.:
Part# Price Qty Cost

Put in A2:

=IF(ISERROR(MATCH(SMALL(Sheet1!$X:$X,ROW(A1)),Sheet1!$X:$X,0)),"",OFFSET(She
et1!$A$1,MATCH(SMALL(Sheet1!$X:$X,ROW(A1)),Sheet1!$X:$X,0)-1,COLUMN(A1)-1))

Copy across to D2
(or across as many cols as there is in the table in Sheet1)
then fill down by as many rows as was done in col X
in Sheet1, viz. fill down to say D2000

Copy > Format the table as in Sheet1

Cols A to D will auto-extract only those rows
with Qty > 0 from Sheet1,
viz., for the sample data in Sheet1, you'll get:

Part# Price Qty Cost
Item1 $100.00 1 $100.00
Item3 $300.00 2 $600.00
Item5 $100.00 3 $300.00

--
Rgds
Max
xl 97
---
GMT+8,  1� 22' N  103� 45' E
xdemechanik <at>yahoo<dot>com
----
"Eddie Shapiro" <Eddie Shapiro@discussions.microsoft.com> wrote in message
news:F9DD13B7-CE6C-4318-939D-01BD32CB3B58@microsoft.com...
> I have a workbook that has about four worksheets .
>
> I want to create a list of items that will automatically be included on
> worksheet tab 4 from Worksheet tab 1 if the quantity for the item listed
on
> the first worksheet has been changed from 0 to any other number (i.e. 1 or
10
> etc).
>
> I have a list of equipment with pricing on worksheet tab 1 and the list is
> long. Many of the items will have quantities of zero and will not increase
as
> they will not be selected for the project being created. We have
additional
> columns for cost, extended cost, labor hours etc. If the quantity changes
for
> a part on any particular row, the extended cost changes from 0 to x and
the
> labor hours change from 0 to x.  Once the project is approved, there is an
> individual who must order all equipment that has a quantity greater than
0.
> It would be ideal for that list to automatically be created on the
worksheet
> tab #4 so that this individual does not have to sort through the entire
list
> of equipment that could have been selected for the project (but was not).
How
> does one go about automatically creating this new worksheet (tab or sheet
#4).
>
> Thanks!!!
>
> Eddie
>
>


0
demechanik (4694)
12/1/2004 7:46:13 AM
Oops, typo correction:

> Put in X2: =IF(OR(C2="",C2=0),"",ROW())
> Copy down as many rows as data is expected
> in the table, say down to C2000?

Last line in above should read as
> in the table, say down to X2000?
-- 
Rgds
Max
xl 97
--
GMT+8, 1� 22' N 103� 45' E
xdemechanik <at>yahoo<dot>com
---


0
demechanik (4694)
12/1/2004 2:16:54 PM
... and a note from the OP ..
--
Date: Wed, 01 Dec 2004 08:51:50 -0500
Subject: Linking Cells in Excel
From: "Eddie Shapiro"
To: demechanik@yahoo.com

Hi Max .... Thanks for the great help with my excel question !!!!!

Eddie Shapiro
-- 
Rgds
Max
xl 97
--
GMT+8, 1� 22' N 103� 45' E
xdemechanik <at>yahoo<dot>com
---


0
demechanik (4694)
12/1/2004 2:55:51 PM
Reply:

Similar Artilces:

Receipt not in Item Transaction Inquiry
I have a client that uses project accounting. They have received against a purchase order. The receipt shows up in the Purchase Receipts Inquiry, but does not show up in the Item Transaction Inquiry. What could be causing this? ...

Linking a Chart with graphics to ppt
Dear group, when I create a chart with scatters, which carry graphics like Excel-Block-Arrows and link this to Powerpoint, the graphics are shown there in a very low quality with large pixels. Any ideas how to improve this? Thanks in advance, Holger. ...

Can E-mail recipient policy be linked to the OU membership?
Running Exchange 2003 (mixed mode) and Windows 2003 server (Windows 2000 mixed mode): I'd like to create a e-mail recipient policy that is linked to the different OU's that are created on the system. Is it possible to link the policy to a specific OU or do I need to create groups on the AD to control which recipients a policy applies to? Martin Moustgaard Yes, you can create a recipient policy that looks to just a specific OU. It is done through LDAP queries. Here are some references to articles and whitepapers that discuss how to work with recipient policies. 249299.KB.EN-US HO...

Macro for Sharepoint List and another Table?? Same # records?
I am creating a database which we have a form made up of prepopulated data (from a sharepoint list) and also data input needed by a user (for example, comments, etc.). I have two tables: one sharepoint list that is linked to sharepoint website and another table that houses the additional user inputs [table 2]. I have linked the files based on the primary key. So, every record in my table 2 has a plus sign and it will expand and you will see the linked data from the sharepoint list. Is there a way that I can automatically create a record for every corresponding record ...

Public Folder link to Excel File
Hi - I have a user that claims that he had a shortcut to a spreadsheet "in his public folders" at his old job. Obviously, he's an end user that may or may not know what he's talking about.... I'm trying to figure out the best way to accomodate him. He basically wants an Excel file stored on a shared folder to be available to all user as either a shortcut, or a link in the public folders (can't post to a folder as he will be updating the sheet regularly). When a user clicks on the shortcut/link, it would just launch Excel and display the file. Anybody have any ideas...

changing values of one field based on another
How can I best change the values of one field in a table based on values of another field of the same table. We have an existing table of thousands of entries and I would like to use the following logic to populate a new boolean field. If field1 = "Done" Then BooleanFieldCompleted = True I have some Excel VBA experience but limited Access. I dont want to do this manually! Any assistance appreciated. In general, you'd use an Update query. However, in this case I don't see why you'd need such a field. Why not just create a query with a computed field that returns True...

Corrupt "Deleted Items" folder
I am unable to empty the "Deleted Items" folder. The error that comes up tells me that the "Outlook.pst" file has errors in it and to use the "Repair Inbox Tool". I've tried using the repair function under the "help" menu...to no avail. I have also tried opening the "Outlook.pst" file in MS Word, but the file is 129 megabytes! It crashes MS Word when I try to open it. Any ideas? Look for scanpst.exe on your local drive and run it against your outlook.pst file >-----Original Message----- >I am unable to empty the "Del...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

Ctrl+D not working on outlook messages
Any idea why? alltimefav wrote: > Any idea why? And what are you expecting to happen? "alltimefav" wrote in message news:a95c52df-a387-4a8f-bf9d-269ff485451f@i29g2000prf.googlegroups.com... > Any idea why? So instead of hitting Ctrl+D to delete an item, what happens when the item is selected and you hit the Del key, or the "X" toolbar button, or right-click on the item and select Delete? Did you really expect a detailed response for such a vague question? You didn't even bother to say what "not working" means, like the item does not get delete...

Need HELP! for Linking data
Could someone please direct me to where I can learn how to link date in a work book. i.e., I have individual pages for each subject but I need the data that is entered in these individual pages to transfer to the Master page without having to manually in put it.........TNX Bubey, There are not too many bits about linking worksheets or workbooks that I can find. But have a look at the links below, in case they give you the information you need. I think it is frustratingly one of those things which is very easy when you know how, or if you can get someone to actually show you, but if you hav...

how to make macro work even a sheet is hidden.
hello there I have macro assigned to a button in Sheet 1 which goes to sheet 2. Press a button to Refresh and then PRINT preview It does work in normal state. I do not want the user to see the Sheet 2. I hid sheets from Format-sheet-hide. but the macro is not working when sheet 2 is hidden. how to overcome this error "can't execute code in break mode" the macro code is Sub Print_Preview() Sheets("PaySlips2009-10").Select Application.Run "'Latest 2009Payslip.xls'!Sheet2.HURows" ActiveWindow.SelectedSheets.PrintPreview ...

Some Excel formatting functions taking a long time to work #2
Excel 2000 SP3 When I use some formatting functions for the first time in a session, such as bold, increasing font size etc, it takes up to 30 seconds to work. Meantime Excel is locked up until it completes that formatting call. I suspect faulty DLL? Has anyone experienced this? How to fix (other than a complete re- install) ? Any advice appreciated. Piri On Nov 5, 8:39=A0am, Piri <wiremu.pare...@hotmail.com> wrote: > Excel 2000 SP3 > When I use some formatting functions for the first time in a > session, such as bold, increasing font size etc, it takes =A0up to 30 > secon...

Adding a combo box to a worksheet
Hi all, I'm re-creating one of our paper forms in Excel and I'd like to add combo boxes to some blanks on the form to allow the user to choose a name from a list. I know a little about Excel formulas and no VB code at all...what's the idiot-proof way to do this? Thanks, Chris Hi Chris, The easiest way is to right-click within Excel in the toolbars area and select the "Forms" toolbar. Then Forms toolbar should then appear and could can select the "Combo Box" icon and click on that. If you can't tell which icon represents the Combo Box, just hover yo...

Report to show Item Class Distribution Amounts
We would like to create a report, using Crystal Reports, that would show the following: dollar amount break down of the Sales Distribution accounts (COGS and Sales) per item class based on a date range. What is the most accurate way of going about this? We could only think of this method: (in short) sum the Ext Price based on SOP30300.CSLSINDX and SLSINDX and hope it matches the SOP10102 summed distribution amounts. Any advice would be appreciated. Thanks in advance. With the SLSINDX you would use the Extended Price and the CSLSINDX you would use Extended Cost. You would probably ...

VBA to put a copy of worksheet on the desktop 05-13-10
Hi all, In my workbook XYZ I have a sheet ABC. With a button on sheet DEF I can refresh sheet ABC. When the code finishes it job I want to add the actual date (short European notation dmyy) and time (f.i. 241110 16.31) to the name of the sheet (which becomes ABC 241110 16.31) and after that make a copy of that sheet in a separate workbook and put that workbook as an icon on the desktop of my computer. Is this possible? If so, please help me with the necessary code. Thanks in advance for your assistance. Jack Sons The Netherlands ...

Workplace Queues
We just rolled out CRM a few weeks ago. I'm getting a lot of complaints from the users about the thousands of items showing up in their My Work\Queues\In Progress folder. When I look at my own items, I have about 1000 activities showing in my In Progress folder but when I open them up most of them are owned by someone else. According to the Help description of this folder, only items that I have accepted should show up in my In Progress folder. I've never accepted anything, so I'm not sure anything whatsoever would be showing up in this folder. We used Scribe to import ...

With and import tool can you change only item description?
Is there a way to change only the item description on a large quanity of items. What about the extended description? Thanks for your help. Use the MS SQL Data Import Tool by EMS. $65.00. The QSImport Tool available to download from Microsoft will probably work but is not supported by Microsoft. Kinnard L. Kohler Business Machines Systems 6101 South Shackleford Road Little Rock, AR 72204-8606 (T) 501-375-8380 (F) 501-375-0043 (Cell) 501-412-5686 Email: kinnard@removebmsar.com "Lisa" wrote: > Is there a way to change only the item description on a large quanity of >...

why does my spell check not work?
My spell check has never worked. What can I do to set it up? In what program / version "karen" <karen@discussions.microsoft.com> wrote in message news:DA8883BD-0958-4AB0-93FF-F43E4FB9D176@microsoft.com... > My spell check has never worked. What can I do to set it up? ...

Right clicking on a CListCtrl item
I have a TreeCtl object in a dialog box. I created an OnNMRclick.. override function to capture a right clicks. The problem I can't figure out is how to find the tree item that the user has right clicked on. Here's what I tried: void CRestoreFiles::OnNMRclickXYZ(NMHDR *pNMHDR, LRESULT *pResult) POINT CurPos; TVHITTESTINFO lpht; HTREEITEM RightClickItem; GetCursorPos(&CurPos); lpht.pt = CurPos; RightClickItem = TreeView_HitTest(pNMHDR->hwndFrom,&lpht); ... I figured that GetCursorPos would give me the position of the cursor where I had right clicke...

Date function quit working
Hi, I have an Access 2002 application that I have been running on Windows XP SP2 without issue. I just installed the application (running in Access Runtime) on a Windows Vista Home Premium machine. Now, anywhere I used the =Date() function, it fails and just shows #Name? I also have a subform on one of my forms that has now gone blank. It also uses the date function. I had this problem when I converted to Windows XP several years ago and updating the OWC10.dll to version 6619 fixed both issues. However, everything I have read says that reference file makes no difference to the Access...

Word 2003: Will it work on Windows 7 Pro
The ads say there is virtual XP on Windows 7 that can run programs like Word 2003. In your experience is this true? On any Windows 7 machine? Does 64 versus 32 bit processor matter for either Word/Office 2003 or Office 2007? thanks. You don't need Virtual XP to run Office 2003 on Windows 7. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "removing all headers and footers" <removingallheadersandfooters@discussions.microsoft.com> wrote in message news:95298031-44F6-4E74-A608-C76987DFAB46@microsoft....

item class table
I am creating SOP IM import. I need to fill the distribution fields with a rev account that is part of the item class. I would like to find a table that would hold the item class accounts. I looked in IV40400 and did not see any distribution accounts. What is the best table to pull these accounts. If the accounts have been defined on the Item Class, they will appear on the records in the IV40400 table. They're in the fields IVIVINDX, IVIVOFIX, etc - and they're just the keys to the actual account definitions in the GL00100 table. If a particular account type isn't defined ...

Selecting the Right Text Alignment for a edit box doesn't work
When I select right text alignment in the edit control properties, the text is still left aligned when I run the program. What am I doing wrong Thanks Dan Dan, "Dan" <anonymous@discussions.microsoft.com> a �crit dans le message de news:DECFE605-A130-416B-9924-60BA0C79D684@microsoft.com... > When I select right text alignment in the edit control properties, the text is still left aligned when I run the program. What am I doing wrong? > I've no idea :-))) You can open your RC-file as text, and make sure it has the ES_RIGHT style set, thus: EDITTEXT IDC...

Sorting by file extension in a worksheet
Hello, I have an excel worksheet which has a list of file names from a directory and various stats about each file. I need to be able to sort them by the file extension. Eg. c:\documents\folderA\picture1.eps c:\documents\folderB\document.doc I would like to sort them by the ".eps" extension. I have tried using the Data,Sort menu and something like *?*.eps as the criteria. But am obvisouly missing something. Any help would be most appreciated. Thanks Karl You could use a helper column to extract just the extension then include this in your sort range and sort on the helper col...

Accessing another user's calendar OL2000/OL2003/Ex2000
Here's the situation: Running Exchange2000, v 6.0, sp4. Users are on XP/OL2003 or NT/OL2000. (we're in the process of upgrading all of the workstations to XP.) VIP user is on XP/OL2003. He can access his calendar just fine on his workstation. His secretary and other office admin type people are still on NT/OL2000. They have reviewer or editor rights to his calendar. They try to access his calendar by chosing File/Open/Other User's Folder... and put in his name and calendar. When they do this, the process hangs. In Task Manager, Outlook is "Running." End the Outloo...