Format control data - values to be input differently on different tabs

Hi guys,

I have a spreadsheet I am using to track holidays on. I am learning to use format control and index match etc.

I need seperate tabs which you can see whereby i have setup 'April 2019' and 'May 2019' and a tab called holiday sheet.

Can anyone help me with inputting the dates into the holiday tab whereby, if i inset date - to date in April (after selecting April using the format control box on the holiday tab) the figure for April is populated on the holidays column in the April 2019 tab

Similarly if I insert another range of dates of holiday on the holiday sheet tab for the month of may, the number of days off is inserted into the may 2019 tab against john smith. Sheet attached!

Thank you guys!

Hi Jose

Sadly this is not what i required

I have the dates already, I just need the number of days for each month that john smith is on holiday to be reflected on the relevant month's tab. Thanks for sending this though

Originally Posted by José Augusto

Hi

Try to see if this video helps you.
https://www.youtube.com/watch?v=5pdcLTwkn5s

Hi Jose

Sadly this is not what i required

I have the dates already, I just need the number of days for each month that john smith is on holiday to be reflected on the relevant month's tab. Thanks for sending this though

Originally Posted by José Augusto

Hi

Your sheets April and May has different layout. To avoid different formulas you need use only one of them.
Adjust the range in holidays sheet as you need.

In 'April 2019' Use in B2 (adjust range and drag down)

Formula:

=SUMPRODUCT(('Holiday Sheet'!$A$3:$A$100=A2)*(MONTH('Holiday Sheet'!$B$3:$B$100=4))*'Holiday Sheet'!$D$3:$D$100)

In 'May 2019' sheet use in C2 (adjust range and drag down)

Formula:

=SUMPRODUCT(('Holiday Sheet'!$A$3:$A$100=B2)*(MONTH('Holiday Sheet'!$B$3:$B$100=5))*'Holiday Sheet'!$D$3:$D$100)

Hi Jose,

Thank you this.

However i think the problem is linked with the date ranges. If i input two dates, and change the month from april to may or vice-versa on the holiday sheet tab, the dates are not changing? therefore it looks like the holiday numbers are staying the same on the april and may 2019 tab!

any suggestions please?

Try the following modifications
For April: =SUMPRODUCT(('Holiday Sheet'!$A$3:$A$100=A2)*(MONTH('Holiday Sheet'!$B$3:$B$100)=4)*'Holiday Sheet'!$D$3:$D$100)
For May: =SUMPRODUCT(('Holiday Sheet'!$A$3:$A$100=B2)*(MONTH('Holiday Sheet'!$B$3:$B$100)=5)*'Holiday Sheet'!$D$3:$D$100)
Let us know if you have any questions.

Originally Posted by JeteMc

Try the following modifications
For April: =SUMPRODUCT(('Holiday Sheet'!$A$3:$A$100=A2)*(MONTH('Holiday Sheet'!$B$3:$B$100)=4)*'Holiday Sheet'!$D$3:$D$100)
For May: =SUMPRODUCT(('Holiday Sheet'!$A$3:$A$100=B2)*(MONTH('Holiday Sheet'!$B$3:$B$100)=5)*'Holiday Sheet'!$D$3:$D$100)
Let us know if you have any questions.

Hi Jetemc

Thank you for your reply. The dates don't change on the holiday sheet tab therefore the number of days remain the same and subsequently this is still showing for april and for may. Can you please advise? thank you

As seen in the attached copy of the file the formulas display 2 in April 2019!B2 and 0 in May 2019!C2.
Let us know if you have any questions.

Hi JeteMc

This seems to work partially!

So it is calculating April and May dates

However, I want to achieve if i select April from the dropdown on the holiday sheet, i should have april dates inserted - which feeds into the april tab (number of days off)

If i select the May from the dropdown on the holiday sheet, I should have dates for may inserted - which feeds into the may tab (number of days off)

Please advise. Thank you!!

It sounds as if the dates April 11, 2017 and April 13, 2017 should change to May 11, 2017 and May 13, 2017 when May is selected in the control form on the Holiday sheet. If that is the case the formulas for B3 and C3 respectively are:
B3: =DATE(2017,$J2,11)
C3: =DATE(2017,$J2,13)
Let us know if you have any questions.

Originally Posted by JeteMc

It sounds as if the dates April 11, 2017 and April 13, 2017 should change to May 11, 2017 and May 13, 2017 when May is selected in the control form on the Holiday sheet. If that is the case the formulas for B3 and C3 respectively are:
B3: =DATE(2017,$J2,11)
C3: =DATE(2017,$J2,13)
Let us know if you have any questions.

Hi JeteMc,

Unfortunately not. I will explain my request again..

On the format control, on the holiday sheet tab, if i select april, I want to input date for april ONLY: i.e. 01/04/2019 - 10/04/2019. This is 9 days in total for april, which should be calculated on the April 2019 tab

If I select May from the format control on the holiday sheet tab, I want to input a date for May only i.e. 01/05-2019 - 06/05/2019. This is 5 days intotal for may, which shou;d be claculated on the may 2019 tab.

Hope this makes sense! thank you!!

Sounds as if you want all of your input to go into cells B3:C3 on the Holiday sheet and have to output (D3) sent to the appropriate month based on the format control value. Unfortunately, if my understanding is correct, I don't believe your goal can be accomplished using formulas. Although I am not knowledgeable of VBA I am doubtful of that also.
My suggestion is that you fill down columns B:D on the Holiday sheet and use the formulas from post #7 as modeled on attachment.

Originally Posted by JeteMc

Sounds as if you want all of your input to go into cells B3:C3 on the Holiday sheet and have to output (D3) sent to the appropriate month based on the format control value. Unfortunately, if my understanding is correct, I don't believe your goal can be accomplished using formulas. Although I am not knowledgeable of VBA I am doubtful of that also.
My suggestion is that you fill down columns B:D on the Holiday sheet and use the formulas from post #7 as modeled on attachment.

Hi JeteMC,

Thank you for this.

Do you think if I accept your solution, you can help me achieve this.

If I input dates for john smith on the holiday sheet tab, i.e. 01/04/2019 - 06/04-2019 = 5 days, can this be input onto the april tab? similarly ont he holiday sheet tab if i write john smith 01/05/2019 - 07-05/2019 = 6 days, this should be input onto the may tab? i don't want to list all the vetters ion all tabs as there are too many. hope you can help

Yes, the array entered formula* that populates the 'Name' column is:

Formula:

=IFERROR(INDEX('Holiday Sheet'!$A$3:$A$16, MATCH(0, IF(MONTH('Holiday Sheet'!$B$3:$B$16)<>4, 1, COUNTIF(A$1:A1,'Holiday Sheet'!$A$3:$A$16)), 0)),"")

The formula that populates the 'Holidays' column remains pretty much the same.
*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.

Originally Posted by JeteMc

Yes, the array entered formula* that populates the 'Name' column is:

Formula:

=IFERROR(INDEX('Holiday Sheet'!$A$3:$A$16, MATCH(0, IF(MONTH('Holiday Sheet'!$B$3:$B$16)<>4, 1, COUNTIF(A$1:A1,'Holiday Sheet'!$A$3:$A$16)), 0)),"")

The formula that populates the 'Holidays' column remains pretty much the same.
*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.

tHANK you JeteMC. This seems pretty good! thank you so much. rep added :-)

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.