# Excel formula question

hastelloy 12:52 09 Nov 07
Locked
Answered

I have a series of sheets, which follow on from one another. In the 1st sheet N39=N37-N38 and N40=N38-N37. Obviously 1 of these will always be positive and the other negative unless both = 0.

The question is, how do I make Sheet2 E9=Sheet1 N39 if N39 is positive but otherwise 0 and sheet 2 E10 = sheet 1 N40 if N40 is positive but otherwise 0.

Thanks for any help - I've been playing with this for hours and am either missing something very simple or its way over my head (probably the latter).

PalaeoBill 13:04 09 Nov 07

make Sheet2 E9=IF(sheet1!n39>0,sheet1!n39,0)
and
Sheet2 E10=IF(sheet1!n40>0,sheet1!n40,0)

Taff™ 13:06 09 Nov 07

Something Like =IF(Sheet1!N40>0,Sheet1!N40,"0") in Cell E10 and =IF(Sheet1!N39>0,Sheet1!N39,"0") in E9 I think.

PalaeoBill 13:07 09 Nov 07

You may also wish to investigate the absolute function ABS(). ABS(N37-N38) will give you the difference between the two values, stripping any negative sign.

silverous 17:18 09 Nov 07

you could also do =MAX(Sheet1!N40,0) I believe.

hastelloy 17:51 09 Nov 07

It seems that the problem isn't quite what I thought it was!! In asking my question, I called the sheets 1 and 2 for simplicity and when they are called this it works. Unfortunately, I need them to be "Week 59-53 (07)" for sheet 1 and "Week 01-04 (08)" for sheet 2 etc. If I use the original (ie sheet1, 2 etc) names for sheets it works fine and seems to carry on working if I rename the sheets. Unfortunately it doesn't seem to work if the sheets start off being named as I want them. I haven't had time to really check this out but will have another go on Monday

VoG II 17:55 09 Nov 07
Answer

You need to wrap the sheet names in single quotes like

'Week 59-53 (07'!N40

hastelloy 18:11 09 Nov 07

I'll give that a try on Monday.

Have a good weekend.

hastelloy 15:46 13 Nov 07

The formulae given above and Vog's single quotes solved the problems. Thanks again to all.

This thread is now locked and can not be replied to.

The Legend of Zelda Breath of the Wild review: Five hours with Zelda on the Nintendo Switch

1995-2015: How technology has changed the world in 20 years

How the painting-like animated sequences in A Monster Calls were created by Glassworks Barcelona

The 22 best Safari extensions | Best Safari plugins: Improve Apple's Safari web browser with these…