Use Data from one sheet to populate Purchase Order on another sheet -Excel 2002

Hello,

Wonder if this is possible..

Sheet1 - has a 'list' of parts that need to be ordered - their description,
qty, type of material, part number etc

Sheet2 - I want to create a requisition from those items listed on Sheet1
based on type of material...

Basically need to take the data from Sheet 1 (can be 50 - 200 items/parts)
keyed in by designers and create a purchase order requisition for our
vendors/suppliers based on the type of material. Is there a way to extract..
say. material is H20.. and of them 50 items keyed in.. 10 of them are H20..
so that I can fill in the qty, description, part number and type of material
automatically on the requisition..

I know how to use vlookup to extract information to automatically fill in
the address now from a list on another sheet.. and filter directly on Sheet
1 which gives me the kind of information I need.. but I don't want to have
the employee just paste.. if possible... in case they miss something...
actually its my supervisors request to some how have a dialog box or
something asking for type of material.. so that it can be filled in.. or the
filter result from sheet 1 connected to this sheet.

I know a database would be easier.. but we are not yet at that point.. this
is an interim solution...

Thanks in advance for your assistance..


0
4/28/2004 9:46:43 PM
excel 39879 articles. 2 followers. Follow

2 Replies
817 Views

Similar Articles

[PageSpeed] 18

You could use data validation and VLookup formulas to create a purchase 
order requisition form. There's an example on my web site:

    http://www.contextures.com/excelfiles.html

Under 'Data Validation', look for 'Order Form'


Cheryl wrote:
> Hello,
> 
> Wonder if this is possible..
> 
> Sheet1 - has a 'list' of parts that need to be ordered - their description,
> qty, type of material, part number etc
> 
> Sheet2 - I want to create a requisition from those items listed on Sheet1
> based on type of material...
> 
> Basically need to take the data from Sheet 1 (can be 50 - 200 items/parts)
> keyed in by designers and create a purchase order requisition for our
> vendors/suppliers based on the type of material. Is there a way to extract..
> say. material is H20.. and of them 50 items keyed in.. 10 of them are H20..
> so that I can fill in the qty, description, part number and type of material
> automatically on the requisition..
> 
> I know how to use vlookup to extract information to automatically fill in
> the address now from a list on another sheet.. and filter directly on Sheet
> 1 which gives me the kind of information I need.. but I don't want to have
> the employee just paste.. if possible... in case they miss something...
> actually its my supervisors request to some how have a dialog box or
> something asking for type of material.. so that it can be filled in.. or the
> filter result from sheet 1 connected to this sheet.
> 
> I know a database would be easier.. but we are not yet at that point.. this
> is an interim solution...
> 
> Thanks in advance for your assistance..
> 
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd1 (5911)
5/1/2004 2:07:48 AM
Thank you very much for the information.. It's great.. !


"Debra Dalgleish" <dsd@contexturesXSPAM.com> wrote in message
news:40930674.6060401@contexturesXSPAM.com...
> You could use data validation and VLookup formulas to create a purchase
> order requisition form. There's an example on my web site:
>
>     http://www.contextures.com/excelfiles.html
>
> Under 'Data Validation', look for 'Order Form'
>
>
> Cheryl wrote:
> > Hello,
> >
> > Wonder if this is possible..
> >
> > Sheet1 - has a 'list' of parts that need to be ordered - their
description,
> > qty, type of material, part number etc
> >
> > Sheet2 - I want to create a requisition from those items listed on
Sheet1
> > based on type of material...
> >
> > Basically need to take the data from Sheet 1 (can be 50 - 200
items/parts)
> > keyed in by designers and create a purchase order requisition for our
> > vendors/suppliers based on the type of material. Is there a way to
extract..
> > say. material is H20.. and of them 50 items keyed in.. 10 of them are
H20..
> > so that I can fill in the qty, description, part number and type of
material
> > automatically on the requisition..
> >
> > I know how to use vlookup to extract information to automatically fill
in
> > the address now from a list on another sheet.. and filter directly on
Sheet
> > 1 which gives me the kind of information I need.. but I don't want to
have
> > the employee just paste.. if possible... in case they miss something...
> > actually its my supervisors request to some how have a dialog box or
> > something asking for type of material.. so that it can be filled in.. or
the
> > filter result from sheet 1 connected to this sheet.
> >
> > I know a database would be easier.. but we are not yet at that point..
this
> > is an interim solution...
> >
> > Thanks in advance for your assistance..
> >
> >
>
>
> -- 
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>


