Indirect Address Column Formula help needed

[trankim]

The formula below looks for a date selected in a combo box, then goes to another worksheet and returns the column letters of that date. The dates in the other worksheet is a rolling 21 week date range.

My question: Is there a way to display a "N/A" in the cell if the latest week's data is not updated yet? Right now, if there's missing data during the week selected, the cell shows a "0"... I tried to do a IF statement where that cell equals "0" but it kept giving me a circular error and it never worked anyways.

Any help is greatly appreciated.

=INDIRECT(ADDRESS($A313,COLUMN(INDIRECT(""&$E$4&"! "&G$4&"$3:"&G$4&"$509")),1,TRUE,"DMI"))

[Admin]

Hi

May be..

=IF(ISERROR(1/INDIRECT(ADDRESS($A313,COLUMN(INDIRECT(""&$E$4&"! "&G$4&"$3:"&G$4&"$509")),1,TRUE,"DMI"))),NA(),INDI RECT(ADDRESS($A313,COLUMN(INDIRECT(""&$E$4&"! "&G$4&"$3:"&G$4&"$509")),1,TRUE,"DMI")))

or you could even custom format the cells

[=0]"#N/A";mm-dd-yyyy

[trankim]

BRILLIANT....it works! thanks so MUCH!

[trankim]

I tried to be creative and see if it worked using the same formula to calculate week over week. However, the above formula isn't working for me too well. If the reported week is not calculated, it technically shows a zero, which you then help me to show "n/a" instead. That works...however, when I have another column that looks at week over week change, when it sees the "n/a", it will calculate the week over week average to be at -100%.

Is there a way to fix the below formula to show that if the result is -100% or if the actual reported week shows "n/a", that the week over week column also shows "n/a"? Any advice is GREATLY appreciated!

=IF(ISERROR(1/(INDIRECT(ADDRESS($A260,COLUMN(INDIRECT(""&$E$4&"! "&F$4&"$3:"&F$4&"$509")),1,TRUE,"DMI"))/INDIRECT(ADDRESS($A260,COLUMN(INDIRECT(""&$E$4&"!" &J$4&"$3:"&J$4&"$509")),1,TRUE,"DMI"))-1)),"n/a",(INDIRECT(ADDRESS($A260,COLUMN(INDIRECT(""&$E$4 &"!"&F$4&"$3:"&F$4&"$509")),1,TRUE,"DMI"))/INDIRECT(ADDRESS($A260,COLUMN(INDIRECT(""&$E$4&"!" &J$4&"$3:"&J$4&"$509")),1,TRUE,"DMI"))-1))

[Admin]

Hi

Better to use helper column/row.

Put the original formula in the helper column/row. In the actual cells try something like

=if(or(helpercell="n/a",helpercell="-1"),"n/a",helpercell)

The original formula I meant was the first one.