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

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?