+ Reply to Thread
Results 1 to 4 of 4

help automatically adding value based on previous column....for a form

  1. #1
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    help automatically adding value based on previous column....for a form

    Note: i have enclosed a file. Two worksheets in the file. "Sheet1" is the worksheet were data is entered. Worksheet="DataSelections" is where i identify item and its corresponding cost.

    On Sheet1 i want someone to select an item from a dropdown list........which i have done using datavalidation.

    After an item is selected i want the associated cost of this item (which you can get from the worksheet "DataSelections") to be automatically put into column B(cost column) on sheet1.

    I am not sure the best way to make this happen?

    Anythoughts.
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,391

    Re: help automatically adding value based on previous column....for a form

    Highlight the two ranges in the Selection sheet, select FORMULAS and click "Create from Selection", ticking only "Create values from Top Row"
    Then put this in B2 on sheet 1 and copy down:

    =IFERROR(INDEX(Cost,MATCH(A2,Description,0)),"")

    (Look in Sheet2. Find whichever row in the Description column (Col A) matches your item in Sheet 1, and copy the price from Col B of that row into sheet 1. And if there is no item in Col A in sheet 1, set Col B as blank)

    Ochimus
    Attached Files Attached Files
    Last edited by Ochimus; 02-18-2021 at 10:58 AM.

  3. #3
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,662

    Re: help automatically adding value based on previous column....for a form

    question........i was looking at some things online and a lot of people use vlookup to return the values...........is this not a good idea?
    are there limitations as a result of using vlookup?

  4. #4
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,391

    Re: help automatically adding value based on previous column....for a form

    Yes, lots of people prefer LookUps, and both formats do essentially the same thing, but I would point to three 'drawbacks' where Index/Match is "better"

    1. VLOOKUP can look only 'left to right.'
    If you put it in Col C and set it to find a value in Col J, it can do it. But if you want to look "right to left" (e.g. find the value in Col A that matches Col C) it can't do it. Index/Match does.

    2. Errors are easier with LOOKUPs.
    Especially in big spreadsheets, because you have to specify the column number holding the value you want. Entails you having to count the columns to find which one you want. If you get it wrong, but the error isn't obvious, finding and debugging the mistake can be a nightmare! INDEX MATCH, on the other hand, lets you quote the specific column letters. Much easier to follow and debug if things go wrong.

    3. VLOOKUP can slow down big spreadsheets.
    If you want a value in Col AD, you have to select thirty columns. Lot of extra computations, and large spreadsheets simply crawl.
    INDEX/MATCH requires you to select only the column containing the 'lookup' value, and the one containing the 'return' value, and nothing else. Much more efficient.

    BUT my real recommendation, as you are still on Office 2007, is to update to MS365.
    It's "online" (so you always have the latest versions automatically) and gives you a massive range of new features - and in this case it gives you "XLOOKUP" which is a Step Change on both VLOOKUP or HLOOKUP and Index/Match.

    Assume this is in B2
    =XLOOKUP(B5,C5:C80,D5:F80)

    One short and easy-to-follow formula that searches C5:C80 for whatever is in B5. When it finds it, it copies whatever is in Cols D - F of that row into B2 - D2!

    Hope this helps (and prompts the change?)

    Ochimus
    Last edited by Ochimus; 02-18-2021 at 12:11 PM.

+ 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] How to automatically add date based on the previous spreadsheet?
    By AmyV1 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 01-10-2022, 10:20 PM
  2. Dynatic textbox based on previous entry in a form.
    By agarcia.nd in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-20-2019, 05:31 PM
  3. Correct formula for adding columns based on previous row
    By packers316 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-02-2013, 06:44 AM
  4. [SOLVED] Merging two tabs and automatically adding columns based on the first column
    By amyqueen in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-03-2013, 04:41 PM
  5. Generating the next number automatically based on a previous cell value
    By hpfeiffer in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-04-2012, 03:04 AM
  6. Macro to automatically fill cells based on previous entries
    By jerinjan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-03-2008, 08:13 AM
  7. Adding Checkboxes to Form based on how many names in column?
    By BigDubbe in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-10-2008, 09:42 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