Create macro to fill multiple cells from input in one cell

**codilaine** · 09-28-2011, 10:29 PM

I am trying to figure out how to create a macro that will allow me to input a number ie 10 in any cell in column b which will then populate cells in column c, d and f of the same row with values specific to item 10
Example:

cell b input 10 - cell c populates with company name, cell d with city and cell f with an alpha numeric input

I have 15 accounts where instead of filling in the company name, city and account code across a row 100 times a day, I could create something that will let me enter the company number (1 through 15 for each account) and then it would just put the correct predetermined values in the other cells of that row. I assume I would need to create 15 little formulas to represent each company so that which ever account # I choose the values for it will then fill in across the row so:

Account #1 would mean I put 1 in cell b - cell c would then say ABC Co., cell D would say Your town and cell F would say BB then I would just repeat the formula 15 times. It would take a little time to set up but I have been typing these 15 companies in to a spreadsheet 100 times a day for a year - I don't mind setting up for each one, if it can be done.

I hope this makes sense, I have an idea that most here are way out of my league, I am just a mom from ND hacking my way around excel trying to make my life a little easier.

Thanks if you can figure out what I want and have an idea.

**Andrew-R** · 09-29-2011, 02:34 AM

How about something like the attached workbook? There's a lookup table of company information on sheet 2 and the formula on sheet 1 reference it to add company information when a value is entered in column B.

Is that the sort of thing you were after?

**arlu1201** · 09-29-2011, 02:43 AM

Andrew-R,
That's the exact thing i was going to work on. The only thing is i was only thinking of vlookup since i am not good at match. In Sheet1, why do you have the value column ?

I have attached a simple vlookup formula that i have used.

**arlu1201** · 09-29-2011, 02:54 AM

I had probs attaching the file. Here it is.

**Andrew-R** · 09-29-2011, 03:56 AM

I only used MATCH because we need to see if the VLOOKUP is going to return NA and the MATCH function is slightly shorter, so it was just a neatness thing - your VLOOKUP replacement is fine.

The value column is there because the OP said,

cell b input 10 - cell c populates with company name, cell d with city and cell f with an alpha numeric input

I'm wasn't sure what the alphanumeric input was, so I just added a generic column.