Data entered from list automatically enters number in another cell

I am using Excel 2007  and here is an simplified example of what I need.

   
    Items         Price

Potatoes        4.35
Apples          5.55
Oranges        7.95
Onions          4.55
Carrots         3.75


Items Column is List for Valid entries in table below

Prices are the numbers I want entered when I enter the Item

If I enter Oranges from Dropdown List in A16 , I want Excel to automatically 
enter the number 7.95 3 columns over in D16,or if it is quite a bit easier, 
just 1 column over in cell B16

Example of table I want:

My Entries:                Excel Enters
     
A16 Carrots               D16  3.75
A17 Oranges              D17  7.95
A18 Onions                D18  4.55
A19 Onions                D19  4.55
A20 Potatoes              D20  4.35


Thanks so much for your help !


0
Utf
2/19/2010 5:31:02 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

4 Replies
658 Views

Similar Articles

[PageSpeed] 58

Put this in D16:

=3DVLOOKUP(A16,A$2:B$6,2,0)

then copy down.

If you want it a bit more robust, then you will need something like
this:

=3DIF(A16=3D"","",IF(ISNA(VLOOKUP(A16,A$2:B$6,2,0)),"",VLOOKUP(A16,A$2:B
$6,2,0)))

Hope this helps.

Pete

On Feb 19, 5:31=A0pm, gbryce <gbr...@discussions.microsoft.com> wrote:
> I am using Excel 2007 =A0and here is an simplified example of what I need=
..
>
> =A0 =A0 Items =A0 =A0 =A0 =A0 Price
>
> Potatoes =A0 =A0 =A0 =A04.35
> Apples =A0 =A0 =A0 =A0 =A05.55
> Oranges =A0 =A0 =A0 =A07.95
> Onions =A0 =A0 =A0 =A0 =A04.55
> Carrots =A0 =A0 =A0 =A0 3.75
>
> Items Column is List for Valid entries in table below
>
> Prices are the numbers I want entered when I enter the Item
>
> If I enter Oranges from Dropdown List in A16 , I want Excel to automatica=
lly
> enter the number 7.95 3 columns over in D16,or if it is quite a bit easie=
r,
> just 1 column over in cell B16
>
> Example of table I want:
>
> My Entries: =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Excel Enters
>
> A16 Carrots =A0 =A0 =A0 =A0 =A0 =A0 =A0 D16 =A03.75
> A17 Oranges =A0 =A0 =A0 =A0 =A0 =A0 =A0D17 =A07.95
> A18 Onions =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0D18 =A04.55
> A19 Onions =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0D19 =A04.55
> A20 Potatoes =A0 =A0 =A0 =A0 =A0 =A0 =A0D20 =A04.35
>
> Thanks so much for your help !

0
Pete_UK
2/19/2010 5:43:49 PM
Take a look VLOOKUP function in help menu


"gbryce" wrote:

> I am using Excel 2007  and here is an simplified example of what I need.
> 
>    
>     Items         Price
> 
> Potatoes        4.35
> Apples          5.55
> Oranges        7.95
> Onions          4.55
> Carrots         3.75
> 
> 
> Items Column is List for Valid entries in table below
> 
> Prices are the numbers I want entered when I enter the Item
> 
> If I enter Oranges from Dropdown List in A16 , I want Excel to automatically 
> enter the number 7.95 3 columns over in D16,or if it is quite a bit easier, 
> just 1 column over in cell B16
> 
> Example of table I want:
> 
> My Entries:                Excel Enters
>      
> A16 Carrots               D16  3.75
> A17 Oranges              D17  7.95
> A18 Onions                D18  4.55
> A19 Onions                D19  4.55
> A20 Potatoes              D20  4.35
> 
> 
> Thanks so much for your help !
> 
> 
0
Utf
2/19/2010 6:19:01 PM
I had never used VLOOKUP function before but now I am beginning to understand 
how it works. It is exactly what I was needing. Thanks Guys.

"Pete_UK" wrote:

