changing sheet name in the formula on the change of dropdown

[abdulncr]

Hi,


I have below formula in E9, which is working fine.


=SUMPRODUCT((george!B9:B28>=Report!H4)*('george'!B9:B28<=Report!K4)*('george'!H9:AE28="N"))

i wanted to change sheet name in the formula automatically when i select sheet name in E4.


Thanks


Abdul

 

[shrivallabha]

You will have to use INDIRECT like:

=SUMPRODUCT((INDIRECT(E4&"!B9:B28")>=Report!H4)*((INDIRECT(E4&"!B9:B28")<=Report!K4)*((INDIRECT(E4&"!H9:AE28")="N"))


PS : There could be a typo in above formula as I've not tested it. However, it should be enough to give you an idea.

 

[abdulncr]

Hi shirvalla,


Great thanks


thanks


Abdul