how do i link cells so that when typing in an item, the price app.

Hi there, 

I'm trying to create a quotation template which enables me to enter in an 
item in one cell and its corresponding price appear in the next cell? Is this 
possible? 

I have created a spreadsheet of Items and prices (of which there are going 
to be over 1000) as a refernce point but am unsure how to link these for 
automatic entry into the quotation.

Anyone know how to do this?

Cheers

julie
0
Flynn1 (2)
9/6/2004 5:13:02 AM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
449 Views

Similar Articles

[PageSpeed] 34

Hi Julie!

You need to build a table of the items and prices. For 
example, on sheet2 in column A you list the items and in 
column B you enter the corresponding price. Assume that 
table is in the range A1:B1000.

Now, on sheet1 cell A1 is where you enter the item and B1 
is where you want the corresponding price. So in B1 enter 
this formula:

=VLOOKUP(A1,Sheet2A1:B1000,2,0)

Now, some things to consider. If the item is not found in 
the table you will get a return of #N/A. Or, if cell A1 is 
empty you will also get a return of #N/A. You can suppress 
that by using this formula. It will leave cell B1 blank:

=IF(ISNA(VLOOKUP(A1,Sheet2A1:B1000,2,0)),"",VLOOKUP
(A1,Sheet2A1:B1000,2,0))

Biff

>-----Original Message-----
>Hi there, 
>
>I'm trying to create a quotation template which enables 
me to enter in an 
>item in one cell and its corresponding price appear in 
the next cell? Is this 
>possible? 
>
>I have created a spreadsheet of Items and prices (of 
which there are going 
>to be over 1000) as a refernce point but am unsure how to 
link these for 
>automatic entry into the quotation.
>
>Anyone know how to do this?
>
>Cheers
>
>julie
>.
>
0
biffinpitt (3172)
9/6/2004 5:40:20 AM
Looks like I forgot something!

The reference to sheet2 should be: Sheet2!A1:B1000

Biff

>-----Original Message-----
>Hi Julie!
>
>You need to build a table of the items and prices. For 
>example, on sheet2 in column A you list the items and in 
>column B you enter the corresponding price. Assume that 
>table is in the range A1:B1000.
>
>Now, on sheet1 cell A1 is where you enter the item and B1 
>is where you want the corresponding price. So in B1 enter 
>this formula:
>
>=VLOOKUP(A1,Sheet2A1:B1000,2,0)
>
>Now, some things to consider. If the item is not found in 
>the table you will get a return of #N/A. Or, if cell A1 
is 
>empty you will also get a return of #N/A. You can 
suppress 
>that by using this formula. It will leave cell B1 blank:
>
>=IF(ISNA(VLOOKUP(A1,Sheet2A1:B1000,2,0)),"",VLOOKUP
>(A1,Sheet2A1:B1000,2,0))
>
>Biff
>
>>-----Original Message-----
>>Hi there, 
>>
>>I'm trying to create a quotation template which enables 
>me to enter in an 
>>item in one cell and its corresponding price appear in 
>the next cell? Is this 
>>possible? 
>>
>>I have created a spreadsheet of Items and prices (of 
>which there are going 
>>to be over 1000) as a refernce point but am unsure how 
to 
>link these for 
>>automatic entry into the quotation.
>>
>>Anyone know how to do this?
>>
>>Cheers
>>
>>julie
>>.
>>
>.
>
0
biffinpitt (3172)
9/6/2004 6:17:53 AM
The address of the range needs to be absolute, also:

The reference to sheet2 should be: Sheet2!$A$1:$B$1000



Biff wrote:

