Data dependent drop down list in VBA

Hi guys,

I'm trying to create, via VBA, a macro to create a new order with:

A) a drop down list for Account Ref (AC Ref), starting in Cell E3
B) creating a dependant drop down list in F3 (Using the formula as the source "=OFFSET(AnchorCell, 1, MATCH($e3, Customers, 0)-1, COUNTA(OFFSET(AnchorCell, , MATCH($e3,Customers, 0)-1, 50, 1))-1, 1)". (I have placed the formula above in cell F10 for display purposes only but will obviously delete this when a solution is found)

The problem I have is that when creating the next line entry, via clicking the "New Entry" button, the formula still looks at $e3, and I need it to move to E4. Obviously the next new entry would move to E5 and so on


Regards

Don't let your access to VBA make things harder than they need to. With your current approach, any changes to the design of your sheet would require rewrite of all your code, too. Ugh.

Better.... use row3 of your sheet as a template. Basically, as long as row3 is setup and working the way you want, then this simple "copy" macro will copy that to the next empty row down below and remove any constants. Any formulas will adjust themselves, including properly constructed data validation.

Please Login or Register  to view this content.