> Put this in D16:
> 
> =VLOOKUP(A16,A$2:B$6,2,0)
> 
> then copy down.
> 
> If you want it a bit more robust, then you will need something like
> this:
> 
> =IF(A16="","",IF(ISNA(VLOOKUP(A16,A$2:B$6,2,0)),"",VLOOKUP(A16,A$2:B
> $6,2,0)))
> 
> Hope this helps.
> 
> Pete
> 
> On Feb 19, 5:31 pm, gbryce <gbr...@discussions.microsoft.com> wrote:
> > I am using Excel 2007  and here is an simplified example of what I need..
> >
> >     Items         Price
> >
> > Potatoes        4.35
> > Apples          5.55
> > Oranges        7.95
> > Onions          4.55
> > Carrots         3.75
> >
> > Items Column is List for Valid entries in table below
> >
> > Prices are the numbers I want entered when I enter the Item
> >
> > If I enter Oranges from Dropdown List in A16 , I want Excel to automatically
> > enter the number 7.95 3 columns over in D16,or if it is quite a bit easier,
> > just 1 column over in cell B16
> >
> > Example of table I want:
> >
> > My Entries:                Excel Enters
> >
> > A16 Carrots               D16  3.75
> > A17 Oranges              D17  7.95
> > A18 Onions                D18  4.55
> > A19 Onions                D19  4.55
> > A20 Potatoes              D20  4.35
> >
> > Thanks so much for your help !
> 
> .
> 
0
Utf
2/19/2010 10:40:02 PM
You're welcome - thanks for feeding back.

Pete

On Feb 19, 10:40=A0pm, gbryce <gbr...@discussions.microsoft.com> wrote:
> I had never used VLOOKUP function before but now I am beginning to unders=
tand
> how it works. It is exactly what I was needing. Thanks Guys.
>
>
>
> "Pete_UK" wrote:
> > Put this in D16:
>
> > =3DVLOOKUP(A16,A$2:B$6,2,0)
>
> > then copy down.
>
> > If you want it a bit more robust, then you will need something like
> > this:
>
> > =3DIF(A16=3D"","",IF(ISNA(VLOOKUP(A16,A$2:B$6,2,0)),"",VLOOKUP(A16,A$2:=
B
> > $6,2,0)))
>
> > Hope this helps.
>
> > Pete
>
> > On Feb 19, 5:31 pm, gbryce <gbr...@discussions.microsoft.com> wrote:
> > > I am using Excel 2007 =A0and here is an simplified example of what I =
need..
>
> > > =A0 =A0 Items =A0 =A0 =A0 =A0 Price
>
> > > Potatoes =A0 =A0 =A0 =A04.35
> > > Apples =A0 =A0 =A0 =A0 =A05.55
> > > Oranges =A0 =A0 =A0 =A07.95
> > > Onions =A0 =A0 =A0 =A0 =A04.55
> > > Carrots =A0 =A0 =A0 =A0 3.75
>
> > > Items Column is List for Valid entries in table below
>
> > > Prices are the numbers I want entered when I enter the Item
>
> > > If I enter Oranges from Dropdown List in A16 , I want Excel to automa=
tically
> > > enter the number 7.95 3 columns over in D16,or if it is quite a bit e=
asier,
> > > just 1 column over in cell B16
>
> > > Example of table I want:
>
> > > My Entries: =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Excel Enters
>
> > > A16 Carrots =A0 =A0 =A0 =A0 =A0 =A0 =A0 D16 =A03.75
> > > A17 Oranges =A0 =A0 =A0 =A0 =A0 =A0 =A0D17 =A07.95
> > > A18 Onions =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0D18 =A04.55
> > > A19 Onions =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0D19 =A04.55
> > > A20 Potatoes =A0 =A0 =A0 =A0 =A0 =A0 =A0D20 =A04.35
>
> > > Thanks so much for your help !
>
> > .- Hide quoted text -
>
> - Show quoted text -

0
Pete_UK
2/20/2010 3:21:52 PM
Reply:

Similar Artilces:

