data from one column to produce data for next 3 columns

[sasoderl]

I am trying to write a macro OR something that will allow me to take the data entries in one column and produce the data that belongs in the next three columns.

The idea is that I have one column of numbers that range from 36 to 103. Each number is a "code" that has information that I want to appear in the spreadsheet but don't want to type it all up every time. With each of these numbers, I need to insert into the next 3 columns a two-letter code, a sentence, and a price. The info that belongs to the "code number" will never change. I'd like to automate this. How???

 

[Cbrine]

Sounds like you need a few vlookups.

You will need to setup a sheet like this, with only unique entries in column A. The others can have duplicated info.

A...B....C...................D...
36..AB..This is a test...5.99
37..BB..This is a test....6.99
....

103..BC..This is atest...7.99

Name the table Data.

On your orginal sheet enter the following formula assuming the numeric code exists in column A(Otherwise you will need to adjust the cell address in the formula.

Cell B2
=Vlookup(A2,data,2,false)
Cell C2
=vlookup(A2,data,3,false)
Cell D2
=Vlookup(A2,data,4,false)

This should give you the results you are looking for.

 

[sasoderl]

Thank you CBrine. This was helpful, but now I am running into a situation where I must have missed something along the way. I created a new workbook sheet and filled in the cells with the info in the format you suggested. Then, I named it DATA. Then, I returned to my original worksheet and tried the vlookup and it gives me the #NAME? error. I tried to do the same thing within the new sheet called DATA and got the same error. Any suggestions?

Thanks

 

[scuzz]

i think that you have to name each individual cell and not the whole sheet

 

[Cbrine]

Scuzz is correct, you need to create a named range, not set the sheet name to it.
Highlight the data table you want the vlookup to work on.
Click on the drop down menu box just to the left of your formula entry toolbar(Should have a cell address in it)
Type Data over the cell address and press enter.
This creates a named range of the data you want to do the search on.
If this explanation is unclear to you, do a search in the Excel help on Named ranges, and this should help as well.

 

[sasoderl]

Thank you both, I am obviously a beginner at Excel but have a degree in computer science. Bet you couldn't tell :wink:
However, I am still experiencing one last (hopefully) problem. I select the entire range, change the name to data in the name box, yet it will only change the name in A1 and not the entire range???

Thanks

 

[Cbrine]

If the range existed prior to this, you will need to go into
Insert...Name... Define
select the range in the pop up window and select delete.

Once you assign the range on the screen, you must delete before you can use that name again. So, if you messed it the first time, you need to delete the named range and try again. It won't overwrite.

To test if you have it correct. Once you have assigned it, you should be able to select it from the drop down box and have it selected automatically.

 

[sasoderl]

Okay, I have named the entire range "data", now, vlookup is only working for the first number "code" in my "data" table. Any suggestions on what I am doing wrong?

Really appreciate all of your help!!!!

 

[Cbrine]

Did you test the named range by selecting from the drop down menu in the name combobox? When you did this, it should have highlighted the entire data table range? That's the only place an error would cause the results you mentioned, if the named range is only selecting the first row of your data, the vlookup will only return the first row.

You can replace the named range in all your vlookup's with :

SheetName!$A$1:$A$35

SheetName being the name of the sheet with the data table. Make sure you use absolute referencing.

See if that works, if it does then your named range was wrong.

 

[sasoderl]

unfortunately, that did not work either. And yes, in the name box, when i choose "data" it highlights the entire range. I am stumped.