#### james rockford

##### Active Member
hello to all...well i have a work book and within the work book i have 12 week sheets (listed by months jan to dec ) and one sum sheet....the issue is on the sum sheet ...as i add info to the main work sheets by month i must add the formula to the sum sheet monthly and i would like to add a formula to the sum sheet but when i do i get the divid in the cel....
I.E.work sheet jan cel d7 is where the info sum up to and then i add this info to the sum sheet or =(jan!d7) to copy info over....and would like to do this on all the months by using the if d7=0,"", jan!d7 but does not work

can someone advise the correct formula ...hope this is clear and thanks

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
You will need quotation marks

try using

=('jan'!D7)

hi jamie ....this is what the formula looks like =if(jan!d7=0,'',)sum(jan!d7)

what i am looking to do is if work sheet jan d7 is 0 then at 0 to the work sheet sum....but does not work

also work sheet jan d7 has a formula the sum up from d1 to d6 FYI.....

Hello Jim,
I'm not sure I follow. Is it that your formula is on the summary sheet and you want it so
if jan D7 is blank, return a blank or return the value in jan D7?

If that's right then this should do it.
=IF(jan!D7="","",jan!D7)

[EDIT:]
After seeing the last two posts (which I hadn't seen when I posted ) it sounds like
all you need is =jan!D7
If that's returning a zero on the jan sheet then it'll return a zero on the summary sheet as well.
(Is that not what you're looking to do?)

hi halface....sorry as this is not clear.....think of this and one sheet the sum up to a cell or d1 to d6 and then add info to d7 then copy over to sum sheet ...i have added your info but get divid and i would like 0 or nothing in cell ..or only when valve is add to sheet

hope this helps and thank for your help

I'm still not sure I'm getting it. Probably not enough coffee yet this morning.
In my jan sheet, D7 I have the formula =Sum(D1:D6)
In another sheet I put =If(jan!D7=0,"",jan!D7)
Now if jan!D7 =0 the formula returns a blank, otherwise it returns the sum of D1:D6.

Am I on the right track?

You could just hide all 0 values

tools|options|view(tab)|uncheck zero values box

would that do?

hi halface ...you are correct that is what i have set up....

hi jamie and halface...well but using your help and have correct my issue...the problem was in the cel d7 work sheet JAN....because this cel was summing up info and because they was no valves in the cel it was add divid to the cell and the formula you ask me to use was correct...it was copying the info over.....wow work great now thanks.......

Replies
6
Views
208
Replies
11
Views
335
Replies
5
Views
305
Replies
8
Views
312
Replies
4
Views
637

1,219,917
Messages
6,150,946
Members
450,996
Latest member
darko1515s

### 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.

### Which adblocker are you using?

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

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