Applying Formulae to a worksheet which are hidden

[Ra]

The problem:

I have set up a work sheet with formulae in the following row:

Cells E5 to Z5 are populated with formulae,
(F5, G5, I5, L5, Q5, V5, Y5, Z5 have Conditional Formatting). The formulae,
in some cases, are dependent on the preceding cell.

I would like to apply all these formulae to all the rows from row 5 and
below. Also, I would like all the formulae to be hidden. So that when new
data is entered in rows 6 to X, all you see is the data being entered with
the formulaes being hidden.

Please can you help, I am new to excel so simplicity is key.

Many thanks,

 

[Gord Dibben]

You state you want row 6 and below to have the same formulas as row 5 but
then you want to enter data in row 6 and below.

You cannot have formulas in cells then enter data in those same cells
without erasing the formulas.

Please re-describe what you need.


Gord Dibben MS Excel MVP

 

[Ra]

Hi Gord

Thank you for your reply and apologies for not being clear enough. For
example:

Cells E5 to Z5 are populated with formulae,
(F5, G5, I5, L5, Q5, V5, Y5, Z5 have Conditional Formatting). The formulae,
in some cases, are dependent on the preceding cell.

E5=D5+14, H5=G5+10, etc. These are on one row and interspersed with
conditional formatting on adjacent cells, as indicated above.

I want to apply these same conditions to the rows immediately beneath row 5
and hide the formulae.

I hope that makes sense this time.

 

[Sean Timmons]

Are you looking to hide the entire rows? Or, do you want to show just values
instead of the formulas?

you can hide them by right-clicking on the row number to the left and
selecting hide.

If you have a formula in the cell, you can't hide the fact that it has a
formula. You can copy the cells, then right-click, select paste
special/values to CHANGE them to constants. In that case, the formulas would
be gone altogether...

Was that what you were referring to?

 

[Ra]

Hi Sean

I just want to show the values, not the formulas.

 

[Gord Dibben]

Use the Format Paintbrush to copy the CF to the cells below.

To copy formulas, simply drag/copy downwards.

Hiding the formulas means you don't want users to see the formulas in the
formula bar or in the cells?

You have to use sheet protection to hide the formulas in the bar or just
don't have the bar in view.

To not see anything in the cells you can custom format as 3 semi-colons ;;;


Gord

 

[Ra]

Gord

How do I hide the fomula bar?

How do you custom format using 3 semi-colons?

Thank you for your help,

 

[Gord Dibben]

See in-line replies.

Gord

How do I hide the fomula bar?

Excel 2003..........View>Formula Bar. Toggle on or off

Excel 2007...........View Tab>Formula Bar

How do you custom format using 3 semi-colons?

Excel 2003........Format>Cells>Number>Custom. Enter ;;;

Excel 2007........Home Tab>Format>Format Cells.......as above

To hide the formulas in formula bar only use Format>Cells>Protection. Check
"hidden" then protect the workheet


Gord

 

[Ra]

Hello Gord

Many thanks for this. Really appreciated. Please can you help me with one
other question:

I have the following situation:

E5 has today's date
F5 = E5+10
When I enter E6 as say tomorrow's date, F6 does not automatically update not
until 4 rows have been entered. I hope this makes sense. How can I get F6
to automatically update?

Many thanks once again.

 

[Gord Dibben]

What do you have in F6 that would cause it to update when a date is entered
in E6 and to what value would it update?

Post the formula you must have in F6

Maybe =IF(E6="","",E6 + 10)


Gord

 

[Ra]

Gord

What I have is:

F6=E6+10
F7=E7+10
etc.

At the end of the day, what I really want is, if I input a whole load of
Formulas in say row 5, see below. I want all the formulas to be applied to
all the descending cells without them being visible. So that when I enter
data in those cells the formulas will calculate the results.

For example,

Cells E5 to Z5 are populated with formulas,
(F5, G5, I5, L5, Q5, V5, Y5, Z5 have Conditional Formatting).

I was given this macro to install but quite honestly, I don't understand
what it does nor do I know if it works for the specific problem described?

Private Sub Worksheet_Change(ByVal Target As Range)
mv = Target
Application.EnableEvents = False
Target.Formula = "=offset(a1," & mv & ",2)"
Application.EnableEvents = True
End Sub


Thank you,

 

[Gord Dibben]

I don't what else to offer that I have not already posted for copying and
hiding things.


Gord