tracking progress through multiple sheets

[sbr_ram]

hi guys, i need to write a function that tracks progress of a particular invoice through different sheets.
I have a main sheet that should show numerical values of 25, 50, 75, 100.
My function works with 2 sheets but if i add on more than that it fails
the formula for 2 sheets is
=IF(AND(ISBLANK(Quote!C2),ISBLANK(PO!C2)),"0",
IF(ISBLANK(PO!C2),"25","50"))
I have two more sheets that tracks to invoice and payment, would greatly appreciate any help you can give me

 

[jtakw]

Hi,

Since you say it's "tracking progress", can we assume that if the 1st Sheet C2 is Blank, the rest (2nd Sheet, 3rd Sheet, 4th Sheet) will be Blank ?
In other words, C2 of the 4 sheets will be populated in order of progress starting from 1st sheet to 4th sheet ?

EDIT: Also, when C2 is Blank, is it Truly Blank/Empty or may be result of a formula blank ( "" ) ?

 

[sbr_ram]

Hi,

Since you say it's "tracking progress", can we assume that if the 1st Sheet C2 is Blank, the rest (2nd Sheet, 3rd Sheet, 4th Sheet) will be Blank ?
In other words, C2 of the 4 sheets will be populated in order of progress starting from 1st sheet to 4th sheet ?

EDIT: Also, when C2 is Blank, is it Truly Blank/Empty or may be result of a formula blank ( "" ) ?

Yes, if the 1st sheet is blank the rest will also be blank. It follows the order of 1st sheet,2nd sheet and so on. and it truly blank/empty.

 

[jtakw]

Thanks for the clarification.

Either one of these formulas should work.

Change/adjust Cell reference and Sheet Names as needed:

=LOOKUP(COUNTA(Quote!C2,PO!C2,Invoice!C2,Payment!C2),{0,1,2,3,4},{0,25,50,75,100})

=IFERROR(CHOOSE(COUNTA(Quote!C2,PO!C2,Invoice!C2,Payment!C2),25,50,75,100),0)

BTW, don't put quote marks around the numbers (i.e. "25", "50", etc) if you want Real Number values, quote marks turn them into Text Values.

 

[sbr_ram]

Thank you so much,
without the quotes i can get data bars to work as well. was wondering why the data bars didnt work before. Appreciate the help

 

[jtakw]

You're welcome, welcome to the forum.