0
5/4/2004 2:31:17 PM
Reply:

Similar Artilces:

Outlook 2002 (office xp developer suite)
Does anyone know of a place where I can find a bunch of various macros for outlook? I've looked around and found 1 or 2 but I would think someone has a large amount of them.. Thanks Dave dave wrote: > Does anyone know of a place where I can find a bunch of > various macros for outlook? I've looked around and found > 1 or 2 but I would think someone has a large amount of > them.. > > Thanks > > Dave Dunno, but a Google search on "Outlook macros" gets about 55,000 hits.... G'Day Dave, Try: http://www.outlookvba.com/examples//allexamples...

OL 2002 Hanging up When Attaching Docs
I have been having a problem recently that is probably an easy fix .... just need some guidance. Running OL 2002 on an XP PRO machine connected to a Windows 2000 Server and Exchange 2000. Everytime I go to attach a file to an email it takes forever to navigate through the trees, often resulting in the program freezing. Have tried Detect and Repair but no difference. Problem just started a few weeks ago but have not added anything to my system. Can anyone help? ...

Highlighted word wont DEL when I use Bakspc only in office/word
When I highlight a word in and hit the Backspace key it will not delete the word, this is only in outlook and word , however I can delete using the backspace key using one key stroke at a time. In Tools > Options > Edit, check "Typing replaces selection". (In Word 2007 this option is at Office button > Word Options > Advanced.) -- Regards, Jay Freedman Microsoft Word MVP FAQ: http://word.mvps.org Email cannot be acknowledged; please post all follow-ups to the newsgroup so all may benefit. backspace key not working correctly wrote: > When I ...

Formula without using numbers after decimal in the answer
I have a formula that derives the answer from a figure with a decimal. I don't want to use the figures after the decimal. Is there a way to just use the whole number and omit the numbers after the decimal without having to manually key in all these numbers manually? Thanks, Mustang You can use the INT function. This 'rounds down' any number to th nearest integer, e.g. if A1=2.567, a formula in B2 of =INT(A1) return 2 HTH Bruc -- swatsp0 ----------------------------------------------------------------------- swatsp0p's Profile: http://www.excelforum.com/member.php?...

Using Relative path for XML data file?
Is there a way to specify a relative path to an XML data file imported into Excel 2003? I am writing a web app that generates report data as XML for the user to download to their local machine. This data is to be consumed by an Excel reporting spreadsheet, which contains display-formatted tables and charts that are mapped to various data fields in an XML Map, which is in turn linked to the xml data file they will download. The idea is the user only needs to download the data for updates, not the whole spreadsheet. However, since I cannot predict the path where the user will store their...

Unable to open .docx documents in Word 2002
I cannot open word documents sent with .docx extendion in word 2002. I checked my language setting and it is English. What is wrong? Download the Compatibility Pack. http://www.microsoft.com/downloads/details.aspx?familyid=941b3470-3ae9-4aee-8f43-c6bb74cd1466&displaylang=en -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dummies" "Johnpm" <Johnpm@discussions.microsoft.com> wrote in message news:43E248E8-5D83-4E91-9743-CE12CCD6A443@microsoft.com... >I cannot open word documents sent with .docx extendi...

Show date, time & day of week in one cell
Can I show the date, time and day of week in one cell. I have: 09/03/07 8:30 AM in one cell using the format (Format/Cells/Custom): [$-409]mm/dd/yy h:mm AM/PM;@ Excel refuses to accept ddd for Mon or dddd Monday at the end of the format I want it to read: 09/03/07 8:30 AM Monday in 1 cell. I have Excel 2003. One way: mm/dd/yy h:mm AM/PM dddd;@ In article <Xns99B8A3CAF9130pencilunlistedcom@208.49.82.220>, Burp <burp@beep.comINVALID> wrote: > Can I show the date, time and day of week in one cell. > > I have: > 09/03/07 8:30 AM > in one cell using the for...

Pulling data from separate tabs
When charting in Excel 2002 is there a way to use sets of data from two different tabs within the same worksheet? For example, a spreadsheet contains separate tabs for prior year and current year data. Is there a way to reference the data or label series to pick up data from both? I tried pointing and clicking, and then typing the following as a reference for the axis labels: ='Prior Year'!$B$110:$M$110,'Current Year'!'$B$110:$M$110 but receive an error that I'm referring to an external worksheet. I've used the comma (') in the past to reference breaks ...

