MrExcel Publishing
Your One Stop for Excel Tips & Solutions

If Statement help


Posted by Matthew Schmitt on April 18, 2001 1:28 PM

I need to make an IF statement, or maybe a macro that will look at the contents of cell F2. If F2 is 0 then F2 = C2 and delete the contents of cell A2


Posted by Matthew Schnmitt on April 18, 2001 1:53 PM

If F2 is 0 and cell directly below F2 = C2 then F2 = C2 and delete the contents of cell A2.

If F2 is 0 and cell directly below F2 <> C2 and <> = 0 then F2=Cell directly below F2 and delete A2.

If cell directly below F2 = 0 then move to cell directly below that. Continue moving down that column until a cell that <> 0 is found.

Posted by Mary Ann on April 18, 2001 2:23 PM

How can I add cells across worksheets?

I'm trying to add totals on three worksheets. For example I need to add cells D4 on sheets 1-3 for a grand total on sheet 3 cell D10. How do I write that formula?

Posted by Mark W. on April 18, 2001 2:25 PM

Re: How can I add cells across worksheets?

=SUM(Sheet1:Sheet3!D4)

Posted by Gary_41701 on April 18, 2001 9:55 PM

Re: How can I add cells across worksheets?

That's a great response, however there's risk for
a error if you add any more tabs. Let's say you
add a new tab "Sheet10" --just something to play
with (ie, junk) -- if you move Sheet10 between
sheet1 & sheet3, the formula below would also
include in the sum - any numbers in Sheet10!D4.
I hate to say it, but I think it's safer, if the
formula was "=sum (Sheet1!D4,Sheet2!D4,Sheet3!D4)"

Any better suggestions are welcome.

Posted by Mark W. on April 19, 2001 7:23 AM

Re: How can I add cells across worksheets?

Gary, as with most things there are +/-'s
with any approach. For example,
=SUM(Sheet1!D4,Sheet2!D4,Sheet3!D4) is more
suceptible to sheet deletion than
=SUM(Sheet1:Sheet3!D4). I would also strongly
encourage that once a worksheet has been developed
and put into use that it's structure be protected
against worksheet insertions, rearrangement, and
deletion. Another, technique that can be used
to call attention to the 3-D range is add an
empty worksheet on either side of the sheets
included in the 3-D range and call attention to
these sheets with a tab label such as --> and
<--.

Posted by Gary_041901 on April 19, 2001 4:21 PM

Mark Thxs\ Like to know what you think of a couple of my ideas

Mark :
Thxs for the comment\advice - I stand corrected.
The only reason I suggest the sum method was that I
once came very close to blowing up a major reporting
package by making the same error noted in my
earlier posting.
Just a couple of things to chew on :

1>I agree that once a workbook has been put into
production, its structure should be protected -
assuming you don't use a password.
My manager would have my head if I did that

2>Unless the user constantly needs access to
"sheet!1:sheet!3", what's your opinion on
grouping and hiding them (Format\Sheet\Hide
or via xlveryhidden). A simple macro together
w\ a vbYesNo msg could be setup to
display\hide the sheets. Same for a print macro.

3>Just to make sure that I understood you correctly re:
using "sum" approach is "susceptible to
sheet deletion" I'm assuming that
your referring to the #REF!D4 error you get,
if you were to delete a sheet. I've come
this numerous times. On most occasions, I just:
a>Select the sheet before deleting it, then
b>Select all of the relevant rows and delete
them and,
c>Then delete the sheet. The error now reads
#REF!#REF!,
d>Which I can get rid of with a simple replace:
* Find what : #REF!#REF!,
* Replace with : [I leave blank]

Please let me know your opinion of the above -
Everyday I'm learning more and more. This site,
together with the your advice (and others)
are a great tool.

Thxs again

Posted by Mark W. on April 19, 2001 4:52 PM

Re: Mark Thxs\ Like to know what you think of a couple of my ideas

> ...I stand corrected

It wasn't my intent to "correct" anyone. I was
just providing some more considerations. I'm
really not against your recommendation.

> ...what's your opinion on grouping and hiding them

That's certainly one way to keep prying hands off
your worksheets! : )

> I'm assuming that your referring to the #REF!D4 error...

Yep! ...and nifty little Find/Replace strategy.

Posted by Gary_041901 on April 19, 2001 10:07 PM

Thxs again - Since Excel .............

Again thxs for the comments. It's always nice to hear
that one's ideas makes some form of sense. Hope I
didn't offend you w/ the comment "I stand corrected"
Just deferring to the experts. Have a great day &
look forward to reading your tips\advice.
Gary