Reflecting data based on month

[RodneyW]

I had similar post a couple days ago but the solution required using "Indirect" which won;t work because of the various ways the final doc is shared. I've pulled the data from the other document into the one I'll be sharing and here's how I need help now.

I’m setting up an analytic report for next year

H8 represents January
I8 represents February
J8 represents March
K8 represents April and so on through out the year.

My destination cell for the first quarter is W8. In the month of January, W8 will be empty. On Feb 1st, W8 should reflect the number in H8 (January). On March 1st, it should reflect the number in I8 (February) and on April 1st, it should reflect the number in J8 (March). It should remain this way forever.

I have a similar concept for cell Z8.May 1st, it should reflect K8 (April) and continue until July 1st, when it reflects M8 (June) and stays that way forever. Likewise for the remaining months of the year, but if I get Jan – March figured out, I can take it from there.

Thanks in advance.

 

[RodneyW]

In that case what is the formula in T8?

=SUMPRODUCT(--([Members2020.xlsx]Dec!$A$3:$A$200=$C$1),[Members2020.xlsx]Dec!$D$3:$D$200) ==== I have verified this is empty

For comparative purposes, here is the formula in Q8

=SUMPRODUCT(--([Members2020.xlsx]Sep!$A$3:$A$200=$C$1),[Members2020.xlsx]Sep!$D$3:$D$200)

 

[Fluff]

That formula cannot return "nothing" so I am very surprised that your formula in AF8 works as it shouldn't.
Try changing the formula to

=IF(T8<>0,T8,IF(S8<>"",S8,IF(R8<>"",R8)))

 

[RodneyW]

Formula in T8 is
=SUMPRODUCT(--([Members2020.xlsx]Dec!$A$3:$A$200=$C$1),[Members2020.xlsx]Dec!$D$3:$D$200)

for comparative purposes, this is the formula in Q8

=SUMPRODUCT(--([Members2020.xlsx]Sep!$A$3:$A$200=$C$1),[Members2020.xlsx]Sep!$D$3:$D$200)

 

[RodneyW]

That formula cannot return "nothing" so I am very surprised that your formula in AF8 works as it shouldn't.
Try changing the formula to

=IF(T8<>0,T8,IF(S8<>"",S8,IF(R8<>"",R8)))

Nope, that's not working I truly appreciate your help

 

[Fluff]

In what way?

 

[RodneyW]

When placing that formula in T8, AI8 remains empty

 

[Fluff]

That formula needs to go in AI8
Do you also have a formula in S8?

 

[RodneyW]

That formula needs to go in AI8
Do you also have a formula in S8?

Actually saying it was placed in T8 was a mistake. This formula =IF(T8<>0,T8,IF(S8<>"",S8,IF(R8<>"",R8))) it is in AI8

T8 formula is =SUMPRODUCT(--([Members2020.xlsx]Dec!$A$3:$A$200=$C$1),[Members2020.xlsx]Dec!$D$3:$D$200)
S8 formula is =SUMPRODUCT(--([Members2020.xlsx]Nov!$A$3:$A$200=$C$1),[Members2020.xlsx]Nov!$D$3:$D$200)
R8 formula is =SUMPRODUCT(--([Members2020.xlsx]Oct!$A$3:$A$200=$C$1),[Members2020.xlsx]Oct!$D$3:$D$200)

 

[Fluff]

In that case try

=IF(T8<>0,T8,IF(S8<>0,S8,IF(R8<>0,R8,"")))

 

[RodneyW]

In that case try

=IF(T8<>0,T8,IF(S8<>0,S8,IF(R8<>0,R8,"")))

YAHTZEE!!!! Thank you