Value error in one IF formula but not another

[Fyresparxx]

I have a workbook that has two sheets. Sheet one is in color and there are six timecards on it that all fit on one screen. Sheet 2 has those same six time cards, organized vertically instead of horizontally, and all the color formatting has been removed for printing. The idea is that everyone fills their card out on page one, and the sheet can be printed from page two. Printing page 1 with fit to page makes it too difficult to read. Each card is identical in every way, using copy/paste and format painter. Page 2 uses a bunch of IF formulas to transfer data from page 1 to two, leaving blank blocks blank.

My problem is that on page two, I'm using the following formula to transfer a "notes" block for employee A.

=IF('Enter Timecards'!C14:J15=0,"",'Enter Timecards'!C14:J15)

And I'm using this one for employee B.

=IF('Enter Timecards'!Y14:AF15=0,"",'Enter Timecards'!Y14:AF15)


Employee A works as intended. Employee B gives me a Value error. I don't understand why this is happening.

 

[Gerald Higgins]

What data do you have in C14:J15 and Y14:AF15 ?

Also, I can't get these formulas to display any value other than a #VALUE error, UNLESS I enter them as array formulas.
Is that what you are doing ? Has one of them been entered as an array, and one not ?

 

[Fyresparxx]

If there is data entered in either block, it will be text. Most of the time the block is blank. Ive tried both blocks empty, and both with text, ending in the same result.

The only way I could have entered either as an array formula would have been by accident, and I don't know how to check. I didn't know such a thing existed. Will this fix my problem?

 

[Fyresparxx]

Trying this array formula thing, I get told that it isn't valid in merged cells.

 

[Jonmo1]

You should be using Single Cell references like

=IF('Enter Timecards'!C14=0,"",'Enter Timecards'!C14)

 

[ww4612]

You should be using Single Cell references like

=IF('Enter Timecards'!C14=0,"",'Enter Timecards'!C14)

yes. C14:J15=0 is not vaild statement. you must judge cell value one by one, not together

 

[Fyresparxx]

That change worked, and I feel dumb for not realizing it. I was clicking the cell I wanted and it was auto entering it. Since It worked for the first two blocks, I assumed the formula was right, and couldn't figure out why it wouldn't work for the next ones. Though, this fix still doesn't explain why it worked (wrong) in the first place.

 

[Jonmo1]

Excel tries it's best to 'figure out' what it is you're trying to do.
So if you entered the formula
=IF('Enter Timecards'!C14:J15=0,"",'Enter Timecards'!C14:J15)
on a sheet other than 'Enter Timecards"
And somewhwere within C14:J15 on this other sheet
Then excel returns the value 'relative' to the cell you entered it
So if the formula is entered in E15, then it references E15 on the Enter Timecards sheet.
But if you enter it somewhere outside of C14:J15, say A2, then it can't even make a best guess about which value you wanted, so it returns #Value!