Auto Populate A Cell Based On Data Entered into Two Possible Cells.

[Angry Robot]

Hi everyone, I would like to thank everyone for your assistance up front. I'm really stumped on how to do this, or if it is even possible. This might belong in the VBA or Macro forum, since that might be what I end up having to do.

So, I work for a company that builds automated industrial equipment. I am creating a spreadsheet in order to help automate creating a Bill Of Material for each new machine we build. I am storing Internal Part Number, Manufacturer Part Number, Manufacturer, Description, and Supplier in an access database. Then I am pulling that data into a worksheet in my workbook. Right now I am using the VLOOKUP formula to populate the Manufacturer, Description, and Supplier based on entry into the Manufacturer Part Number cell. That works just fine, but it is a bit limiting having to use just the MFG. part number- because some part numbers are very long. We have assigned parts a much shorter internal part number in order to simplify things. So I could change the lookup to be based on the value in internal part number cell. The problem is that there are a few parts that I easily remember the mfg part number for and don't want to have to memorize the internal number.

I would like to be able to populate the dependent cells based on data entry into either Internal Part Number or Manufacturer Part Number. Is this possible?

And to throw another wrench into the works... if I need the Internal Part Number and Manufacturer Part Number cells to also reference to each other; so that way if I enter one, it will auto populate the correct value into the other cell.

[AliGW]

Yes, it's perfectly possible. Give a sample of your layout and we'll be able to provide a suggested formula.

[Angry Robot]

Here is a very basic template of what I am trying. I have removed the database info and just hardcoded in some test data.

[Angry Robot]

So... do I need to use some VBA scripting and macros then?

[AliGW]

Patience is a virtue! Remember that forum members are all over the world in different time zones.

I have only just seen this attachment, and can't look at it now, but will have a look later when I have a moment.

Have you included a mock-up of what you want with some expected outcomes entered manually?

[Angry Robot]

Sorry, I didn't intend to come across as impatient, I just wanted to bump the thread back up the list.

What I included was a copy of my working template with some basic test data entered into the table that normally stores my database info. I did not manually enter expected outcomes because I was worried it would be a bit confusing to have hard coded data there.

Essentially I need column "A" and "C" to look at each other and populate themselves with data from the database based on what is entered into the other column.

Scenario 1, I know the internal part number but not the mfg. number:

I enter "1" into Column A, and Column C will autofill with "TEST1".

Scenario 2, I know the mfg. part number but not the internal part number:

I enter Test 2 into Column C, and Column A will autofill with "2".

I'm not sure if I'm making any sense or not with how I'm describing it all.

[AliGW]

The problem that you will face is that you cannot have both a formula and a manually entered value in the same cell. You would be better off having some sort of drop-down to choose the part that you want and then have all other fields populating themselves based on that selection. In other words, you are going to need to decide which is going to be the unique identifier, then work with that.

[Angry Robot]

I'm actually ok with just overwriting the formula that is in the cell with manually entered data if it comes down to that. Theoretically there will only be 3 to 4 people using this worksheet, and we should all be starting with from the same master template every time. So if the formula in a cell is overwritten with the manually entered data, it wouldn't be a deal breaker as long the other cell's formula continued to work.

I think there will be way too many parts to have a drop down box.

[AliGW]

OK, well in that case, all you really need in each of those cells is a LOOKUP to match whichever one you use to the items in the database - then all other lookups can be based on whichever one you choose, as both will be there. Hope this makes sense.

[Angry Robot]

Thank you for your help.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.