+ Reply to Thread
Results 1 to 19 of 19

VLOOKUP and manual data entry

  1. #1
    Registered User
    Join Date
    11-26-2012
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    11

    VLOOKUP and manual data entry

    I have created a spreadsheet for some purchase orders, I figured out how to use VLOOKUP to "auto fill" contractor information into relevant fields... however there is on occasion variable contractors we may contact from time to time as "one offs" is there a way to maintain the VLOOKUP formula in the cell and also allow for manual contractor details to be entered?

    If you need any more information please let me know.

  2. #2
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: VLOOKUP and manual data entry

    Hi

    Just add the data for the variable contractor in your data table, then change your lookup value.

    Kevin

  3. #3
    Registered User
    Join Date
    11-26-2012
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: VLOOKUP and manual data entry

    Unfortunately that might be too tricky, I'm putting this document together for a dept that I dont quite think will understand formula's, value's etc I was hoping there might be a way around amending the function fields each time

  4. #4
    Forum Contributor Steve N.'s Avatar
    Join Date
    12-22-2011
    Location
    USA
    MS-Off Ver
    Excel 2007, 2010
    Posts
    298

    Re: VLOOKUP and manual data entry

    Can you post a sample wb?

  5. #5
    Registered User
    Join Date
    11-26-2012
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: VLOOKUP and manual data entry

    Good idea,

    Brief explanation;
    I have been given this workbook to "bring it up to date" it was made a few years back, very basic and I'm a bit of a noob with this as well...
    I'm currently working off tab "140" with the VLOOKUP forumals which link to the contractors tab.
    Attached Files Attached Files

  6. #6
    Forum Contributor Steve N.'s Avatar
    Join Date
    12-22-2011
    Location
    USA
    MS-Off Ver
    Excel 2007, 2010
    Posts
    298

    Re: VLOOKUP and manual data entry

    Ok, as far as your question -

    "...is there a way to maintain the VLOOKUP formula in the cell and also allow for manual contractor details to be entered?"

    Not that I know of. But I've attached a possible solution. I changed your Contractors data range to a Table named Contractors_Tbl (It's in the Name Manager). I also created named ranges for each colummn in that table (also located in the Names Manager).

    I referenced your VLOOKUP formulas to the Contractors_Tbl. Your formulas now look like this
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and changed FALSE to 0 since it means the same thing "exact match" in this function. Saves typing and typos (for me anyway).

    All you need to do with this set-up is enter a new name and info into the table on your Contractors tab. Just start typing in the first empty cell in column A and go from there.

    Your dropdown list will automatically see the new Contractor entry...you know the rest

    Hope I didn't over explain - just didn't know your experience with tables...

  7. #7
    Registered User
    Join Date
    11-26-2012
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: VLOOKUP and manual data entry

    Oh my goodness! that is perfect! thankyou so much I never would have thought of that, but now you've shown me it seems so obvious XD

    You sir are a genius! Who explained the whole lot to me just the right amount *hugs*

  8. #8
    Forum Contributor Steve N.'s Avatar
    Join Date
    12-22-2011
    Location
    USA
    MS-Off Ver
    Excel 2007, 2010
    Posts
    298

    Re: VLOOKUP and manual data entry

    Very happy to have been of help. First hug I ever got for a spreadsheet - thanks!

  9. #9
    Registered User
    Join Date
    11-26-2012
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: VLOOKUP and manual data entry

    Eep, sorry :3

    I've been trying to figure it out, but I just cant see how you've done it. I need to create 14 more tables that correspond to the appropriately numbered tab, along with that I also need to do the VLOOKUP formula's & drop down list... any chance you'd be able to explain that? or have I just turned a simple solution into a giant headache?

  10. #10
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: VLOOKUP and manual data entry

    Hi

    Has i said in post #2 You need to update your tables etc.
    See your file attached, data validation on sheet 140 highlighted and instructions etc on sheet - Contractors

    Kevin
    Attached Files Attached Files

  11. #11
    Forum Contributor Steve N.'s Avatar
    Join Date
    12-22-2011
    Location
    USA
    MS-Off Ver
    Excel 2007, 2010
    Posts
    298

    Re: VLOOKUP and manual data entry

    Do you need to replicate Tab 140 and tie it to its own table? Please give more detail.

  12. #12
    Registered User
    Join Date
    11-26-2012
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: VLOOKUP and manual data entry

    to a degree, each "numbered" tab will require its own table to be linked to it.

    Each numbered tab represents a different stage in the building process, which is why each one needs its own table to create a drop down menu of appropriate contractors for each stage.

    I'm just not able to figure out how you created the table, let alone linked to a VLOOKUP formula...

    I've attached the complete work book

    PO's Completing.xlsx

  13. #13
    Forum Contributor Steve N.'s Avatar
    Join Date
    12-22-2011
    Location
    USA
    MS-Off Ver
    Excel 2007, 2010
    Posts
    298

    Re: VLOOKUP and manual data entry

    So, a different list of suppliers in each new table?

  14. #14
    Registered User
    Join Date
    11-26-2012
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: VLOOKUP and manual data entry

    yeah and each tab needs a table

  15. #15
    Forum Contributor Steve N.'s Avatar
    Join Date
    12-22-2011
    Location
    USA
    MS-Off Ver
    Excel 2007, 2010
    Posts
    298

    Re: VLOOKUP and manual data entry

    Can the tables all be on the same Contractors tab?

  16. #16
    Registered User
    Join Date
    11-26-2012
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    11

    Re: VLOOKUP and manual data entry

    Yeah I think that would be the best way to go about it, each table would need to be named to the corresponding tab though, so that other users could identify which table to update

  17. #17
    Forum Contributor Steve N.'s Avatar
    Join Date
    12-22-2011
    Location
    USA
    MS-Off Ver
    Excel 2007, 2010
    Posts
    298

    Re: VLOOKUP and manual data entry

    The entire process gets a bit involved. Not sure this is the best way, but heres what I know - you can create a copy of tab 140 and rename it to oh, say, 310. Then you can 'copy' the existing Contractors140_tbl on the Contractors tab and paste it where you want it - even on the same sheet.

    Then rename the table to something like "Contractors310_tbl" (no quotes). Once that is done, go to tab 310 and replace the Contractors140_tbl with Contractors310_tbl in each formula. Actually, just change the tab number in each formula.

    You'll also need to create a dropdown list referencing the list of Suppliers in the new table.

    It might be helpful if you take a look at a better description of working with tables as you go - Easy stuff but it would take me a lot of typing to explain it.

    Have a look at this

    http://office.microsoft.com/en-us/ex...spx#BMcreating

    Hope this helps. Hit me back if you get stuck...

  18. #18
    Forum Expert Kevin UK's Avatar
    Join Date
    12-07-2010
    Location
    Radstock, Somerset
    MS-Off Ver
    365
    Posts
    1,922

    Re: VLOOKUP and manual data entry

    @ Steve N
    Not that I know of. But I've attached a possible solution. I changed your Contractors data range to a Table named Contractors_Tbl (It's in the Name Manager). I also created named ranges for each colummn in that table (also located in the Names Manager).
    Why put a table in when all you had to do is make the range (Contractors) dynamic including the range (Suppliers) for the data validation! Plus whats the reason for creating named ranges for all the columns. You are passing the Contractors range to the VLOOKUP for the table array!

    @ Miss.Rubixcube

    What do you mean by a separate table for each tab!! Why can't the data be on the Contractors Tab, the only thing that needs to change is what suppliers need to be entered for each tab in the data validation list, in cell B9.
    Also I would stay clear of merging cells.

  19. #19
    Forum Contributor Steve N.'s Avatar
    Join Date
    12-22-2011
    Location
    USA
    MS-Off Ver
    Excel 2007, 2010
    Posts
    298

    Re: VLOOKUP and manual data entry

    @ Kevin

    Tables - Well, personal preference depending on the situation - I guess. Tables are easy to manage and allow for the automatic resizing, like a dynamic range, when data is added or deleted. The added benefit of a table, especially in a wb shared by multiple users, is that it is self contained. You can lay data into cells a couple of rows below a table and not worry about where the dynamic range ends. So many ways to do things, it's really based on local needs...for me.
    I work with a lot of people who spend very little time in excel - I tend to lean on some of excel's resident functions, like tables, to keep things simple. Formulas scare some folks...

    Named ranges for all the columns - bad habit of mine. I deal mostly in number data and often create named ranges for formula use. Then I go through the name manager and clean-up what I didn't use. I gotta watch that - it does confuse...thanks for pointing that out.

    @ Miss.Rubi

    Kevin makes a good point. You could have a single dropdown list of all Suppliers on the Contractors tab. Then you don't need to go through all the formula changes and creating separate validation lists.

    You can simply create a copy of tab 140 and rename it (sample tab 310 created for you). Then add the new Suppliers to the existing table on the Contractors tab and you're on your way...
    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)

Tags for this Thread

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