Forum Discussion
Creating a Form
ineedhelp , okay, when in Summary tab
stay on C2, on ribbon Data->Data validation, select list and add names of your tabs as here
To count number of weekdays for the selected sheet in C3 formula
=COUNTIF(INDIRECT("'" & $C$2 & "'!B:B"), $B3)and drag it down.
To return value of the cell S77 in selected sheet in D3
=INDEX(INDIRECT("'" & $C$2 & "'!S:S"),76+ROW()-ROW($B$2))and drag it down.
Sample is attached.
https://support.office.com/en-us/article/introduction-to-microsoft-power-query-for-excel-6e92e2f4-2079-4e1f-bad5-89f6269cd605is the right tool fo such applications to consolidate, transform, summarize data from multiple places. have you explored this yet ?
once you have teh data consolidated , then you can use Pivot /Charts , which by default provide you dropdown to pick the options to present a specific tab or any other variable.
While there may be other methods, given the amout of data you seem to have and need to transform, it is best to use power query.
If you provide a sample data file , you will get a better and specific solution in this forum.
I have not explored a Power Query. I have never even tried setting up a power query / pivot table to explore those options.
Here is some sample data. Basically, on the Summary tab I would need to be able to select Samples 1-3, since they are each different months, and then be able to put in a number in C3:C9 (3-5) and have a hidden formula off to the right hand side somewhere (maybe in Column E or something), that will know to go to the sheet selected in the drop down and multiply the number on the summary tab by S77:S83
Just to clarify - to put C3:C9 you mean exactly these numbers
and nothing more, you don't need other weeks? And what is (3-5)?
SergeiBaklan No, I mean C3:C9 on the summary tab with 3-5 being the number of days to input on the summary tab next to number of sundays, mondays, etc.