> Looks like I forgot something!
> 
> The reference to sheet2 should be: Sheet2!A1:B1000
> 
> Biff
> 
> 
>>-----Original Message-----
>>Hi Julie!
>>
>>You need to build a table of the items and prices. For 
>>example, on sheet2 in column A you list the items and in 
>>column B you enter the corresponding price. Assume that 
>>table is in the range A1:B1000.
>>
>>Now, on sheet1 cell A1 is where you enter the item and B1 
>>is where you want the corresponding price. So in B1 enter 
>>this formula:
>>
>>=VLOOKUP(A1,Sheet2A1:B1000,2,0)
>>
>>Now, some things to consider. If the item is not found in 
>>the table you will get a return of #N/A. Or, if cell A1 
> 
> is 
> 
>>empty you will also get a return of #N/A. You can 
> 
> suppress 
> 
>>that by using this formula. It will leave cell B1 blank:
>>
>>=IF(ISNA(VLOOKUP(A1,Sheet2A1:B1000,2,0)),"",VLOOKUP
>>(A1,Sheet2A1:B1000,2,0))
>>
>>Biff
>>
>>
>>>-----Original Message-----
>>>Hi there, 
>>>
>>>I'm trying to create a quotation template which enables 
>>
>>me to enter in an 
>>
>>>item in one cell and its corresponding price appear in 
>>
>>the next cell? Is this 
>>
>>>possible? 
>>>
>>>I have created a spreadsheet of Items and prices (of 
>>
>>which there are going 
>>
>>>to be over 1000) as a refernce point but am unsure how 
> 
> to 
> 
>>link these for 
>>
>>>automatic entry into the quotation.
>>>
>>>Anyone know how to do this?
>>>
>>>Cheers
>>>
>>>julie
>>>.
>>>
>>
>>.
>>

-- 
Mike Argy
Custom Office solutions and
Windows/UNIX applications
0
Mike
9/6/2004 12:05:05 PM
>The address of the range needs to be absolute

Not necessarily. Only if the lookup formula is to be 
copied to other cells and the OP didn't mention that would 
be the case.

Biff

>-----Original Message-----
>The address of the range needs to be absolute, also:
>
>The reference to sheet2 should be: Sheet2!$A$1:$B$1000
>
>
>
>Biff wrote:
>
>> Looks like I forgot something!
>> 
>> The reference to sheet2 should be: Sheet2!A1:B1000
>> 
>> Biff
>> 
>> 
>>>-----Original Message-----
>>>Hi Julie!
>>>
>>>You need to build a table of the items and prices. For 
>>>example, on sheet2 in column A you list the items and 
in 
>>>column B you enter the corresponding price. Assume that 
>>>table is in the range A1:B1000.
>>>
>>>Now, on sheet1 cell A1 is where you enter the item and 
B1 
>>>is where you want the corresponding price. So in B1 
enter 
>>>this formula:
>>>
>>>=VLOOKUP(A1,Sheet2A1:B1000,2,0)
>>>
>>>Now, some things to consider. If the item is not found 
in 
>>>the table you will get a return of #N/A. Or, if cell A1 
>> 
>> is 
>> 
>>>empty you will also get a return of #N/A. You can 
>> 
>> suppress 
>> 
>>>that by using this formula. It will leave cell B1 blank:
>>>
>>>=IF(ISNA(VLOOKUP(A1,Sheet2A1:B1000,2,0)),"",VLOOKUP
>>>(A1,Sheet2A1:B1000,2,0))
>>>
>>>Biff
>>>
>>>
>>>>-----Original Message-----
>>>>Hi there, 
>>>>
>>>>I'm trying to create a quotation template which 
enables 
>>>
>>>me to enter in an 
>>>
>>>>item in one cell and its corresponding price appear in 
>>>
>>>the next cell? Is this 
>>>
>>>>possible? 
>>>>
>>>>I have created a spreadsheet of Items and prices (of 
>>>
>>>which there are going 
>>>
>>>>to be over 1000) as a refernce point but am unsure how 
>> 
>> to 
>> 
>>>link these for 
>>>
>>>>automatic entry into the quotation.
>>>>
>>>>Anyone know how to do this?
>>>>
>>>>Cheers
>>>>
>>>>julie
>>>>.
>>>>
>>>
>>>.
>>>
>
>-- 
>Mike Argy
>Custom Office solutions and
>Windows/UNIX applications
>.
>
0
biffinpitt (3172)
9/6/2004 5:55:45 PM
It is a good practice.  Next week, someone will insert a row or column 
above or to the left of the cell, and there goes your lookup function.

