+ Reply to Thread
Results 1 to 3 of 3

Incrementing Lookup Table Cell by 1

  1. #1
    Registered User
    Join Date
    10-09-2012
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    2

    Incrementing Lookup Table Cell by 1

    G'day Folks,

    I am a newbie (Aussie from down under) to the forum so please be gentle.

    Ok, I have a worksheet (1) containing a 2 column table with the first column A: containing 100 x 4 digit Postcodes (A1:A100), and the second column B: (B1:B100) containing the delivery quantities.

    In another worksheet (2) I am using a single cell A1 as a data entry point so that when a user enters a Post code (and presses enter), it will automatically vlookup the Worksheet (1) A1:B100 and fetch the current quantity in column B relating to that particular postcode, and then increment it by 1 to update it.

    So, as the operator continues to enter postcodes into Worksheet(2) Cell A1, the quantities in Worksheet(1) Column B will continue to be incremented.

    Thanking you in advance for your help

    Cheers
    Grant

  2. #2
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,593

    Re: Incrementing Lookup Table Cell by 1

    There is such a thing as Dynamic Named Ranges but in this case I think that's a bit overkill.

    Just use the whole columns
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  3. #3
    Registered User
    Join Date
    10-09-2012
    Location
    Australia
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Incrementing Lookup Table Cell by 1

    Thanks Jacc for the speedy response, however I am looking for the VBA code to automate the updating of the data being looked up.

    In other words, when a user enters a single 4 digit postcode into Worksheet(2) Cell A1, the VBA code automatically detects the new data entered, then it looks up Worksheet(1) Table Range A1:B100 to match the Postcode entered (against the predefined set of postcodes), then whatever value is currently stored in Column B for that record, the VBA code increments that record by 1. So at the end of the data entry period I will see a data set containing 100 records with postcodes and their numerical quantities/volumes.

    Example Data Set for 5 records:

    A B
    1234 34
    1235 129
    1236 74
    1237 12
    1238 106

    Now when a new post code is entered for say Postcode 1236 into the other worksheet(2) Cell A1, the data set is automatically updated by the VBA code incrementing the value 74 to 75, and the data set now looks like this:

    A B
    1234 34
    1235 129
    1236 75
    1237 12
    1238 106

    Does this sort of make sense ?

    Thanks again for your help

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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