Change data source of a sheet by a dropdown (data validation)

[F_WT]

Hello,

I have a workbook containing sheets A, B, C, ... as well as an evaluation sheet.
A, B, C are congruently set-up.

On the evaluation sheet, I'd like to have a data validation drop down fulfilling two requirements:

1. Contains "A", "B", "C", ... (and more, if sheets are added) as content to choose
2. If e.g. "B", is chosen, all formulas of the evaluation sheet shall adapt accordingly: =SUM(B!B3:B7) (choosing "A" leads to "=SUM(A!B3:B7)"

As the INDIRECT functions decrease the speed of the file, a VBA solution is preferred.

Can anybody help me?

Thank you very much for every hint

Florian

[PCI]

See next code and file attached

Please Login or Register  to view this content.

[F_WT]

Wow!
Great!

How to modify the code if only formulas in the range A50:ZZ200 shall be dependent on the dropdown?

Possible?

Thank you so much

Regards

Florian

[PCI]

See new code
Notice where the range address is recoded

Please Login or Register  to view this content.

for the next adjstment ...!

Please Login or Register  to view this content.

[F_WT]

Dear PCI,

thanks a lot for the updated script.

I am currently working with the script you posted on 12-25-2017, 11:06 PM

I just noted the following:

- the formulas do change as long as I choose A or B as data source, but not the one in C10
- as soon as I choose C as data source, the formula change correctly, but afterwards, when I choose A or B again, the formulas are not updated

What might be wrong here?

And an additional question for interest (as I have very less VBA knowledge): what is the line "Set WSDic = CreateObject("Scripting.Dictionary")" for and where can this object be found?

Thank you so much in advance for your help and clarification

Regards

Florian

[PCI]

Not sure to get it right:
"not the one in C10" but C10 is not the working range: "A50:ZZ200"
Can you make test with the file attached and comment.
Concerning CreateObject("Scripting.Dictionary")
It is in fact a collection used to make the list of the sheets and permit to reuse this list easely: an Arraylist could be used

[F_WT]

Dear PCI,

thanks again for your reply.
I adapted the working range from A6 to ZZ200 and tried it again.
It still only works limited as described in the thread before:
1) choosing "A" and click update: values in C6/C7/C8/C9 do change, value in C10 does not change
2) then choosing "B" and click update: values in C6/C7/C8/C9 do change, value in C10 does not change
3) then choosing "C" and click update: values in C6/C7/C8/C9 do change, value in C10 remains 1500 (which is correct)
4) then choosing "A" and click update: no change of any value

Looking to the formula in C6 after step 1: =SUM(A!B3:B7)
Looking to the formula in C6 after step 2: =SUM(B!B3:B7)
Looking to the formula in C6 after step 3: =SUM('C'!B3:B7)
Looking to the formula in C6 after step 4: =SUM('C'!B3:B7)

=>Are the "superscripted commas" in 'C' the problem?

Florian

[PCI]

"Are the "superscripted commas" in 'C' the problem?" YES certainly
Can you send a sample of your file ??

[F_WT]

Dear PCI,

please find attached my file

Regards

Florian

[PCI]

OK clear, I forgot how Excel could work with sheet's name in formula
Try next code

Please Login or Register  to view this content.

[F_WT]

Dear PCI,

thank you so much for the code modification.

It seems to work now.

May I ask you two additional things:

- how to handle sheet names other than A, B, or C, which might be more than one word (e.g. "San Marino")?
- how to handle a non-continuous range (e.g. A6:G200 and K6:ZZ200 instead of A6:ZZ200)?

Heavily more work for you?

Regards

Florian

[PCI]

See here update

Please Login or Register  to view this content.

[F_WT]

Dear PSI,

just woke up and immediately checked my mailbox and your updated listing:

IT WORKS PERFECTLY!

THANK YOU VERY MUCH!

Regards

Florian

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.