#value! error no matter what iteration of formula I use

[Justrelax]

Hi all,

New to the forum and trying to figure out what I perceived to be a simple problem...At first. We have a excel template that was given to us by a state agency to use to invoice services provided. Every service has a id# associated. A row of the template would include date, client name, provider name, id#, rate for particular id#, etc...I created a second worksheet that is identical to the template to calculate the wage we pay a provider instead of using the rate we would receive. It references the template and copies info into cells but uses vlookup to find our rate for service (utilizes reference table) instead of using there rates. I set the worksheet to reference the sheet and if a referenced cell is blank, it produces a blank on the second worksheet. The only math calc used in the worksheet is a product formula to multiply our rate times the amount of hour/units. The problem is that if one of the cells is blank(that is used in multiplication), it produces the #value! error. I think it is important to know that the cells that are used to multiply have formulas that reference the template page. No numbers are entered into the second worksheet. I have scoured forums, help articles, and tried everything i know how to do. I have made multiple attempts to re-write the formula to produce a blank cell or even a 0. I am hoping that someone can make a suggestion or point me in the right direction. All help is welcomed!!

[Jonmo1]

The problem is that formulas returning "" are NOT blank. That is actually a TEXT String
And TEXT*NUMBER = #Value!

Can you post the actual formula in question, that provides the desired results when the cell(s) are NOT blank "" ?
And what would you like to happen instead of #Value! error when one of the cells is blank ""

You might just wrap your whole formula in IFERROR
=IFERROR(yourformula,"")

[Justrelax]

Thanks for responding so quickly JONMO1! My formula for units/hours is =VALUE('ASO Invoice'!H13). It was ='ASO Invoice'!H13 & "" to produce blank when referenced cell was blank. I changed because I was suspicious of text and tried to change. My wage lookup formula is =IF(ISNA(VLOOKUP(TEXT(G13, "0"),'Reference Sheet'!$B$3:$C$18,2,0)),"",VLOOKUP(TEXT(G13, "0"),'Reference Sheet'!$B$3:$C$18,2,0)) to produce a blank if the referenced cell is blank. There are many variations on my mult formula that I have tried but the one I have in the last column now is =H13*I13 & "". I have tried the IF function and the ISBLANK function with no success. I even just tried what you suggested =IFERROR(H13*I13 & "") and received this error - This field is a calculation and should not have amounts entered directly into it. Let me know what you think and forgive my ignorance!

[Jonmo1]

I would drop the idea of doing number & "" for the purpose of hiding 0's.
That just turns your numbers into Text Strings.
There are far easier and more effective ways to hide zeros
Go to File - Options - Advanced and UNcheck "Show a zero in cells with zero value"

Now try
=IFERROR(H13*I13,"")

[Justrelax]

It worked!! I turned off data validation in the last column and it allowed me to enter your suggestion. Thanks so much for taking the time to help and with such quickness. I am not completely new to excel but I have a long way to go. I appreciate it and will try to learn from this experience so that I may be able to help someone else with the same problem. Thanks again!

[Jonmo1]

You're welcome.