Data Validation - replace choices for multiple sheets of source data

**mshaw1970** · 06-13-2013, 06:30 AM

Hi,

Brand new to both the forum and using macro's in general.

I am trying to create a menu sheet from various data sheets where a drop down DV list shows the name of the item chosen under the heading but populates a price when selected.

I have used the following post with great success for 1 drop down [URL="//madrocketscientist.com/jerrybeaucaires-excelassistant/data-validation/replace-choice"]

I would now like to apply this to other sheets in the same work book to create the same function from different DV lists.

All sheets are set up in an identical manner, my problem seems to be getting the macro to read more than 1 sheet.

I have tried without success adding an array of the sheet names, although my gut feeling is this is where I need to make changes.

Apologies if this is simplistic or covered elsewhere - as stated these are my baby steps into the world of excel.

now attached the file for clarity. Basically I have a macro running the result of which can be seen in cell B3 on the SUMMARY sheet. Data is pulled from Venues sheet. I would also like the same to happen when I select from the drop down in B5, pulling data from the Facilitator sheet. I hope this helps and thank you in advance

Mark

**jaslake** · 06-19-2013, 08:47 PM

Hi mshaw1970

Welcome to the Forum!

I'm a bit unclear...try this

Please Login or Register  to view this content.

**mshaw1970** · 06-20-2013, 03:07 AM

Hi John,

Thanks so much for this, it worked perfectly.

Would I be correct in thinking that if i wanted to add another sheet I would just need to alter the target adress and the sheet name into the text below and paste it into the code before end sub?

If Target.Address = "$B$5" Then
Application.EnableEvents = False
Set cFIND = Sheets("Facilitator").Range("A:A").Find(Target.Value, LookIn:=xlValues, LookAt:=xlWhole)
Target.Value = cFIND.Offset(, 1).Value
Application.EnableEvents = True
End If

I realise this is fairly basic stuff but it has whetted my appetite as to what can be acheived - an excel convert in the making maybe!! Any suggestions on entry level resources to learn more about coding would be greatfully received.

Thanks again

Mark

**jaslake** · 06-20-2013, 07:34 AM

Hi Mark

Yes, you would be correct.

You're welcome...glad I could help. Thanks for the Rep.

If this resolves your issue please mark your Thread as SOLVED.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save