I like to make my products user-proof!! :-)


Mike Argy
Custom Office solutions and
Windows/UNIX applications



Biff wrote:
>>The address of the range needs to be absolute
> 
> 
> Not necessarily. Only if the lookup formula is to be 
> copied to other cells and the OP didn't mention that would 
> be the case.
> 
> Biff
> 
> 
>>-----Original Message-----
>>The address of the range needs to be absolute, also:
>>
>>The reference to sheet2 should be: Sheet2!$A$1:$B$1000
>>
>>
>>
>>Biff wrote:
>>
>>
>>>Looks like I forgot something!
>>>
>>>The reference to sheet2 should be: Sheet2!A1:B1000
>>>
>>>Biff
>>>
>>>
>>>
>>>>-----Original Message-----
>>>>Hi Julie!
>>>>
>>>>You need to build a table of the items and prices. For 
>>>>example, on sheet2 in column A you list the items and 
> 
> in 
> 
>>>>column B you enter the corresponding price. Assume that 
>>>>table is in the range A1:B1000.
>>>>
>>>>Now, on sheet1 cell A1 is where you enter the item and 
> 
> B1 
> 
>>>>is where you want the corresponding price. So in B1 
> 
> enter 
> 
>>>>this formula:
>>>>
>>>>=VLOOKUP(A1,Sheet2A1:B1000,2,0)
>>>>
>>>>Now, some things to consider. If the item is not found 
> 
> in 
> 
>>>>the table you will get a return of #N/A. Or, if cell A1 
>>>
>>>is 
>>>
>>>
>>>>empty you will also get a return of #N/A. You can 
>>>
>>>suppress 
>>>
>>>
>>>>that by using this formula. It will leave cell B1 blank:
>>>>
>>>>=IF(ISNA(VLOOKUP(A1,Sheet2A1:B1000,2,0)),"",VLOOKUP
>>>>(A1,Sheet2A1:B1000,2,0))
>>>>
>>>>Biff
>>>>
>>>>
>>>>
>>>>>-----Original Message-----
>>>>>Hi there, 
>>>>>
>>>>>I'm trying to create a quotation template which 
> 
> enables 
> 
>>>>me to enter in an 
>>>>
>>>>
>>>>>item in one cell and its corresponding price appear in 
>>>>
>>>>the next cell? Is this 
>>>>
>>>>
>>>>>possible? 
>>>>>
>>>>>I have created a spreadsheet of Items and prices (of 
>>>>
>>>>which there are going 
>>>>
>>>>
>>>>>to be over 1000) as a refernce point but am unsure how 
>>>
>>>to 
>>>
>>>
>>>>link these for 
>>>>
>>>>
>>>>>automatic entry into the quotation.
>>>>>
>>>>>Anyone know how to do this?
>>>>>
>>>>>Cheers
>>>>>
>>>>>julie
>>>>>.
>>>>>
>>>>
>>>>.
>>>>
>>
>>-- 
>>Mike Argy
>>Custom Office solutions and
>>Windows/UNIX applications
>>.
>>
0
Mike
9/7/2004 2:25:54 AM
Reply:

Similar Artilces:

Link or embed into Word?
Posted this in Word discussionwith no luck. Using Office 2003. 125 page Word doc with multiple small spreadsheets - embedded as objects. Having recurring corruption issues with these. Need Excel functionality, so don't want to use true Word tables. Is it better (more stable) to link instead of embed? Either way, is it better to use one large spreadsheet with multiple tabs, or lots of small spreadsheets? Thanks! ...

Combining Cells #2
I wish to combine two cells, want the cell to appear as Chicago Golf Club 04/15/04, where Chicago Golf Club is cell a1 and 04/14/04 is cell b1. When I use the formula a1&" "&b1 the date is shown as a number. Is there anyway it can be shown as a date? This cell will then be used to save the file, using a macro. Hi try =A1&" "&TEXT(B1,"MM/DD/YY") -- Regards Frank Kabel Frankfurt, Germany ABYPFCS wrote: > I wish to combine two cells, want the cell to appear as Chicago Golf > Club 04/15/04, where Chicago Golf Club is cell a1 and 04/14/04 ...