Changing Cells and entering data in them
Thanks for the help again. Big thanks to Steve you've got me this far. I went out and bought a book, but it's like reading a foreign language. I was informed today that I can't have message boxes come up. I need to have the code point at the cells and if they are blank turn which ever one is blank red or if both are then both turn red then pause for each cell to be filled in. Cell F14 "Last Name" then automatically go to Cell F16 "First Name" on tab or enter. Basically if Cell F22 or F23 has an X in it, Cells F14 an F16 turn red and cell F14 has the focus...

print multiple pages on one sheet of paper
I am using mailmerge in Publisher to create placecards for a party we are hosting. The final size of the placecards is 1.5" by 1.5" and we have to print 100 final cards. Publisher gives me the option of printing multiple copies of the same page on one sheet of letter sized paper or one page on one sheet of letter sized paper. What I would like to do, however, is print multiple different pages on one sheet of paper. If I cannot find a solution for this, I will need to print 100 separate pages with a 1.5" square box of copy in the center of each sheet. In page setup, sel...

Disable Secure Sockets Layer on exchange server when using RPC over HTTP
Hi im trying to enable RPC over HTTP to enable users to establish contact to my Excahger server 2003 over the internet. Now, I dont want to use SSL (security not that important) and i am told by this article that i can disable SSL in windows registry. Quote: Note While RPC over HTTP does not require Secure Sockets Layer, you must modify the registry to enable RPC over HTTP if you do not want to use Secure Sockets Layer. Microsoft recommends that you enable and require Secure Sockets Layer for your RPC over HTTP communications. At this address: http://support.microsoft.com/?id=833401 But i ...

Disk drive order
Hello, How to make my disk driver appear as C: (Its a virtual boot drive) and enumerate the other disk accordingly? The physical disk is always enumerate first, and windows think he is C: Thanks Guilherme You are out of luck, physical devices are always enumerated first. This has been true since Windows 2000. -- Don Burn (MVP, Windows DKD) Windows Filesystem and Driver Consulting Website: http://www.windrvr.com Blog: http://msmvps.com/blogs/WinDrvr Remove StopSpam to reply "Guilherme Moro" <guilherme.moro@gmail.com> wrote in message news:7f0...

data sort
ok now should be simple >> I need to sort by month on data that is held in format >> day/month so eg 1511 1510 3011 3010 now custom/ends with/ 11... does not work custom/ends with/ ??11.. or *11 does not work either contains 11 does not work (& would also be wrong if data set contained 1011) but still I am stumped so any help would be great cheers Alex I would be inclined to add a new, temporary field of formulas that pull off the right 2 digits, and sort by that: =RIGHT(A1,2) -- Jim Rech Excel MVP ...

using the journal on outlook
Once I link an email to the journal, can I still find that email in my mail box? I seem to be able to get to it only via the journal. If this is the way it is supposed to be, how do I remove it from the journal and get it back into my mail box? Am I just missing something? -- thanks, Independent Are you linking to the item or putting a copy into the journal item? Also, has the item been archived or not? "Independent" <Independent@discussions.microsoft.com> wrote in message news:868279F2-53C8-403A-97F5-604CEECD873C@microsoft.com... > Once I link an email to the journ...

predict future data
Is there a way to create an XY line graph wih plotted data, yet leave room to predict future data on the axes? I can get the graph, but the x and y axes stop at the last data points, and I want those axes continued so that the existing data can be examined and future data predicted and plotted on the same graph, but I am not sure how to accomplish this. Any suggestions would be appreciated. Thanks. Jeff 1) Click on data series in chart, use Add trendline; in Option tab specify some units forward OR 2) Read Help about TREND and FORECAST, and SLOPE and INTERCEPT OR 3) Get crystal bal...

Let me use the Line Color icon on charts
It would speed up a lot of my work if I could use the Line Color icon on Excel charts, the same way I am able to use the Fill Color and Font Color icons. However, when I highlight any chart object, like the Plot Area, Chart Area, or a Series, the Line Color icon is disabled. -- Stuart Bratesman, Jr., MPP Muskie School of Public Service Univ. of Southern Maine Portland, Maine ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If ...

Default scene is solid blue but only on one account
I have 2 accounts with WLM, on one the default scene is as it should be, a mix of blue and white. But on the other account it is solid blue. If i change the scene in the program it changes to that but any contact windows i open continue to be solid blue by the persons name but above that is the new scene. I've done a repair job on 2 computers but does not fix the problem. Guesses? ...