Using Relative path for XML data file?
Is there a way to specify a relative path to an XML data file imported into Excel 2003? I am writing a web app that generates report data as XML for the user to download to their local machine. This data is to be consumed by an Excel reporting spreadsheet, which contains display-formatted tables and charts that are mapped to various data fields in an XML Map, which is in turn linked to the xml data file they will download. The idea is the user only needs to download the data for updates, not the whole spreadsheet. However, since I cannot predict the path where the user will store their...

Changing Cells and entering data in them
Thanks for the help again. Big thanks to Steve you've got me this far. I went out and bought a book, but it's like reading a foreign language. I was informed today that I can't have message boxes come up. I need to have the code point at the cells and if they are blank turn which ever one is blank red or if both are then both turn red then pause for each cell to be filled in. Cell F14 "Last Name" then automatically go to Cell F16 "First Name" on tab or enter. Basically if Cell F22 or F23 has an X in it, Cells F14 an F16 turn red and cell F14 has the focus...

Formula without using numbers after decimal in the answer
I have a formula that derives the answer from a figure with a decimal. I don't want to use the figures after the decimal. Is there a way to just use the whole number and omit the numbers after the decimal without having to manually key in all these numbers manually? Thanks, Mustang You can use the INT function. This 'rounds down' any number to th nearest integer, e.g. if A1=2.567, a formula in B2 of =INT(A1) return 2 HTH Bruc -- swatsp0 ----------------------------------------------------------------------- swatsp0p's Profile: http://www.excelforum.com/member.php?...

Find and Replace Footnote Numbering
I often have documents where I need to put formatting before or after the footnote number for every footnote, often for hundreds of footnotes...anyone know how I can do this in one shot? I know I can specify ^f in find and replace, but only in the find portion...it gives an error if you put it in replace. ...

how to find an item in a list control?
hi all, In a list Control how to find an item. If it is found then the item should be highlighted. how to do this? i wrote the following code but its not working what's wrong in the code? LVFINDINFO info; int nIndex; info.flags = LVFI_PARTIAL|LVFI_STRING; info.psz = (LPCTSTR) str; //fd.m_strEditCtrl; sprintf(st,"%d",m_ListCtrl.GetItemCount()); MessageBox(st,"Count"); for(int i=0;i < m_ListCtrl.GetItemCount();i++) { if((nIndex = m_ListCtrl.FindItem(&info,-1)) == -1 ) { m_ListCtrl.SetItemState(nIndex,LVIS_SELECTED,LVIS_SELECTED); } } thanks and r...

Moving incoming mail automatically to specific folders.
I have three separate E-Mail Accounts (1,2, and 3) I have created three subfolders under Inbox (Mail1, Mail2, Mail3) How can I automatically get the mail sent to the correct Mail in location (Mail1,Mail2 or Mail3). I am sure the answer like everything is easy when you know how but I have just spent an hour trying! Thanks Ted On Mon, 2 Jan 2006 00:24:44 -0000, "EddyStone" <teds@screaming.net> wrote: >I have three separate E-Mail Accounts (1,2, and 3) >I have created three subfolders under Inbox (Mail1, Mail2, Mail3) >How can I automatically get the mail sent to ...

contact list #13
Good Day, I hope I am in the right area, we are running Exchange 2003 one Server 2003 and have Outlook 03 clients. We have address lists in the public folder an admin and a vendor, these contain contact information on contrators and vendors. In the folder list I can browse down and few and click on a contact to send them an email, but when I open a new email to send and I click on to and what to chose the list from the address book drop down box they are not there. How do I set it up so vendors and Admin contact list appear in the drop down box in the contact list? Thank you David ...

Stop Outlook from starting up automatically
Once I installed Outlook2003, it now runs automatically on startup. How do I disable this? -- | +-- Julian | are you using a PDA that is trying to access the data in it? If so, make sure the device is not connected when you boot. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Guide) Author, Google and Other Search Engines (Visual QuickStart Guide) Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com Subscribe to Exchange Messaging Outlook ne...

