Value error in one IF formula but not another

Fyresparxx

New Member
Joined
Mar 21, 2014
Messages
18
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.
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
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

New Member
Joined
Mar 21, 2014
Messages
18
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?
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

You should be using Single Cell references like

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

ww4612

Well-known Member
Joined
Apr 24, 2014
Messages
515
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

New Member
Joined
Mar 21, 2014
Messages
18
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,529
Messages
5,625,351
Members
416,096
Latest member
forevans

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top