+ Reply to Thread
Results 1 to 28 of 28

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

  1. #1
    Registered User
    Join Date
    04-02-2014
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    30

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

    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
    Last edited by uplandpoet; 03-15-2017 at 04:30 PM. Reason: i screwed up somewhere

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Trying to duplicate some Quickbooks functions for a proposal form

    title updated
    Last edited by protonLeah; 03-12-2017 at 06:47 PM.
    Ben Van Johnson

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Trying to duplicate some Quickbooks functions for a proposal form

    @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.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Registered User
    Join Date
    04-02-2014
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: Trying to duplicate some Quickbooks functions for a proposal form

    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!

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: how do i create a system of entering a code in one cell to insert a text string in ano

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

  6. #6
    Registered User
    Join Date
    04-02-2014
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: how do i create a system of entering a code in one cell to insert a text string in ano

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

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: how do i create a system of entering a code in one cell to insert a text string in ano

    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.

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: how do i create a system of entering a code in one cell to insert a text string in ano

    Are you really using Excel 2003?

  9. #9
    Registered User
    Join Date
    04-02-2014
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: how do i create a system of entering a code in one cell to insert a text string in ano

    here is what i have.
    Attached Files Attached Files

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: how do i create a system of entering a code in one cell to insert a text string in ano

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

  11. #11
    Registered User
    Join Date
    04-02-2014
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: how do i create a system of entering a code in one cell to insert a text string in ano

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

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: how do i create a system of entering a code in one cell to insert a text string in ano

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




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  13. #13
    Registered User
    Join Date
    04-02-2014
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: how do i create a system of entering a code in one cell to insert a text string in ano

    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!
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    04-02-2014
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: how do i create a system of entering a code in one cell to insert a text string in ano

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

  15. #15
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: how do i create a system of entering a code in one cell to insert a text string in ano

    In sheet4, B2, copied across and down:

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

  16. #16
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: how do i create a system of entering a code in one cell to insert a text string in ano

    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
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    04-02-2014
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: how do i create a system of entering a code in one cell to insert a text string in ano

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

  18. #18
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: how do i create a system of entering a code in one cell to insert a text string in ano

    You're welcome. Thanks for the rep.


    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



    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.

  19. #19
    Registered User
    Join Date
    04-02-2014
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: how do i create a system of entering a code in one cell to insert a text string in ano

    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
    Attached Files Attached Files

  20. #20
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: how do i create a system of entering a code in one cell to insert a text string in ano

    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!!
    Attached Files Attached Files

  21. #21
    Registered User
    Join Date
    04-02-2014
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: how do i create a system of entering a code in one cell to insert a text string in ano

    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!

  22. #22
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: how do i create a system of entering a code in one cell to insert a text string in ano

    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.

  23. #23
    Registered User
    Join Date
    04-02-2014
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: how do i create a system of entering a code in one cell to insert a text string in ano

    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!
    Attached Files Attached Files

  24. #24
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,900

    Re: how do i create a system of entering a code in one cell to insert a text string in ano

    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?

  25. #25
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: how do i create a system of entering a code in one cell to insert a text string in ano

    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.

  26. #26
    Registered User
    Join Date
    04-02-2014
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: how do i create a system of entering a code in one cell to insert a text string in ano

    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?

  27. #27
    Registered User
    Join Date
    04-02-2014
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: how do i create a system of entering a code in one cell to insert a text string in ano

    Will do, thanks.

  28. #28
    Registered User
    Join Date
    04-02-2014
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    30

    Re: how do i create a system of entering a code in one cell to insert a text string in ano

    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.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Autobuild a proposal...
    By jmaejr in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-26-2014, 12:33 AM
  2. Using Excel to create a Proposal
    By DanB.S.F. in forum Hello..Introduce yourself
    Replies: 2
    Last Post: 12-19-2013, 09:49 AM
  3. Need help to create a proposal form in excel
    By mgdachs in forum Excel General
    Replies: 0
    Last Post: 01-20-2012, 10:45 AM
  4. Estimating & proposal writing
    By Contractor in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 01-08-2011, 07:39 AM
  5. creating a proposal page
    By PoolMan in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-08-2007, 04:24 PM
  6. Request Proposal
    By gabch in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-17-2006, 12:22 PM
  7. Creating a Flexible Proposal Form
    By Jason Roberts in forum Excel General
    Replies: 1
    Last Post: 08-26-2005, 12:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1