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.
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)
ineedhelp , thank you for the clarification. One more - in you sample file there is Jan 2018, Jan 2019 and Jan 2020 in one tab, which year to take? Or your actual file has another structure and in January tab you have only records for January 2019? Are there any other changes in actual file compare to your sample? At least it looks like we shall rename Sample 1 tab to January, Sample 2 tab to February, etc., and remove from each tab any other records. For which years?