+ Reply to Thread
Results 1 to 12 of 12

Creating an invoice - multiple inputs to pull from multiple return values

  1. #1
    Registered User
    Join Date
    04-04-2018
    Location
    Missouri USA
    MS-Off Ver
    2016
    Posts
    6

    Creating an invoice - multiple inputs to pull from multiple return values

    Hi there! I know this should be simple, but my brain just isn't working with me.

    I'm trying to make an invoice template where I have multiple values that I will select, preferably from a drop-down menu I created (items labeled with A, B, C, D). If I select A, I want it to return the value of 1 in the next cell over, B return 2, C 3, etc. I have all of this information already in both a table and a pivot chart on a second tab, but that's where my brain is shutting down and I don't know what the easiest and most appropriate way to make this work would be.

    Any help is greatly appreciated.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,408

    Re: Creating an invoice - multiple inputs to pull from multiple return values

    In the next cell over from your drop-down (assume this is in A2, so we are talking about in B2) you can use this formula:

    =IF(A2="","",CODE(A2)-64)

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    04-04-2018
    Location
    Missouri USA
    MS-Off Ver
    2016
    Posts
    6

    Re: Creating an invoice - multiple inputs to pull from multiple return values

    I will try that! Can you tell me what the "CODE(A2)-64" part is though? I've never seen anything like that. Also, can I repeat that within the same parens?

    I want to be able to have that one formula look to see if the cell next to it says A, B, C, D, etc, and return a different value for each.

    Thanks!

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,408

    Re: Creating an invoice - multiple inputs to pull from multiple return values

    The CODE function returns the ASCII code of the first character in the cell - if this is "A" (without the quotes) it will return 65, for "B" it will be 66 etc., hence the reason for subtracting 64 from this value.

    I don't know what your second paragraph means - perhaps you should attach a sample Excel workbook, to explain it more clearly.

    To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

    Please note that the Paperclip icon (Attachments button) does not work on this forum, so don't try to use that.

    Hope this helps.

    Pete

  5. #5
    Registered User
    Join Date
    04-04-2018
    Location
    Missouri USA
    MS-Off Ver
    2016
    Posts
    6

    Re: Creating an invoice - multiple inputs to pull from multiple return values

    Thanks for that explanation!

    Here's an example of my data I'm wanting to use. I want to be able to put anything from column A (the words) into a cell (A1 for ease) and have B1 return the corresponding numeric value (below) based on what was entered into A1. Does that make sense?

    Ideally I'd love to have a drop-down or some other form to pull the information from Column A as my data is much more in-depth than letters. (Specific billing codes for my employer that there are so many of that I don't want to have to try to memorize and type manually each time.)

    I can provide a basic Excel if that's easier, I'd just have to edit my current one so I don't infringe on any proprietary Information.

    A 1
    B 2
    C 3
    D 4
    E 5
    F 6

  6. #6
    Registered User
    Join Date
    04-04-2018
    Location
    Missouri USA
    MS-Off Ver
    2016
    Posts
    6

    Re: Creating an invoice - multiple inputs to pull from multiple return values

    Here, hopefully this attached.
    Attached Files Attached Files

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,408

    Re: Creating an invoice - multiple inputs to pull from multiple return values

    Yes it would be helpful to see a sample Excel workbook, which has the same layout are your real file even if the data in there is made up.

    It would probably be easier to use a table of descriptions and codes, and then you can use a VLOOKUP formula to retrieve the appropriate code from a given description, but until I can see some realistic example data (and the say it is laid out) I can't suggest a specific formula for you to use.

    Hope this helps.

    Pete

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,408

    Re: Creating an invoice - multiple inputs to pull from multiple return values

    Ah, you attached it while I was replying. Give me a few minutes while I have a coffee, then I'll take a look.

    Pete

  9. #9
    Registered User
    Join Date
    04-04-2018
    Location
    Missouri USA
    MS-Off Ver
    2016
    Posts
    6

    Re: Creating an invoice - multiple inputs to pull from multiple return values

    Thank you so much for all your time helping me! It's been too many years since I did something this "complicated" within Excel for me to remember how it can be done.

  10. #10
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,408

    Re: Creating an invoice - multiple inputs to pull from multiple return values

    If the table on Sheet2 is representative of what you are going to enter into column B of Sheet1 (i.e. single letters), then you can have this formula in C7:

    =IF(B7="","",CODE(B7)-64)

    However, if column A of the table on Sheet2 is more descriptive (i.e. words for different types), then you can have this formula in C7:

    =IFERROR(VLOOKUP(B7,Sheet2!$A:$B,2,0),"")

    Copy the formula down as far as you need it. Note that some of your entries in column A of Sheet2 have spaces after them, so you do not get an exact match.

    Hope this helps.

    Pete

  11. #11
    Registered User
    Join Date
    04-04-2018
    Location
    Missouri USA
    MS-Off Ver
    2016
    Posts
    6

    Re: Creating an invoice - multiple inputs to pull from multiple return values

    Sir, I would buy you a coffee if you were local! That worked PERFECTLY! Thank you so much!!!

  12. #12
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    25,408

    Re: Creating an invoice - multiple inputs to pull from multiple return values

    Glad to help.

    In your workbook you indicated it would be nice to select column B values from a drop-down. You can achieve this by using Data Validation on those cells. To begin with, set up a named range which covers the entries in column A of Sheet2 (suppose you call this "my_name"). then select the cells in column B of Sheet1 that you want this to apply to, then click on the Data tab and then Data Validation (twice, for some reason), then under Allow click on List and then in the Source box enter:

    =my_name

    then click OK. Now when you select one of those cells in column B you will see the DV pull-down on the edge of the cell, and you can select the entry from the list.

    Hope this helps.

    By the way, if that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post and mark this thread as SOLVED.

    Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile). It's cheaper than a coffee !!

    Pete

+ 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. Replies: 4
    Last Post: 10-22-2015, 02:51 PM
  2. Replies: 2
    Last Post: 05-26-2015, 07:29 PM
  3. Replies: 1
    Last Post: 04-20-2015, 03:18 PM
  4. Replies: 1
    Last Post: 02-03-2015, 01:34 AM
  5. Replies: 1
    Last Post: 06-12-2014, 10:58 AM
  6. Replies: 7
    Last Post: 09-22-2013, 03:40 AM
  7. Creating a schedule from multiple inputs
    By AnctEgypt in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-24-2013, 10:45 AM

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