Why are there so many transaction types in Great plains
I am trying integrate my POS with Great plains. I am trying to figure out which tables to populate based on which table get populated when I create transactions in Great plains itself. But I can't decide which transaction I need to create. Why are there so many transaction types? There are receivables, SOP, and Invoicing. What is a user suppose to do. Make all these transaction for one sales order. Shouldn't it be that if someone enters an order the receivable and the invoice to that customer get created automatically. When ever I enter a sales order for a customer it doesn't...

cell formatting zero padding on binary no's
I'm struggling through doing some binary math on excel. Ugh. You'd think Excel is written by programmers, so they would see the value of having some programmer-friendly bitwise math and binary, octal and hex formatting stuff in there, if for no other reason than they could use it themselves. Anyway, that's my rant, here's my question I have some numbers in cells that I convert to binary C5: 55 DEC2BIN(C5) gives me 1010101 I'd really like to display that as 0101 0101 .... ie with a leading zero and a space between the nibbles. It appears that ce...

Rosters and adding hours linked to a persons name
Hi All, I have a roster that goes across 8 weeks . Each line (Say Line 2) corresponds to the number of hours in that week - being line 2 that a person works . Line 2 is 44 hours, Line 5 may be 36 hours etc etc. At the end of the 8 weeks I want to know how many hours each staff member has worked. Unfortunately staff do not start on a line and systematically progress from week to week down the line numbers but will often chop and change from line 2 to line 6 and then to line 4 etc. I enter the name of the person on the line across the spreadsheet but how can I now have the program look ...

The numbers entered change to symbols when leaving the cell
I am trying to enter 10,418 in a dollar amount, when I leave the cell it changes to this :########. How can I fix this? Make the column wider, or the font size smaller. George Gee "CH100688" <CH100688@discussions.microsoft.com> wrote in message news:303ADD36-C519-4A11-B38D-86E61DA66D53@microsoft.com... >I am trying to enter 10,418 in a dollar amount, when I leave the cell it > changes to this :########. How can I fix this? Thanks...that worked!! "George Gee" wrote: > Make the column wider, or the font size smaller. > > George Gee > >...

Paste Special+Link (for only a few objects)
Hi all, I know that I can Copy/Paste Special/Link an entire Visio drawing or a single object from a drawing, but when I try to do the same for a certain portion of my drawing, Visio will not allow me to do so. If I just want to copy and link, say, the top half of my drawing (copying and linking all objects and connectors from the top half) to another document, how would it be done? Thanks, Ivan ------=_NextPart_0001_6067E047 Content-Type: text/plain Content-Transfer-Encoding: 7bit I created a Group from several objects to make the Paste Link option function when pasting into Word. ...

Assembling a link to another document
Hi all ... More help needed please. I'm trying to construct an anchor link to another document for my shopping cart web app. something like: <a href="ShoppingBasket.aspx?isbn=<isbndata> ><img src="basket.gif"></a> where the <isbndata> is extracted from the xml file In my xsl file I've tried something like: <a href="ShoppingBasket.aspx?isbn=<xsl:apply-templates select="isbn" /> > <img src="images/basket.gif" valign="top" hspace="5" /></img> </a> but I'm get...

linking 2 worksheets together...
Hello all, The data i have is spread over two sheets. One sheet has the parts data and the other the componet part data, essentially i would like to link these two sheets so that when i look at a particular part i then see each component part that belongs to it and all the corresponding data. I was hoping for a dropdown menu or somekind of link. The data doesn't need to be modified, simply viewed. I'm not sure if i have explained this particularily well. I have average excel knowledge, but no VBS skills whatsoever... I hope someone can help. What do you mean by "so that when ...

