asked on

how to use worksheet event instead of formulas

Hello,

please see attached workbook.

currently, i have built this template and made it automatic with formula for example if you select any name in C2 all the other cells will be filled automatically.

i am looking for a VBA based solution where, the same operation is done without formulas in those cells. and they are done with worksheet change event. for example if i type any name or i select any name in the C2 then all of those cells populate as values and then if no match found then it should be left blank.

thanks for your usual help.
MAIN.xlsm

Rob Henson

Why do you need to use a VBA option?

VBA will have some advantages but not sure from your description above what you would gain.

Thanks
Rob H

Flora Edwards

ASKER

thanks Rob,

but i need VBA solution. that is what i need.

Martin Liss

I assume what you are doing now is copying a Product Name from the List sheet and pasting it into C2 on the Main sheet. If that is what you are doing your formulas are incorrect because you have multiple entries with the same Product Name but the data that get put on the Main sheet is always from the last of the duplicates. For example if you copy/pasted any one of the "Alice Mutton" values from List to Main, you always get "Conrad" as the sir name.

Please let me know if that's not what you do.

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Flora Edwards

ASKER

Thanks Sktneer

it worked.

Matrin and Rob thanks.

Martin,
yes, i had put just dummy data there, i understand it was duplicate. sorry for the causing confusion. Sktneer solution worked for me for now.

Subodh Tiwari (Neeraj)

You're welcome Flora! Glad I could help.

Rob Henson

Once you have the formulas in place, protect the sheet to ensure it doesn't get changed.

Then there is no need for VBA to change them.