 |
 | 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 / Worksheet Functions / October 20062 Workbooks and 6 Worksheets - Need to Populate 1 Workbook
I have two EXCEL 2000 workbooks( Estimates.xls and Prices.xls). Workbook 'Estimates.xls' has 1 worksheet with empty cells (C&D) that need populating if columns A&B are filled in, see below. (snapshot of ESTIMATES.xls) A B C D 1 Product Batch Description Est Price 2 400 01 ____ $____ 3 400 03 ____ $____ 4 600 01 ____ $____ 5 200 02 ____ $____ 6 900 01 ____ $____ 7 900 02 ____ $____ Workbook 'Prices.xls' has 6 worksheets that are lists of different brands with prices used for referencing the 'Estimate.xls' workbook. Its sheets are titled 'Brand 1, Brand 2 thru Brand 6', see below. (snapshot of PRICES.xls, specifically Brand 1 worksheet) A B C D 1 Product Batch Description Est Price 2 200 01 Red $45 3 300 03 Blue Small $75 4 600 01 Orange Med $47 5 900 02 Large $87 6 400 01 Small $96 7 900 01 Medium $15 (PRICES.xls, Brand 2 worksheet) A B C D 1 Product Batch Description Est Price 2 100 01 Green $22 3 800 03 Brown $14 4 400 01 Black $95 5 200 02 XL $67 6 900 03 XXL $98 7 700 02 XXXL $10 (this is a snapshot of PRICES.xls, Brand 3 worksheet) A B C D 1 Product Batch Description Price 2 500 01 Cyan $44 3 400 03 Magenta $29 Worksheets 4 thru 6 are very similar. Any help with this is appreciated. (ps: I'm a beginner so please use details in response) Michelle You would need to use the VLOOKUP function to do what you're trying to do. However, one suggestion would be to have one workbook rather than two for this task. The reason for this is that the syntax for VLOOKUP formulas is rather complex; it is made more so by having a link to an external workbook. Since you say you're a beginner you may wish to save yourself the headache of auditing VLOOKUPS which reference external workbooks. In any event, this site explains how VLOOKUP works: http://www.techonthenet.com/excel/formulas/vlookup.php Best thing for you to do would go to the site, try the VLOOKUP, and, if you run into problems, reply to this post with the VLOOKUP syntax you are using and someone can help you. Dave  SignatureBrevity is the soul of wit. > I have two EXCEL 2000 workbooks( Estimates.xls and Prices.xls). > [quoted text clipped - 44 lines] > (ps: I'm a beginner so please use details in response) > Michelle Thank you Dave, but I tried the VLOOKUP and it does not work for all of my 6 sheets nor for the 2 criteria search (Product and Batch Code). Also, for accounting purposes I cannot move any data, because other departments reference that same Price.xls workbook. Michelle > You would need to use the VLOOKUP function to do what you're trying to do. > [quoted text clipped - 61 lines] > > (ps: I'm a beginner so please use details in response) > > Michelle excuse me Dave...Michelle, From your sheet title: Brand 1 : item 6 : Product 400, Batch 01, Description Small, Est.Price $96-- Brand 2 : item 4 : Product 400, Batch 01, Description Black, Est.Price $95-- Your encoding sheet A & B on Estimates.xls may not be enough if you dont add another column LIKE for Brand.... The BRANDS sheet Description is quite strange- some are by sizes, others are by colors....please check first the 6 Brand Sheet Description and Product coding....more power... "Bright minds are blessed to those who share them.."-rsb. > Thank you Dave, but I tried the VLOOKUP and it does not work for all of my 6 > sheets nor for the 2 criteria search (Product and Batch Code). [quoted text clipped - 69 lines] > > > (ps: I'm a beginner so please use details in response) > > > Michelle Romelsb, Thanks for replying but..................... In all honesty, the 'Descriptions', I have provided you are irrelevant, only cell fillers in place of the true info. The sheet names.....Brand 1, Brand 2, etc, etc. are also irrelevant, only sheet name fillers in place of the true info., So do not focus on those....... "My main interest is in finding a way to automatically poplulate cells from one workbook with 6 different sheets to another workbook with 1 sheet." I have tried the VLOOKUP function below with cntrl+shft+enter, it works for only 1 criteria search not both (product# and batch#). Maybe there is another function INDEX MATCH or something that I should be using......... =IF(OR('[Prices.xls]sheet1'!$A$2:$A$3=C2),VLOOKUP(C2,'[Prices.xls]sheet1'!$A$2:$C$3,3,FALSE),IF(OR('[Prices.xls]sheet2'!$A$2:$A$3=C2),VLOOKUP(C2,'[Prices.xls]sheet2'!$A$2:$C$3,3,FALSE),IF(OR('[Prices.xls]sheet3'!$A$2:$A$3=C2),VLOOKUP(C2,'[Prices.xls]sheet3'!$A$2:$C$3,3,FALSE),IF(OR('[Prices.xls]sheet4'!$A$2:$A$3=C2),VLOOKUP(C2,'[Prices.xls]sheet4'!$A$2:$C$3,3,FALSE),IF(OR('[Prices.xls]sheet5'!$A$2:$A$3=C2),VLOOKUP(C2,'[Prices.xls]sheet5'!$A$2:$C$3,3,FALSE),IF(OR('[Prices.xls]sheet6'!$A$2:$A$3=C2),VLOOKUP(C2,'[Prices.xls]sheet6'!$A$2:$C$3,3,FALSE),"")))))) Again Anyone with experience in this area please assist. Thank you much !! > excuse me Dave...Michelle, > From your sheet title: [quoted text clipped - 81 lines] > > > > (ps: I'm a beginner so please use details in response) > > > > Michelle Are you saying that you want to match on Product and Batch, and can you guarantee that there are no duplicates of these in your pricing worksheets? In your examples you have 400 01 in two sheets, so would you like to specify the Brand in another column of your Estimates file, or can we say that such duplicates will not occur in the actual Prices file? If you want to perform a lookup over a number of lookup tables, the standard approach is something like: =IF(ISNA(vlookup1),IF(ISNA(vlookup2),IF(ISNA(vlookup3),"missing",vlookup3),vlookup2),vlookup1) This is for 3 tables, but hopefully with the symmetry of the formula you can see how it could be extended to up to 6. I think you may be pushing it with the limit on the number of characters in a formula, so you may like to have a column which looks at 3 sheets and another column which looks at the other three sheets (these could be hidden) and then a final formula which chooses the appropriate value from the two hidden columns (at least one of which will contain "missing" if the entries are unique). However, you want to match on two columns, so VLOOKUP is not appropriate if you can't amend your pricing tables to amalgamate the lookups - you will have to use an INDEX/MATCH/MATCH combination. Hope this helps. Pete > Romelsb, > [quoted text clipped - 106 lines] > > > > > (ps: I'm a beginner so please use details in response) > > > > > Michelle Thanks Pete for replying to my message, but can you do me a favor and write out the "=IF(ISNA" formula using the actual cells in my example "IF(ISNA(VLOOKUP(A1??)", only because I'm not that experienced with this formula? Thank you again. MichelleS > Are you saying that you want to match on Product and Batch, and can you > guarantee that there are no duplicates of these in your pricing [quoted text clipped - 135 lines] > > > > > > (ps: I'm a beginner so please use details in response) > > > > > > Michelle Hi Michelle Would it be acceptable to use a VBA solution, to copy the data from each sheet of Prices.xls into one sheet in Estimates.xls called Summary? During the process, an extra column would be created which would place the name of the sheet tab from each sheet of prices, alongside the each row of data derived from that sheet. Therefore your final table in Estimates would have Brand as well all the other data, thereby getting round any problems with duplicate Product or Batch codes between sheets. If so the code is quite simple and fast, but I need to know if the 2 files reside in the same folder, if not where does each file reside. SignatureRegards Roger Govier > Thanks Pete for replying to my message, but can you do me a favor and > write [quoted text clipped - 175 lines] >> > > > > > (ps: I'm a beginner so please use details in response) >> > > > > > Michelle Roger, Yes, I think I could safely copy the 6 sheets of data onto 1 sheet in the Estimate file and call that Sheet 'Summary'. Also, to correct a constant question here, in my original data there are 'NO Repeats or Duplicate codes like the example'. Also, the two files do not reside in the same folder. Can you share with me a specific VBA solution for the example submitted? Thank you, MichelleS. > Hi Michelle > [quoted text clipped - 188 lines] > >> > > > > > (ps: I'm a beginner so please use details in response) > >> > > > > > Michelle Hi Michelle In your Workbook Summary.xls, create a sheet called Summary with headings in A1:E1 of Product, Batch, Description, Est Price, Brand Use the code below, after modifying the line Set Spath = to reflect the location of the folder containing Prices.xls. This code needs to be copied into a standard module in Summary.xls, (details how to copy shown below the code) Use Tools>Macro>Run to invoke the macro whenever needed. It deletes all entries on Summary sheet and rebuilds all from scratch each time it is run. Sub ColateData() Dim Wbook As Workbook, Spath As String Dim SourceSheet As Worksheet, DestSheet As Worksheet Dim addrow As Long, lastrow As Long Dim source As Range, dest As Range Application.ScreenUpdating = False ThisWorkbook.Sheets("Summary").Select Set DestSheet = ThisWorkbook.Sheets("Summary") With DestSheet lastrow = Cells(Rows.Count, 1).End(xlUp).Row If lastrow = 1 Then lastrow = 2 Rows("2:" & lastrow).Clear End With Set Spath = "C:\Excel\Ngroups\" ' <=== Change to suit Set Wbook = Workbooks.Open(Spath & "Prices.xls") For Each SourceSheet In Wbook.Worksheets With DestSheet lastrow = Cells(Rows.Count, 1).End(xlUp).Row End With addrow = SourceSheet.Cells(Rows.Count, 1).End(xlUp).Row Set source = SourceSheet.Range("A2:D" & addrow) Set dest = DestSheet.Range("A" & lastrow + 1) source.Copy dest DestSheet.Range("E" & lastrow + 1 & ":E" & lastrow + addrow - 1) = SourceSheet.Name Next Wbook.Close Savechanges:=False ThisWorkbook.Save Application.ScreenUpdating = True End Sub You can copy the code and paste it into your Visual Basic Editor (VBE) in a Standard Module located in your file. To do this, Alt + F11 (open VBE) Ctrl + R (open Project Explorer) Select the file name on the left Insert > Module Paste code in Module SignatureRegards Roger Govier > Roger, > [quoted text clipped - 242 lines] >> >> > > > > > (ps: I'm a beginner so please use details in response) >> >> > > > > > Michelle Hi Michelle and Roger, sorry for not responding sooner - have been getting ready to go away first thing in the morning. Roger - could you modify your code so that after it copied the six sheets from the Prices file it inserted a new column C and concatenated A and B (Product and Batch) together? Michelle - if Roger joins the two fields together in a new column C of your Summary sheet, then assuming there are no duplicates you can enter these formulae in columns C and D of your Estimates sheet: C2: =IF(ISNA(VLOOKUP(A2&B2,Summary!C:F,2,0)),"Not Present",VLOOKUP(A2&B2,Summary!C:F,2,0)) D2: =IF(ISNA(VLOOKUP(A2&B2,Summary!C:F,3,0)),"",VLOOKUP(A2&B2,Summary!C:F,3,0)) and you can add a column E with "Brand" in E1: E2: =IF(ISNA(VLOOKUP(A2&B2,Summary!C:F,4,0)),"",VLOOKUP(A2&B2,Summary!C:F,4,0)) If a match isn't found you will get the message "Not Present" in column C and blanks in D and E, otherwise you will get the Description, Price and Brand returned in these columns. If there are duplicates, then VLOOKUP will find the first of these. Copy the formulae down for as many entries as you have in A and B. Hope this helps. Pete > Hi Michelle > [quoted text clipped - 314 lines] > >> >> > > > > > (ps: I'm a beginner so please use details in response) > >> >> > > > > > Michelle Hi Pete and Michelle > Roger - could you modify your code so that after it copied the six > sheets from the Prices file it inserted a new column C and > concatenated > A and B (Product and Batch) together? I can if required, and I had played with using concatenation as a method for Michelle to achieve her objective without resorting to VBA - which would have been perfectly feasible. With the VBA solution, however, she has all of the data she needs without having to resort to any further Lookups of any description hence I do not really think it is required. Marking the top row and applying Data>Filter>Autofilter will permit the selection of any range of products, all of which will have their Description and Price. I have noticed 2 errors in my original code >> Set Spath = "C:\Excel\Ngroups\" ' <=== Change to suit Should not have the word Set at the beginning, it should just read Spath = "C:\Excel\Ngroups\" ' <=== Change to suit Also, I can get inconsistent results with the line >lastrow = Cells(Rows.Count, 1).End(xlUp).Row This should be amended to lastrow = DestSheet.Cells(Rows.Count, 1).End(xlUp).Row I have repeated the complete code below, with the inclusion of Pete's request for the concatenation. I have remmed out the 2 sections which have been added for this purpose, removing the single quote in front of the lines where shown will cause them to be invoked. Sub ColateData() Dim Wbook As Workbook, Spath As String Dim SourceSheet As Worksheet, DestSheet As Worksheet Dim addrow As Long, lastrow As Long Dim source As Range, dest As Range Application.ScreenUpdating = False ThisWorkbook.Sheets("Summary").Select Set DestSheet = ThisWorkbook.Sheets("Summary") With DestSheet lastrow = Cells(Rows.Count, 1).End(xlUp).Row If lastrow = 1 Then lastrow = 2 Rows("2:" & lastrow).Clear 'Remove single quotes at beginning of next 3 lines if Concatenation of 'Product and Batch is required 'If UCase(Range("C1")) <> UCase("Description") Then 'Columns("C:C").Delete 'End If End With Spath = "C:\Excel\Ngroups\" ' <=== Change to suit Set Wbook = Workbooks.Open(Spath & "Prices.xls") ' line amended to remove Set For Each SourceSheet In Wbook.Worksheets With DestSheet lastrow = DestSheet.Cells(Rows.Count, 1).End(xlUp).Row ' Destsheet added to line End With addrow = SourceSheet.Cells(Rows.Count, 1).End(xlUp).Row Set source = SourceSheet.Range("A2:D" & addrow) Set dest = DestSheet.Range("A" & lastrow + 1) source.Copy dest DestSheet.Range("E" & lastrow + 1 & ":E" & lastrow + addrow - 1) = SourceSheet.Name Next 'Remove single quotes at beginning of next 3 lines if Concatenation of 'Product and Batch is required ' DestSheet.Columns("C:C").Insert ' DestSheet.Range("C2").FormulaR1C1 = "=RC[-2]&""|""&RC[-1]" ' DestSheet.Range("C2").Copy DestSheet.Range("C3:C" & lastrow + addrow - 1) Wbook.Close Savechanges:=False ThisWorkbook.Save Application.ScreenUpdating = True End Sub Post back if you have any difficulties. SignatureRegards Roger Govier > Hi Michelle and Roger, > [quoted text clipped - 31 lines] > > Pete Thank you Roger for all your help with this......although I am not that versed with the below codes and am having difficulty using them. Will it be possible to forward you the actual 2 workbooks (Estimates.xls and Prices.xls) via email to see what I am visually trying to relay? Also, Pete thank you as well, even through your travels I still would like to express my appreciation. Hoping a safe return. MichelleS > Hi Pete and Michelle > [quoted text clipped - 122 lines] > > > > Pete Hi Michelle Fine, send the files to me direct. Remove NOSPAM from my email address to mail directly. SignatureRegards Roger Govier > Thank you Roger for all your help with this......although I am not > that [quoted text clipped - 149 lines] >> > >> > Pete Hi Michelle, Hope you managed to get something working. Pete, from Canada > Thank you Roger for all your help with this......although I am not that > versed with the below codes and am having difficulty using them. Will it be [quoted text clipped - 137 lines] > > > > > > Pete Hi Pete / Michelle Michelle sent me 2 sample workbooks. I had to make some substantial changes to the code I posted originally, and sent back to Michelle. She has just mailed me back to thank me, and whilst there are some other difficulties she still needs to resolve. I had asked her when mailing back, would she mind if I posted details of the changes, in order to "close the thread" and also so "Pete would see what has happened on return from his vacation". She agreed and said this thread can now be "closed". Basically the data in Pricelists was "dirty". There were many case where Lot No appeared in column A just once, with many Batch numbers in successive rows. There were also lots of blank rows throughout each Sheet in Pricelist. One Brand sheet, had an extra column E with quantities recorded. I read all data across from each sheet as per original code, but picking up columns A to E from all sheets. I then inserted missing Lot No.s for all relevant lines. I applied an Autofilter to the table, selected blank rows and deleted them. I inserted a column at C and made a concatenation of A2 & "|" &B2 ( I like to insert the pipe character "|" between concatenations so there can be no confusion between whether 36001 is 360 and 01 or 36 and 001. I created a range called Mydata for columns C to F for the range of rows within the cleaned data on the Summary sheet. On the sheet called Data in Michelle's Summary file, she had set up a table with headings in A4 to F4 of Item No., Lot No., Batch No., Quantity, Description, Unit Cost I amended her formulae in E5 and F5 to =IF(B5="","",IF(ISNA(VLOOKUP($B5&"|"&$C5,MyData,2,0)), "Not Present",VLOOKUP($B5&"|"&$C5,MyData,2,0))) with the Offset in F5 being changed from 2 to 3 At the beginning of the code, Autofilter is switched off for the Summary sheet, Column C containing the concatenation is deleted, all rows from row 2 to the last row of data are cleared before the procedure goes through the steps as outlined above. Pete have a great holiday. SignatureRegards Roger Govier > Hi Michelle, > [quoted text clipped - 165 lines] >> > > >> > > Pete |
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.