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.
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.
So, let select some tab. When in C3 of summary tab it'll be number of Sundays from column A (alternatively from Column B, doesn't matter) of selected tab - lot of them. In D3 shall appear magic 3, 4 or 5 - from where they shall be taken?
SergeiBaklan No. Say tab 1 is January. In C3 of the Summary tab, I need to be able to put in a number between 3 and 5 (i.e. there were 4 Sundays in January) and have a hidden formula in column D to go to S77 on tab 1 (January) and multiple it by the 4 Sundays to see the total revenue expected on Sundays in January.
There also needs to be a drop down list on the summary tab for when I need to do the same for February, March, etc. and have it so that the hidden formula in column D of the summary tab automatically adjusts (i.e. knows to go to January, February, etc. depending on what's been selected on the drop down) so that if I want to say there were only 3 Sundays in February, I just select February from the drop down, put 3 into C3, and have the hidden formula multiply 3 by S77 of the February tab (tab 2)