# Office 365 >  >  how do i create a system of entering a code in one cell to insert a text string in another

## uplandpoet

I have never used excel this way, and it may not be possible, but i used to work in quickbooks to create a job proposal/estimate.
the great thing was you could prewrite a description of about 50 items that made up 90% of my transactions, so with a 3 digit code i entered a long piece of writing.
the company i work for now does not use quickbooks, but we all have office, so i am wondering is there a way to have these pre-written text boxes so that when i enter, say "AWS22" it puts this copy in the cell: 	"Single Hung Aluminum Equal Lite White Frame Clear Impact Laminated Glass  26.00 x 26.00    $ 436.00" and then i can set up the next cell to know to multiply the quantity enter times the $436.00?

it seems like something excel can do, but i have no idea where to start.
thanks

----------


## protonLeah

title updated

----------


## TMS

@protonLeah: 



> Trying to duplicate some Quickbooks functions for a proposal form



I think this IS what the OP wants to do and probably has no idea how to ask for it in Excel terms.  Might be useful to mention Excel in there but, if someone were searching for Excel and Quickbooks, I'm guessing the links would relate to importing and exporting files from Excel.

@uplandpoet: you first need to set up a table/list.  This needs to contain columns for the code, the description and the price (as a minimum).  With that in place, you can use Data Validation to select the code and VLOOKUP to return the description and price.

----------


## uplandpoet

thank you! i changed the title, as best i could. ok, i have never made a table/list or used VLOOKUP, but now I now where to start playing around. I really appreciate it!

----------


## TMS

If you post a sample workbook with some typical data we can mock something up fpr you.

----------


## uplandpoet

i dont know ho to attach a work book. i dont see a "paperclip"

----------


## TMS

Attach a sample workbook.  Make sure there is just enough data to demonstrate your need.  Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.  Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

----------


## TMS

Are you really using Excel 2003?

----------


## uplandpoet

here is what i have.

----------


## TMS

Ok. I can do something with that. Just gone midnight here, so it'll be later today.

----------


## uplandpoet

thank you, at your time schedule will be much appreciated!

----------


## Glenn Kennedy

Take a look at this and see if it's getting close...

----------


## uplandpoet

Wow! Thank you! I broke the cells up, in fact i broke the size into two cells for other possible area/cost calculations, unless that doesnt work... i am attaching it. i tried modifying your formula and it worked! but now i guess i would need to extrapolate how to create new formulas to input size.

Thank you, thank you!

----------


## uplandpoet

no, i am two or three computers away from that status, i just bought a new laptop a couple months ago, so i suppose i have the newest. i will see if i can figure that out. funny thing, i started on lotus 123 when it was about a 20k file, what 40 years ago, too bad i didnt learn everything along the way, of course with the magicians who hang out here, i get by just fine. this has to be the best, most useful forum i have ever stumbled into for any subject, though the guys (and gals) over at the Am Diabetes forum are close....

----------


## Glenn Kennedy

In sheet4, B2, copied across and down:

=IFERROR(IF($A2="","",VLOOKUP($A2,Sheet3!$B$2:$F$57,COLUMNS($A1:B1),FALSE)),"Code Error")

----------


## TMS

Building on Glenn's original sample workbook, my attempt.  I've added Data Validation, Dynamic Named Ranges , VLOOKUPs, and moved the price extraction to sheet1

I really like the price extraction formula.  Neat  :Smilie:

----------


## uplandpoet

you guys are amazing!

You know the funny thing is, I am the go to geek guy n our office, and compared to you guys, I know nothing!!!!

----------


## TMS

You're welcome. Thanks for the rep.   :Smilie: 






> I am the go to geek guy n our office, and compared to you guys, I know nothing!



It just takes a little time ... 20 years or so should do it  :Smilie: 



If you are satisfied with the solution(s) provided, please mark your thread as Solved. 


*New quick method:*
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved. 

Or you can use this way:

*How to mark a thread Solved*
Go to the first post 
Click edit
Click Go Advanced
Just below the word *Title* you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you *by clicking on the little star at the bottom* left of their reply to your question.

----------


## uplandpoet

when i expanded my source sheet and tried to create an actual proposal form, some of it worked and some did not, adn i cant see what i am doing different on the error cells

----------


## Glenn Kennedy

You had not changed the range of the lookup beyond row 49, whereas your data go down to row sixty-something.  I have currently set them to 100.

There are still two code errors, as these codes do not exist in your raw data.  If you need to come back about this again, please be clear which sheet(s) you are referring to!!  I think I was looking in the right place!!

----------


## uplandpoet

Thank you! ok, i am trying to read the code, but pretty much got lost. I plan to have about 400 codes in all, i will note sheets in the future. again, thank you!

----------


## Glenn Kennedy

Glad to have helped.

One way to see what Excel is doing is to go to Formulas/evaluate formula and setp through the formula, so you can understand what's going on.

If you are using Excel 2003:

Within Excel, select the cell you want to evaluate.
From the Tools menu, point to Formula Auditing and click Evaluate Formula.
Click the Evaluate button to watch as Excel evaluates each step of the formula.
To view the evaluation again, click Restart.

----------


## uplandpoet

i have added a couple of different concepts. Can this be done this way? I want to add features to a product based on size and other features based on price i.e. this item can be tinted for a 4% premium and this item can be enhanced for $3 per square ft. I tried to do the percentage formula but it isn't doing what i hoped.

I am working from the sheet named proposal source and working on Proposal sheet.

any help would be great!

----------


## Glenn Kennedy

1.  can you add another column into the proposal sheet for each of tint, SS and Low E, using an X to designate if it's to be incorporated or left blank if not?

2.  What is the purpose of column H on the source table?  Can it be moved/deleted?

----------


## TMS

> *uplandpoet*: some of it worked and some did not, adn i cant see what i am doing different on the error cells







> *Glenn Kennedy*: You had not changed the range of the lookup beyond row 49, whereas your data go down to row sixty-something. I have currently set them to 100.



And, sadly, the formula will fail again if you go beyond 100 rows in your data table.

Have another look at the sample workbook I uploaded in Post #16.  That uses Dynamic Named Ranges which will cater for as many rows of data as you throw at it ... without needing to review your formula on a regular basis.

----------


## uplandpoet

i could add more columns, except i am running out of width, i might make the text cell a wrap cell and thus get more width function. "H" is a line item total, in the event someone needs multiple identical windows in one opening.

as far as adding more columns, if i wrap the text cell, how would i add the features i needed?

----------


## uplandpoet

Will do, thanks.

----------


## uplandpoet

i really like the dynamic aspect, but as i dont really undestand it, i dont know how to modify it to add and subtract features or how to direct it to a new table.

----------

