Populating data to other worksheet via data validation
I'm working on creating worksheet selection from a drop down
list. The drop down list contain: Mouse, Keyboard, Monitor for example: I have 2 worksheet in the same workbook. I have a worksheet called (BOM), one of this worksheet for Mouse, another for Keyboard and so on. that contain data in 4 column namely: Model, Part number, Part description and Quantity and has about 100 rows For example if i choose "Mouse" from the drop down list then I want cell A5 to fill with "Model number" and cell B5 with "Part number." and cell A6 with "Part description." and cell B6 with "Quantity" All of this fill-in information would be on another worksheet named Master plan(MP). I am using formula that refers to data data on the BOM worksheets like this: ='BOM'!A2 in cellA5 of MP, and ='BOM'!B2 in B5 of MP, ='BOM'!C2 in A6 of MP and so on. And i repeat by copying the formulas to cell below to extract the data for row 2 of the BOM worksheet. I continue until i get all 100 rows. My problem is when i copy this formula to some cell below, it does not extract the next row of data from the BOM worksheet. Instead it make relative reference to the cell thus returning wrong data. Is there a way of going about to do this. Any help is appriciated. Thank you. For example i choose from the drop-down list: mouse BOM worksheet:(corresponding to mouse) Model Part number Part Des Quantity M123 02-1234-12 Screw 1 M124 02-1235-12 Label 2 The data will be fill-in to the respective cell in the MP worksheet automatically MP worksheet: Model:Fill-in here Part number: Fill-in here Part Des:Fill-in here Quantity:Fill-in here |
Populating data to other worksheet via data validation
Hi,
Something like this? Sub AddFormulae() Dim ws1 As Worksheet, ws2 As Worksheet Dim ws2rng As Range Set ws1 = Worksheets("BOM") Set ws2 = Worksheets("MP") Set ws2rng = ws2.Range("a5") ws2.Range("a5:b300").ClearContents ws2.Range("a5:b300").NumberFormat = "General" lastrow = ws1.Cells(Rows.Count, "A").End(xlUp).Row For r = 2 To lastrow ws2rng.Formula = "=BOM!A" & r ws2rng.Offset(0, 1).Formula = "=BOM!B" & r ws2rng.Offset(1, 0).Formula = "=BOM!C" & r ws2rng.Offset(1, 1).Formula = "=BOM!D" & r Set ws2rng = ws2rng.Offset(2, 0) Next r End Sub "kuansheng" wrote: I'm working on creating worksheet selection from a drop down list. The drop down list contain: Mouse, Keyboard, Monitor for example: I have 2 worksheet in the same workbook. I have a worksheet called (BOM), one of this worksheet for Mouse, another for Keyboard and so on. that contain data in 4 column namely: Model, Part number, Part description and Quantity and has about 100 rows For example if i choose "Mouse" from the drop down list then I want cell A5 to fill with "Model number" and cell B5 with "Part number." and cell A6 with "Part description." and cell B6 with "Quantity" All of this fill-in information would be on another worksheet named Master plan(MP). I am using formula that refers to data data on the BOM worksheets like this: ='BOM'!A2 in cellA5 of MP, and ='BOM'!B2 in B5 of MP, ='BOM'!C2 in A6 of MP and so on. And i repeat by copying the formulas to cell below to extract the data for row 2 of the BOM worksheet. I continue until i get all 100 rows. My problem is when i copy this formula to some cell below, it does not extract the next row of data from the BOM worksheet. Instead it make relative reference to the cell thus returning wrong data. Is there a way of going about to do this. Any help is appriciated. Thank you. For example i choose from the drop-down list: mouse BOM worksheet:(corresponding to mouse) Model Part number Part Des Quantity M123 02-1234-12 Screw 1 M124 02-1235-12 Label 2 The data will be fill-in to the respective cell in the MP worksheet automatically MP worksheet: Model:Fill-in here Part number: Fill-in here Part Des:Fill-in here Quantity:Fill-in here |
Populating data to other worksheet via data validation
"kuansheng" wrote:
.. using formula that refers to data data on the BOM worksheet this: ='BOM'!A2 in cellA5 of MP, and ='BOM'!B2 in B5 of MP, ='BOM'!C2 in A6 of MP and so on. And i repeat by copying the formulas to cell below to extract the data for row 2 of the BOM worksheet. I continue until i get all 100 rows. My problem is when i copy this formula to some cell below, it does not extract the next row of data from the BOM worksheet. Instead it make relative reference to the cell thus returning wrong data. Perhaps try this: In A5: =INDIRECT("'BOM'!A"&INT((ROWS($A$1:A1)-1)/2)+2) In B5: =INDIRECT("'BOM'!B"&INT((ROWS($A$1:A1)-1)/2)+2) In A6: =INDIRECT("'BOM'!C"&INT((ROWS($A$1:A1)-1)/2)+2) In B6: =INDIRECT("'BOM'!D"&INT((ROWS($A$1:A1)-1)/2)+2) (The above returns the same as the 4 link formulas mentioned in your post snippet above. The 4 formulas are identical, except for the reference col letters: A, B, C, D for the sheetname: 'BOM'!) Select A5:B6, then drag the fill handle at the bottom right corner of B6 down as far as required** to produce the required links to BOM The above will increment the links sequentially, viz.: In A5:B6, links point to row2 In A7:B8, links point to row3 In A9:B10, links point to row4 (assume the above incrementation is what you wanted) **since you want to get 100 rows from BOM, copy down 200 rows to B206 -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
All times are GMT +1. The time now is 11:05 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com