Why can't others open same file types as me
Issue summary: Some editors in the same company [using same OS (Vista) and connected to corporate network are unable to open same files as me. We use .asp, .aspx and .cntxml (company custom built page) file extensions. Looking at my associated file extensions I cannot find .cntxml even listed - yet, I can open all the afore mentioned extension. I don't recall ever having had to do anything special to view these pages when I first started (3 years ago). And, when I had to re-load FP w/in the last year I didn't have any issues. I simply navigate to the folder, rt. cli...

Can I break an external link in Excel 97?
Try Bill Manville's excellent "FindLink" utility .. It's available at Stephen Bullen's page: http://www.oaltd.co.uk/MVP/Default.htm -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot>com ---- stadco <stadco@discussions.microsoft.com> wrote in message news:BE79E018-3460-47F7-A8D3-369889E7AF9E@microsoft.com... > Re: Can I break an external link in Excel 97? ...

Linking controls in different tabs of one form?
I am trying to create a database and I am having several issues. I would be greatful if someone could advise me. Here are some issues I am having. 1. I am trying to link to a combobox (Job#) on the "Main" tab and make the text control automatically update in the "materials" tab. (All part of one form). Based on what job # is selected in the main form, I want that selection to automatically poplulate the job# text control in the materials tab. I hope that makes sense. 2. Main form Combobox: Category Combobox: Job Name (depends on the category selected) - works fine ...

Copy Cell Value (text) to Header?
I want to grab the text in a cell (company's name) and automatically copy it to the header using a macro. Thanks for any help. Mike Mike, Try something like ActiveSheet.PageSetup.LeftHeader = ActiveSheet.Range("A1").Text -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "theFrog" <none@none.com> wrote in message news:3uyrb.8918$si2.4367128@news4.srv.hcvlny.cv.net... > I want to grab the text in a cell (company's name) and automatically copy it > to the header using a macro. ...

find all instances of a search item and put result in a single cel
Is it possible to search a column in one sheet for all occurrences of a search item and then put the result of that search in a single cell on a second sheet? For example, Sheet 1 may have something like this: A :: B John yellow Mary yellow Sue red Richard green Michael yellow In the second sheet, I have a row for each color, and I want to put in, say, H1, the names in Sheet 1 that have that color in column B. C :: H yellow John, Mary, Michael So, say I have "yellow" in B1, I want the spreads...

Cell Data Change to Trigger Macro
Is there a way to trigger a macro whenever data changes in a cell? The value in the cell will be varying numbers, so it can't be TRUE/FALSE etc. Thanks Mike, Yes. Put it in the sheet module, instead of a regular module. It should look like this: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then MsgBox "You changed cell A1, you did." End If If Not Intersect(Target, Range("A1").EntireColumn) Is Nothing Then MsgBox "You changed something in column A. You sure did." End If End ...

cell reference from =max
What i am trying to do is find the maximum value from a list of ammounts, then from that maximum value (which i need in one cell as a value) i need to find who that max figure relates to from a list of names ie. Sheet2 A B 1 fred 1.00 2 gareth 2.00 3 laura 3.00 Sheet3 A B 1 =max('sheet2'!B1:B3) =3.00 this cell needs to display name ie laura god what a nightmare, also from this i need to do the same with =large('sheet...

Sheet(CaseName).Delete Error 13 type mismatch
From Access: CaseName = "Dallas" xlApp.Run "ExtractData", CaseName In Excel: ExtractData(CaseName) This gives me an error 13 - type mismatch: Sheet(CaseName).Delete But this doesn't: Sheets("Dallas").Delete I think Sheet(CaseName) is looking for a number not a string. How do I convince it otherwise? Thanks, Mich Is it a typo that you've got Sheet(CaseName).Delete and not Sheets(CaseName).Delete? Have you declared CaseName as String in Excel? -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (n...

How do I move some information in one cell to another cell?
I have a column of information on a worksheet. Each cell in this column holds a number that looks like this 0408015568GH. I need to take the "GH" and move it to another column on the same worksheet. How do I do this? Hi Deborah, Not sure if you want the original to remain. If you don't a macro might be better. Don't know if it is always the last two characters but the following will handle it that way. =LEFT(A1,LEN(A1)-2) =RIGHT(A1,2) --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/e...

"Expression is typed incorrectly or is too complex..."
Hello, I have a form which is linked to a query/report. The general idea is that the user can fill out the form with combo boxes that have been populated with items in a table, to narrow down a search with a piece of equipment that crushes rock. The form has two date boxes (start and end dates), customer box, material box, and location box. Therefore the user can narrow his/her search on many categories. If I double click on the query in the database window view, little boxes come up asking for the values that the query is looking for on the form. If I manually type them in at this point,...

How to merge columns and rows into one cell besides using Merge and Center Icon? #2
J.E: Although David McRitchie's page seems to have everything about basic macros which you have said that this is the only solution to my question, I have tried the example of recording and running macros on my question but it doesnt seem to work; the rows and columns did not merge into one cell. heres how my question goes; I have text data value in D1; Super-Com Distribution Pte Ltd, and in D2; Sales and Stock Half-Yearly Report For 2002. I need to merge the rows and columns from A1 to J2 into one cell and centralise the data within. Could you just show me the way of doing it? I'm b...

URGENT: Query by Form, display in subform, link to / generate repo
I have developed a query and a form that will provide the parameters for that query. My challenge is that I want the results of the query to display in a subform below the search text box(es). Then I would like to have 2nd subform that provides links based on the value of one field to determine which reports may be selected. While I'm not new to Access, I'm still very new to VBA and want to know if this is possible. If possible, how to get it to work. This is pretty urgent as I have two different projects that will use this process. Thanks in advance for the help ... B...

Recovery of deleted items
Hi, Does anyone know if there is a way to recover a personal folder that included different folders for different messages as i accidentaily deleted them. Thanks John from backup... "John" <johnjsmoran21@eircom.net> wrote in message news:17b5d01c4497b$0262f020$a401280a@phx.gbl... > Hi, > Does anyone know if there is a way to recover a personal > folder that included different folders for different > messages as i accidentaily deleted them. > Thanks > John This may or may not work... http://www.sparnaaij.net/howto/restoredeleteditemsfromanoutlookpst.htm...

Using iif() wtih different data types
Hi, I have 2 tables and I'm trying to run a query that runs iff() statements by comparing columns from each table and returning True or False if they are the same. e.g. Using tables Person and User - compare_Name:iif([Person].[F_Name]= [User].[Name],TRUE, FALSE) The problem I am having is that [Person].[F_Name] is a text field and [User].[Name] is a memo field. My question is can I use type casting to help me compare the fields, and if so how? Thankx Mimo Try the CStr function or the Left function: compare_Name:iif([Person].[F_Name]= CStr([User].[Name]),TRUE, FALSE) or com...

Cell formatting #17
Periodically, all my formulas show the formula rather than the result. I've tried changing the cell format but it doesn't revert. I have to close the file without saving and then reopen. What causes this and how can I revers it? Thanks Try Ctrl ~ http://www.rondebruin.nl/print.htm#Formulas -- Regards Ron de Bruin http://www.rondebruin.nl "Wizzer" <anonymous@discussions.microsoft.com> wrote in message news:674c01c52805$2576fe30$a501280a@phx.gbl... > Periodically, all my formulas show the formula rather than > the result. I've tried changing th...

Conditional Format four top four cells
Using this for conditional formating, however if I have a blank cell it still changes the cell format according to the rule. =A1>=LARGE($A$1:$A$5,4) Data 2 3 5 7 8 Would like the 3,5,7,8 to change cell color based on formula in coditional Format Thanks Rob Rob, Your 'blank' cell is not blank - it has a space or a null string ("") in it. Try =AND(A1<>"",A1<>" ",A1>=LARGE($A$1:$A$5,4)) HTH, Bernie MS Excel MVP "Rob" <Rob@discussions.microsoft.com> wrote in message news:F382DA31-1889-4C1C-8178-3923A9BDB953@micro...