Does Outlook use the DAV protocol?
I'm an Outlook Express user who wants to switch to Outlook. I received a notice from Microsoft that includes the following: "... as of June 30, 2008, Microsoft is disabling the DAV protocol and you will no longer be able to access your Hotmail Inbox via Outlook Express." Please tell me if this action by Microsoft will affect Outlook in the same manner, or am I free to make the switch. "BudV" <BudVitoff@(NO)att.(SPAM)net> wrote in message news:%230XUDi%23zIHA.2384@TK2MSFTNGP02.phx.gbl... > I'm an Outlook Express user who wants to switch to Outlook...

Help with importing data
Can I have users fill in a form in Access and have that data be transferred and updated to a spreadsheet. Need for fill out several fields and then export to a specific spreadsheet and place that data into the cells that will update that cell (add to the total in that cell) of a spreadsheet. ...

Formula for cross tab data filling
Hi All Excel 2003 How to using formula for data filling as below (Y/N) ? Sheet A Product A Product B Product C System A Y N Y System B Y Y N Sheet B System A Product A System A Product C System B Product A System B Product B moonhkt ...

Control End After the Sheet is Modified
Hi, I use control end a lot to go to the bottom right corner of my big sheets. If you delete rows & columns of data it no longer goes to the right hand of the data. Is there any easy way to navigate to the end of the current data? End and down arrow doesn't work if there are gaps in the data. Thanks for looking. Paul You should use <Home> <End> Kassie "Paul" wrote: > Hi, > I use control end a lot to go to the bottom right corner of my big sheets. > If you delete rows & columns of data it no longer goes to the right hand of > the data. Is...

2 different Qty in order details in northwind
Hi, im pretty new with access 2007, i decide to use northwind template. In order detail both form, i have 2 different Qty, the original Qty is the Qty that will link to Inventory and I add another field Qty1 for invoice purpose. For example, I got 1 roll of tape, 1 roll will be cut into 10 pcs. We need to bill according to customer request qty 10, but actual qty sold is only 1. Anyone come across this issue. How to modify it w/o effect the function? table/form as below:- ID ORDER ID PRODUCT ID QUANTITY (FOR INVENTORY) QTY1 (FOR INVOICE) UNIT PRICE AND SO ON Is there a...

Provide a blanket purchase order option for sales.
MANY OF OUR CUSTOMERS ISSUE BLANKET PURCHASE ORDER, THEN ASK US TO SHIP A PORTION OF THE ORDER AT A TIME. IE, A PURCHASE ORDER FOR 10,000 UNITS AND THEY WANT 1000 SHIPPED EACH MONTH. CURRENTLY WE CAN SHIP THE FIRST 1000 AND LATER SHIP A SECOND 1000 BUT THEN WE MUS CANCELL THE CUSTOMERS PURCHASE ORDER AND RE-ENTER THEIR ORDER FOR 8000, THEN WE CAN DO 2 SHIMENTS AGAIN AND THEN MUST ONCE MORE CANCELL THE BLANKET PURCHASE ORDER AND ENTER A NEW ONE FOR 6,000, ETC, ETC. I UNDERSTAND THAT PREVIOUS VERSIONS DID ALLOW REPEATED BACK ORDERS AND PARTIAL SHIPMENTS. PLEASE RE-DESIGN THIS BLANKET PUR...

Automatically copy input from one cell to another
After I enter a value in one cell, how can I have it automatically enter it into another cell, within the same worksheet, or into a different worksheet. Thanks, Tom picktr@wowway.co -- Message posted from http://www.ExcelForum.com If you enter the value in A1 of sheet1, put this in the other cell in sheet1: =A1 or in another worksheet: =Sheet1!A1 In article <picktr.15c6uy@excelforum-nospam.com>, picktr <<picktr.15c6uy@excelforum-nospam.com>> wrote: > After I enter a value in one cell, how can > I have it automatically enter it into another cell...

Reference / Copy Dynamic Data
Have a worksheet listing products and prices for numerous suppliers. eg.: Supplier--Product---Price ABC-------apple-----1.00 ABC-------orange----1.20 XYZ-------brick-----3.40 XYZ-------cement----0.80 This worksheet will change often. What I would like is to reference this information on other worksheets. I would also split the info onto a worksheet for each supplier. Therefore i will have a worksheet for ABC and for XYZ, and the info in these worksheets will change as the main "index" worksheet changes. eg. Worksheet ABC contains: Product---Price apple-----1.00 orange----1.20 ...