Hiding data in a range of cells

[Dobbin0_4]

I am seting up a spreadsheet that works like a bank statement, So from
ranges g 6 through g29 i have a balance figure. But i will be adding
transactions to this as i go along through the month. So in cell G7 i have
the formula:
=SUM(G8-F9+E9) and that is then dragged down so htat all the cells to g29
contain this formula.

However they all show the data in the cell above it i.e. cell g8 shows the
amount in g7 and so on. Is there a way to hide this data until i need to
fill the cell with new data?

I hope that you undertand the question its a bit drawn out, and maybe
confusing, if so i apologise.

Thanks in advance

 

[Gary''s Student]

Hide them with blanks:

Say we are entering numbers in column A & B
In column C we have:

=A1+B1 and copied down. For rows that have no values in A or B, column C
shows 0. If we want C to be blank if either A or B is blank, then:

=IF(OR((A1=""),(B1="")),"",A1+B1)

 

[Dobbin0_4]

Ok so I got the formula you gave me working. But there is a small problem i
am trying to use data from the cell above in my formula. so it looks like
this:

=c1-b2+a2

working with the formula you suggested is ok till i add "c1" to the formula
like the example below:

=IF(OR((A1="0"),(B1="0")),"",C1-A1+B1)

Because the data in cell c1 is copied down and i am back to where i started!


anyone else who can help with this. I would be grateful or if Gary Student
has any other ideas , again thanks

 

[Rich/rerat]

Dobbin0_4,
You can add macros and buttons to your sheet to hide/show all the data on
your sheet, until you need to edit it, such as:

Macro#1:

Sub HideRows()
'
Range("A6:G100").Select
Selection.EntireRow.Hidden = True
Range("A1").Select

End Sub

Macro#2:

Sub ShowRows()
'
Range("A6:G100").Select
Selection.EntireRow.Hidden = False
Range("A1").Select
End Sub

Or just hide the column:

Macro#3:

Sub HideColumn()

Columns("G:G").Select
Selection.EntireColumn.Hidden = True
Range("A1").Select
End Sub

Macro#4:

Sub ShowColumn()
'
Columns("G:G").Select
Selection.EntireColumn.Hidden = False
Range("A1").Select
End Sub

You need to adjust the Range information to fit you needs. I am new at
macros, so I had to create to of them. Some one more experienced with them
should be able to, show you how to combine the hide/show macro, so a single
button can be used, to either hide/show rows or columns.

--
Add MS to your News Reader: news://msnews.microsoft.com
Rich/rerat
(RRR News) <message rule>
<<Previous Text Snipped to Save Bandwidth When Appropriate>>


Ok so I got the formula you gave me working. But there is a small problem i
am trying to use data from the cell above in my formula. so it looks like
this:

=c1-b2+a2

working with the formula you suggested is ok till i add "c1" to the formula
like the example below:

=IF(OR((A1="0"),(B1="0")),"",C1-A1+B1)

Because the data in cell c1 is copied down and i am back to where i started!


anyone else who can help with this. I would be grateful or if Gary Student
has any other ideas , again thanks

 

[Gary''s Student]

Use the same approach:
=IF(OR((A2="0"),(B2="0"),(A2=""),(B2=""),(C1="0"),(C1="")),"",C1-A2+B2)

The key is to test ALL of the values that you need. If ANY are zero or
missing, then return blank

 

[Dobbin0_4]

Thanks that really helped.

Use the same approach:
=IF(OR((A2="0"),(B2="0"),(A2=""),(B2=""),(C1="0"),(C1="")),"",C1-A2+B2)

The key is to test ALL of the values that you need. If ANY are zero or
missing, then return blank