Pulling data from separate tabs
When charting in Excel 2002 is there a way to use sets of data from two different tabs within the same worksheet? For example, a spreadsheet contains separate tabs for prior year and current year data. Is there a way to reference the data or label series to pick up data from both? I tried pointing and clicking, and then typing the following as a reference for the axis labels: ='Prior Year'!$B$110:$M$110,'Current Year'!'$B$110:$M$110 but receive an error that I'm referring to an external worksheet. I've used the comma (') in the past to reference breaks ...

Show date, time & day of week in one cell
Can I show the date, time and day of week in one cell. I have: 09/03/07 8:30 AM in one cell using the format (Format/Cells/Custom): [$-409]mm/dd/yy h:mm AM/PM;@ Excel refuses to accept ddd for Mon or dddd Monday at the end of the format I want it to read: 09/03/07 8:30 AM Monday in 1 cell. I have Excel 2003. One way: mm/dd/yy h:mm AM/PM dddd;@ In article <Xns99B8A3CAF9130pencilunlistedcom@208.49.82.220>, Burp <burp@beep.comINVALID> wrote: > Can I show the date, time and day of week in one cell. > > I have: > 09/03/07 8:30 AM > in one cell using the for...

random number generator
Hey is there a way to generate random numbers like you would for a draft. In other words, i have numbers 1 through 10 and generate a random order for those? Use the RAND() function. If 1-10 are in A1:A10, enter =RAND() in cell B1 and copy it down through B10. Recalc (F9), then sort by column B. Hope this helps, Hutch "pat67" wrote: > Hey is there a way to generate random numbers like you would for a > draft. In other words, i have numbers 1 through 10 and generate a > random order for those? > . > You would have to generate 10 random numbers ...

default values in a cell
Hello, can you help me please Cell B1 contains a complex mathematical formula which requires (in several places) a number from cell A1. Cell A1 can contain any integer number, but it is usually the same (30). I would like to be able to leave cell A1 empty, and only enter a number when it is not 30 , ie the default value of A1 is 30, unless specified. How do I go about this ? Should I look at conditional formatting, or put lots of IF functions into an already complex formula? Thank as always KK Use 2 cells Modify the complex formula to use B1 rather than A1 ( or any other un-u...

data sort
ok now should be simple >> I need to sort by month on data that is held in format >> day/month so eg 1511 1510 3011 3010 now custom/ends with/ 11... does not work custom/ends with/ ??11.. or *11 does not work either contains 11 does not work (& would also be wrong if data set contained 1011) but still I am stumped so any help would be great cheers Alex I would be inclined to add a new, temporary field of formulas that pull off the right 2 digits, and sort by that: =RIGHT(A1,2) -- Jim Rech Excel MVP ...

Formula for cross tab data filling
Hi All Excel 2003 How to using formula for data filling as below (Y/N) ? Sheet A Product A Product B Product C System A Y N Y System B Y Y N Sheet B System A Product A System A Product C System B Product A System B Product B moonhkt ...

number format with leading 0
Dose anyone know how to create a number format that would show a leading zero for the numbers 0-9. That is, so that 1 would show as 01. I can do with a text format but would like to retain the integrity of the number. Thanks Try this: Format>Cells>Number Category: Custom Type: 00 Click [OK] Does that help? *********** Regards, Ron "Darby" wrote: > Dose anyone know how to create a number format that would show a leading zero > for the numbers 0-9. That is, so that 1 would show as 01. I can do with a > text format but would like to retain the integrity...

Help with importing data
Can I have users fill in a form in Access and have that data be transferred and updated to a spreadsheet. Need for fill out several fields and then export to a specific spreadsheet and place that data into the cells that will update that cell (add to the total in that cell) of a spreadsheet. ...

Call & Place Graphic Based on Cell Value?
Is there any way to call & place a graphic image based upon a cell value? Maybe you can look at J.E. McGimpsey's page: http://www.mcgimpsey.com/excel/lookuppics.html documike wrote: > > Is there any way to call & place a graphic image based upon a cell value? -- Dave Peterson ...

predict future data
Is there a way to create an XY line graph wih plotted data, yet leave room to predict future data on the axes? I can get the graph, but the x and y axes stop at the last data points, and I want those axes continued so that the existing data can be examined and future data predicted and plotted on the same graph, but I am not sure how to accomplish this. Any suggestions would be appreciated. Thanks. Jeff 1) Click on data series in chart, use Add trendline; in Option tab specify some units forward OR 2) Read Help about TREND and FORECAST, and SLOPE and INTERCEPT OR 3) Get crystal bal...

