data from one column to produce data for next 3 columns

sasoderl

Board Regular
Joined
Feb 17, 2004
Messages
82
:rolleyes:

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???
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
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

Board Regular
Joined
Feb 17, 2004
Messages
82
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

Board Regular
Joined
Feb 1, 2004
Messages
57
i think that you have to name each individual cell and not the whole sheet
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196

ADVERTISEMENT

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

Board Regular
Joined
Feb 17, 2004
Messages
82
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

Well-known Member
Joined
Dec 2, 2003
Messages
3,196

ADVERTISEMENT

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

Board Regular
Joined
Feb 17, 2004
Messages
82
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

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
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

Board Regular
Joined
Feb 17, 2004
Messages
82
unfortunately, that did not work either. And yes, in the name box, when i choose "data" it highlights the entire range. I am stumped.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,494
Messages
5,596,487
Members
414,070
Latest member
DuncanLucas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top