[Regulator]

I need some help with building a macro. I have attached a sample file for reference. What I need to do is pull info from one worksheet to another worksheet. In my wildest dreams I would like to type in the order number found in the reference material under the tab titled Indentured BOM into the "As Proposed Part Number" column found in the BOM1 tab and have the unit cost automatically populate with the corresponding Unit Cost found in the Indentured BOM tab. How can I do this?

[Zack Barresse]

Why do you need VBA? Can't you use a VLOOKUP() formula? I.e. in C14 enter...

=VLOOKUP(B14,'Indentured BOM'!$A:$J,2,0)

Of course you'd have to take into account something not being found, which you could do with a little error trapping in your formula...

=IF(ISNA(VLOOKUP(B14,'Indentured BOM'!$A:$J,2,0)),"NOT FOUND",VLOOKUP(B14,'Indentured BOM'!$A:$J,2,0))

And just change the "NOT FOUND" to whatever you want to show if the part number isn't seen on the other sheet.

Of coures we could do this with VBA as well, but it wouldn't be as efficient.

HTH

[OBP]

This sounds like an Access application.

[Regulator]

Vlookup would not work for my purposes. The reason behind setting up a macro is to alleviate the repitition of setting up a vlookup statement. I would like to build a "smart" spreadhseet that would have macros embedded in key cells and triggered by a change event to call a macro. So I type in the order number and whamo the unit cost automatically appears.

[Zack Barresse]

You can do that if you want, but why would you be setting up a formula every time? You only have to put them in once, then you can copy them down as needed. I don't think I fully understand the scope of what you're trying to do here. Can you explain how you're wanting to use your spreadsheet?

[Regulator]

No problem Zack. On working on a proposal for a bill of material, requirements are constantly changing. In 1 proposal we could have several bills of material that roll up to 1 top assembly. What I have to do is pull the part number/order number for 1 bill of material and so on from the overall indentured bill of material and populate the cost. On any given proposal I will have to re-price and reconfigure the indentured bill of material dozens of times. So if I had a fixed vlookup formula, I would constantly be fixing ranges and I might as well just do a simple "find" than mess with a formula. If you think a Vlookup could work, then I'm game to see how it could fit my requirement. Thanks!

[Zack Barresse]

Hmm, well with the data range changing that may not work. Do you always have the same headers? And are these different workbooks, or data put into the same workbook? A couple of real-world examples would go a long way, especially if they're different and show the entire contrast of the data structure you have and what you're trying to accomplish.

[Regulator]

If you take a look at my first post, I have attached an example of what the 2 spreadsheets I work with look like. The headers never change and they are always in the same workbook.

[Zack Barresse]

Okay, so the workbook in this thread is what you're working with. Do you want to do the lookup based on the value in col A or col B of the BOM1 worksheet? And will the start row always be 14? Are you wanting to re-populate the data everytime? If not, how would you know to exclude it? Also, on the BOM1 sheet your headers to do not match that of the headers on the Indentured BOM sheet, you have them broken up into two rows (which I highly discourage). For this to work well, you'll need them to match exactly. Can you do that?

[Regulator]

Zack, in BOM1 the lookup value should be based on column B and yes the start row will always be 14. Also, I will have to re-populate the data everytime. In terms of the sheet headers matching, I added the headers myself in the Indenture BOM tab, so these headers are flexible and can change to match the one's found in the BOM1 tab. Unfortunately the headers in the BOM1 tab are static and cannot change. Thanks!

[Zack Barresse]

Ok, well as long as they match and are unique. You really should have them on one row though. Having them on two rows (IMHO) is tacky. Plus it makes writing logic for it more difficult. So what will the Indentured BOM sheet look like then? Are you going to change the structure of the data to have a 2-row header?

[Regulator]

Unfortunately the format of the BOM1 spreadsheet was designed to meet strict federal guidelines, hence the tackiness. I updated the headers to reflect each other. Let me know if this works. Same file that I started with in my first posting.

[Zack Barresse]

I don't see any changes. The QTY columns on the BOM1 sheet still show two types, whereas the other sheet shows only one. The only one that somewhat matches is the Description, but even that's not exactly the same as it's all caps on the BOM1 sheet and in proper case on the other. Go ahead and re-post the spreadsheet with your changes (in a new post).

[Regulator]

Zack, do I need headers on the other columns in the Indentured BOM sheet? Honestly, I never use the info from these columns so I would not know what to call them. Here's the updated sheet.

[Zack Barresse]

What's going on with the blank row (3) on the Indentured BOM sheet? Is it there for a reason? What do you want to do if there is no match? Show no data?