Looking for some help with #VALUE!

[Ionic]

#VALUE! started appearing when I slightly changed my formula.

From:
=IF((TODAY()-F3)-(IF(G3=$A$2,$A$6,IF(G3=$A$3,$A$7,IF(G3=$A$4,$A$8,IF(G3=$A$5,$A$8,"")))))>1,"Must Call",(TODAY()-F3)-(IF(G3=$A$2,$A$6,IF(G3=$A$3,$A$7,IF(G3=$A$4,$A$8,IF(G3=$A$5,$A$8,""))))))

To:
=(IF(H2=$A$2,VLOOKUP(D2,$A$10:$B$16,2,FALSE)))+(IF((TODAY()-G2)-(IF(H2=$A$2,$A$6,IF(H2=$A$3,$A$7,IF(H2=$A$4,$A$8,IF(H2=$A$5,$A$8,"")))))>0,"Must Call",(TODAY()-G2)-(IF(H2=$A$2,$A$6,IF(H2=$A$3,$A$7,IF(H2=$A$4,$A$8,IF(H2=$A$5,$A$8,"")))))))

Specifically the "Must Call" in red on the new formula is now giving #VALUE! error but i am not sure why it works in the top formula and not the bottom. The only thing difference in formula is the green portion. Any help or direction you could provide would be great.

 

[sericom]

I'd guess it's trying to add non numeric data (the portion to the left of the '+' and/or to the right isn't numeric)

 

[jasonb75]

I'd guess it's trying to add non numeric data (the portion to the left of the '+' and/or to the right isn't numeric)

Good guess, looking at the formula, I'm seeing VLOOKUP()+"Must Call"

Ionic, it would be easier for us to help you correct the formula if you told us what you were trying to do.

 

[Ionic]

I am trying to create a report that allows me to see which clients I need to call based on client's Sales Prioritization. The kicker is Sales Prioritization A:Stars, is customized by need so some A clients I have to contact every 30 days and some A clients I have contact every 60 days. I would be happy to share the excel sheet I just know how I can do that in the forums. Below is an example.

[TABLE="width: 1528"]
<colgroup><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Count down[/TD]
[TD]Account Name[/TD]
[TD]Last Sales Activity Date[/TD]
[TD]Sales Prioritization[/TD]
[/TR]
[TR]
[TD]A: Stars[/TD]
[TD][/TD]
[TD="align: right"]-6[/TD]
[TD]Client 1[/TD]
[TD]1/30/2015[/TD]
[TD]A: Stars[/TD]
[/TR]
[TR]
[TD]B: Growth Potential[/TD]
[TD][/TD]
[TD="align: right"]-56[/TD]
[TD]Client 2[/TD]
[TD]2/19/2015[/TD]
[TD]A: Stars[/TD]
[/TR]
[TR]
[TD]C: Retention[/TD]
[TD][/TD]
[TD="align: right"]-56[/TD]
[TD]Client 3[/TD]
[TD]2/19/2015[/TD]
[TD]B: Growth Potential[/TD]
[/TR]
[TR]
[TD]D: Preservation/Other[/TD]
[TD][/TD]
[TD="align: center"]#VALUE![/TD]
[TD]Client 4[/TD]
[TD]9/16/2014[/TD]
[TD]C: Retention[/TD]
[/TR]
[TR]
[TD]30[/TD]
[TD][/TD]
[TD="align: right"]-27[/TD]
[TD]Client 5[/TD]
[TD]12/22/2014[/TD]
[TD]D: Preservation/Other[/TD]
[/TR]
[TR]
[TD]60[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]90[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A: Stars[/TD]
[TD]Days[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client 1[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client 2[/TD]
[TD]-30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

please let me know what else I can do to make this easier for you guys.

 

[jasonb75]

See if this works

=IFERROR(TEXT((TODAY()-G2)-(IF(H2=$A$2,$A$6-VLOOKUP(D2,$A$10:$B$13,2,0),IF(H2=$A$3,$A$7,IF(H2=$A$4,$A$8,IF(H2=$A$5,$A$8,""))))),";-#;#")+0,"Must Call")

Hopefully you're not using excel 2003 (or older), if you are IFERROR will not work so a different approach would be needed.

 

[Ionic]

jasonb75 you are a genius! and your formula is much more elegant than mine. Thank you so very much. It worked absolutely perfectly. If there is anything I can help you please just let me know.

 

[jasonb75]

you are a genius

I've been called much worse

A slightly more elegant solution, which requires a change to the way the tables in column A:B are arranged

A: Stars 30
B: Growth Potential 60
C: Retention 60
D: Preservation/Other 90



A: Stars Days
Client 1 30
Client 2 60


=IFERROR(1/(1/MIN(0,TODAY()-(G2+VLOOKUP(IF(H2=$A$2,D2,H2),$A$3:$B$13,2,0)))),"Must Call")

However, this does need all A:Stars clients to be listed individually in the table, if you need unlisted A:Stars clients to default to 30 days then you would need to compromise slightly with

=IFERROR(1/(1/MIN(0,TODAY()-(G2+IFERROR(VLOOKUP(D2,$A$10:$B$13,2,0),VLOOKUP(H2,$A$2:$B$5,2,0))))),"Must Call")

Hope this is of some use.

edit:

The table is not showing as 2 columns, it should follow the same format as your A:Stars table, name in column A, days in column B, but with the actual number of days instead of an offset, also the days in A6:A8 have been moved to B2:B5.

 

[Ionic]

Thank you very much. Very cool.