Converting Formula into Numbers?
With a cell/s selected is there a way to convert the cell's formula into straight numbers (in turn removing all formula and leaving the same number)? -- Emp-Designer ------------------------------------------------------------------------ Emp-Designer's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=37564 View this thread: http://www.excelforum.com/showthread.php?threadid=571948 Emp-Designer a �crit : > With a cell/s selected is there a way to convert the cell's formula into > straight numbers (in turn removing all formula and leaving the same &...

Automatically copy input from one cell to another
After I enter a value in one cell, how can I have it automatically enter it into another cell, within the same worksheet, or into a different worksheet. Thanks, Tom picktr@wowway.co -- Message posted from http://www.ExcelForum.com If you enter the value in A1 of sheet1, put this in the other cell in sheet1: =A1 or in another worksheet: =Sheet1!A1 In article <picktr.15c6uy@excelforum-nospam.com>, picktr <<picktr.15c6uy@excelforum-nospam.com>> wrote: > After I enter a value in one cell, how can > I have it automatically enter it into another cell...

Is there a way to cut off unused cells on a sheet
It seems there are an infinite number of cells on a sheet. As I really dont have much info to enter on each sheet I was hoping there was a way I could somehow cut off all the extra stuff to the sides and bottoms. It is a hassle because everytime I scroll, it will scroll off the side or bottom way past what I was looking for. Thanks! A manual way is to goto the last used row and delete all rows below. Do the same with columns. SAVE -- Don Guillett SalesAid Software donaldb@281.com "newbie" <newbie@discussions.microsoft.com> wrote in message news:469AABE7-8D17-4B72-91C1-...

HOWTO: Customizing CRM
Hello, We would like to customize our CRM Opportunity form. We want to include a OpportunityNumber field, which will be auto-generated when the new opportunity is created, similar to auto-numbers for Contracts, Cases, Articles, Quotes, etc. Is it possible and how do we go about it? I've looked at CRM Deployment Manager. It's possible to create a simple custom Attribute for particular object. But I couldn't figure out how to make it an auto-generated number, like SQLServer Identity... Could I just modify OpportunityBase table in MSCRM database? Do I need to change METABASE databas...

Reference / Copy Dynamic Data
Have a worksheet listing products and prices for numerous suppliers. eg.: Supplier--Product---Price ABC-------apple-----1.00 ABC-------orange----1.20 XYZ-------brick-----3.40 XYZ-------cement----0.80 This worksheet will change often. What I would like is to reference this information on other worksheets. I would also split the info onto a worksheet for each supplier. Therefore i will have a worksheet for ABC and for XYZ, and the info in these worksheets will change as the main "index" worksheet changes. eg. Worksheet ABC contains: Product---Price apple-----1.00 orange----1.20 ...

How do I find the Data Analysis Plus toolpak for excel
does anyone know where I could find the data analysis plus toolpak. opher32 - > does anyone know where I could find the data analysis plus toolpak. < Start by looking on the CD that was in the back of the textbook. - Mike www.mikemiddleton.com ...

' in numbers and Pivot tables
I have a spreadsheet with multiple rows of the same ID number. The ID number (when you click on the cell) has a ' before the number. I have created a pivot table to condese this data into something more meaningful. (which works) However I do not see any ' in front of the ID number. I now need to use vlookup to lookup a value with the same ID as I have on another sheet. But when I use the vlookup formula, I get #N/A. However if I manually add ' in front of the ID in the main sheet it looks it up. There are thousands of rows, but I do not fancy putting an ' in f...