Dynamic Range based on Financial Period?

[khlau]

Hi Guys,

Wondering if someone could help?

I am trying to create a formula string that will change dynamically depending on the financial period.

So for example, if it is in period 1, COUNTA(E3:E3), period 2 COUNTA(E3:F3) etc. all the way to Period 12 where range is E3:P3.

I need this to update for all rows and my string is:

=IF(D3="Bi-Monthly",IF(COUNTA(("E"&ROW()&":"&VLOOKUP($B$1,Formulae!A:C,3,0)&ROW()))=$B$1/2,"No Action","Action Required")) - Open to other ways of achieving the same outcome!

Currently, however when evaluated COUNTA returns 1.

I have attached the URL below:

Thanks for your time.

https://ufile.io/j0hy7

 

[Oscar Cronquist]

Currently, however when evaluated COUNTA returns 1.

Yes, COUNTA evaluates the string to 1 every time. You probably need to use the INDIRECT function to convert the string to a cell ref.

=IF(D3="Bi-Monthly",IF(COUNTA(INDIRECT(("E"&ROW()&":"&VLOOKUP($B$1,Formulae!A:C,3,0)&ROW())))=$B$1/2,"No Action","Action Required"))