 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / January 2007excel tables
Hi, I have a workbook with a two worksheets. Worksheet1 is like a parts table it has a listing of parts (windows) Window item, Cost of window, cost of screen, cost of grill. Worksheet2 is like a purchase order. I want to be able to select items from the parts table in my purchase order? I'm not real sure the best way to do that? Thanks, Geo Use Date Validate -> List to select the item Use VLOOKUP to get costs best wishes  SignatureBernard V Liengme www.stfx.ca/people/bliengme remove caps from email > Hi, > [quoted text clipped - 11 lines] > Thanks, > Geo I don't think Data Validate will work. I'm not able to pull the data from worksheet1 to worksheet2 (the purchase order) using data validate unless it was all on the same sheet. Worksheet 1 UNIT COST SCREEN GRILLES 2535 $ 130.46 $ 14.63 $ 19.10 2541 $ 144.01 $ 16.00 $ 19.10 2559 $ 154.65 $ 20.11 $ 19.10 2941 $ 163.04 $ 16.92 $ 28.04 2965 $ 177.23 $ 22.40 $ 41.44 3341 $ 142.07 $ 17.83 $ 28.04 359 $ 132.72 $ 21.94 $ 28.04 3365 $ 246.27 $ 23.31 $ 41.44 3371 $ 259.81 $ 24.68 $ 41.44 3747 $ 155.5 $ 20.11 $ 36.98 3757 $ 165.30 $ 22.40 $ 36.98 3759 $ 227.50 $ 22.85 $ 36.98 4159 $ 214.0 N/A $ 37.03 5953 $ 234.5 N/A $ 48.56 Worksheet 2 - In this worksheet I want to be able to pick from the parts table enter in quanties and have the cost added up accordingly. UNIT QTY COST QTY SCREEN QTY GRILLES LOCATION 2541 1 $ 144.01 1 $ 16.00 $ 19.10 KITCHEN SUB TOTALS: $ 134.01 DINING ROOM TAX: $ 11.73 KITCHEN TOTALS: $ 145.74 LAUNDRY > Use Date Validate -> List to select the item > Use VLOOKUP to get costs [quoted text clipped - 14 lines] >> Thanks, >> Geo Take your list on Worksheet 1 and give it a name (like PartsList). In your data validation, type =PartsList and you will see that you can have the parts list on the other page and still use data validation. How sweet is that? >I don't think Data Validate will work. I'm not able to pull the data from >worksheet1 to worksheet2 (the purchase order) using data validate unless it [quoted text clipped - 47 lines] >>> Thanks, >>> Geo You can use Data Validation list from another worksheet as long as you name the source range. Sheet1 Unit list is named MyList through Insert>Name Define. Sheet2 DV>.List>Source =MyList Gord Dibben MS Excel MVP >I don't think Data Validate will work. I'm not able to pull the data from >worksheet1 to worksheet2 (the purchase order) using data validate unless it [quoted text clipped - 47 lines] >>> Thanks, >>> Geo If you give the list a name and then refer to the name you can use a list from another sheet, select the list, in the name box above A1 type the name and press enter or do insert>name>define. Once you have named it, in the validation use allow > list and in the source box type =Name (equal sign and the name you gave it) Regards, Peo Sjoblom > I don't think Data Validate will work. I'm not able to pull the data from > worksheet1 to worksheet2 (the purchase order) using data validate unless it [quoted text clipped - 47 lines] >>> Thanks, >>> Geo kinda odd if you ask me. it wont' allow you to select another sheet while making the list, but it allows you to type in another sheet's name!R:C. anyways, if you prebuild the string =Sheet1!A1:A17 should work too cheers, orbii >I don't think Data Validate will work. I'm not able to pull the data from >worksheet1 to worksheet2 (the purchase order) using data validate unless it [quoted text clipped - 47 lines] >>> Thanks, >>> Geo Orbii as the previous repliers mentioned you cannot link directly to another sheet, either name the name and use the name range or use the indirect function as: =indirect("Sheet1!A1:A3") SignatureHope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. > kinda odd if you ask me. it wont' allow you to select another sheet while > making the list, but it allows you to type in another sheet's name!R:C. [quoted text clipped - 54 lines] > >>> Thanks, > >>> Geo hmm i'm using excel 2007, and i can... so try it :) happy new year! orbii > Orbii as the previous repliers mentioned you cannot link directly to > another [quoted text clipped - 66 lines] >> >>> Thanks, >> >>> Geo Orbii I have XL2007 installed and can see no difference between the functionality of DV in 2007 from 2003. That is you cannot select or type a straightforward range address on another sheet or in another book. How are you achieving this? SignatureHTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS www.nickhodge.co.uk > hmm i'm using excel 2007, and i can... so try it :) > [quoted text clipped - 70 lines] >>> >>> Thanks, >>> >>> Geo Hi Nick I have just installed Office 2007 and have been playing with XL2007. On sheet1 I entered some values in A1:A3 On Sheet2 I applied DV>List>=Sheet1!A1:A3 and it works perfectly. I agree that in earlier versions this was not possible. On another note, apart from a certain amount of frustration with finding where things are located, my general impression is very favourable - but perhaps it is early days yet!!! All the very best for 2007 (the New Year not Excel!!) SignatureRegards Roger Govier > Orbii > [quoted text clipped - 83 lines] >>>> >>> Thanks, >>>> >>> Geo Roger I tried it with Nick typed in Sheet2!A1 and then went to a cell on Sheet1 and applied DV>List and =Sheet2!A1 and =Sheet2!$A$1 and neither worked...very late for football, so try multi cells later SignatureHTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS www.nickhodge.co.uk > Hi Nick > [quoted text clipped - 96 lines] >>>>> >>> Thanks, >>>>> >>> Geo Hi Nick Using a single cell reference does come up with the message "You cannot use references to other Worksheets or Workbooks" but using multiple cell references works. Good luck against Leicester - I will be watching Arsenal v Sheffield on Sky a little later, along with my son. SignatureRegards Roger Govier > Roger > [quoted text clipped - 105 lines] >>>>>> >>> Thanks, >>>>>> >>> Geo OK I'm still on 2002. Point taken. But I think the user is still on a version lower than 2007. Anyway way glad to know there is something else save for exceedingly large files and a new menu bar. Have a good new year. SignatureMartin > hmm i'm using excel 2007, and i can... so try it :) > [quoted text clipped - 70 lines] > >> >>> Thanks, > >> >>> Geo i think i've moved almost everyfile this company has got excel wize over to 2007... and let me tell you... i freaking love it :) happy new year! orbii > OK I'm still on 2002. > [quoted text clipped - 82 lines] >> >> >>> Thanks, >> >> >>> Geo I uploaded an example you can look over if you like... http://www.graceba.net/~wmivey/Book1.xls > Hi, > [quoted text clipped - 11 lines] > Thanks, > Geo Thanks for all the help. What if they don't necessarily have to choose a screen or grill. So they can choose say 3 windows but don't have to choose a screen or a grill? Sometimes you can select a window without a screen or grill. >I uploaded an example you can look over if you like... > [quoted text clipped - 15 lines] >> Thanks, >> Geo This should do it... http://www.graceba.net/~wmivey/Book2.xls > Thanks for all the help. > [quoted text clipped - 21 lines] >>> Thanks, >>> Geo |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.