Incrementing Lookup Table Cell by 1

[OzScout]

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

[Jacc]

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

Please Login or Register  to view this content.

[OzScout]

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