Applying Formulae to a worksheet which are hidden

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,
 
0
RA6535 (12)
5/20/2009 12:51:02 PM
excel.newusers 15348 articles. 2 followers. Follow

11 Replies
948 Views

Similar Articles

[PageSpeed] 27

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


On Wed, 20 May 2009 05:51:02 -0700, Ra <Ra@discussions.microsoft.com> wrote:

>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,
> 

0
Gord
5/20/2009 2:03:47 PM
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.


"Gord Dibben" wrote:

> 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
> 
> 
> On Wed, 20 May 2009 05:51:02 -0700, Ra <Ra@discussions.microsoft.com> wrote:
> 
> >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,
> > 
> 
> 
0
RA6535 (12)
5/20/2009 2:31:01 PM
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" wrote:

> 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.
> 
> 
> "Gord Dibben" wrote:
> 
> > 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
> > 
> > 
> > On Wed, 20 May 2009 05:51:02 -0700, Ra <Ra@discussions.microsoft.com> wrote:
> > 
> > >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,
> > > 
> > 
> > 
0
5/20/2009 3:16:01 PM
Hi Sean

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


"Sean Timmons" wrote:

> 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" wrote:
> 
> > 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.
> > 
> > 
> > "Gord Dibben" wrote:
> > 
> > > 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
> > > 
> > > 
> > > On Wed, 20 May 2009 05:51:02 -0700, Ra <Ra@discussions.microsoft.com> wrote:
> > > 
> > > >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,
> > > > 
> > > 
> > > 
0
RA6535 (12)
5/20/2009 3:36:01 PM
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

On Wed, 20 May 2009 07:31:01 -0700, Ra <Ra@discussions.microsoft.com> wrote:

>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.
>
>
>"Gord Dibben" wrote:
>
>> 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
>> 
>> 
>> On Wed, 20 May 2009 05:51:02 -0700, Ra <Ra@discussions.microsoft.com> wrote:
>> 
>> >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,
>> > 
>> 
>> 

0
Gord
5/20/2009 10:06:28 PM
Gord

How do I hide the fomula bar?

How do you custom format using 3 semi-colons?

Thank you for your help,


"Gord Dibben" wrote:

> 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
> 
> On Wed, 20 May 2009 07:31:01 -0700, Ra <Ra@discussions.microsoft.com> wrote:
> 
> >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.
> >
> >
> >"Gord Dibben" wrote:
> >
> >> 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
> >> 
> >> 
> >> On Wed, 20 May 2009 05:51:02 -0700, Ra <Ra@discussions.microsoft.com> wrote:
> >> 
> >> >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,
> >> > 
> >> 
> >> 
> 
> 
0
RA6535 (12)
5/21/2009 8:44:00 AM
See in-line replies.

On Thu, 21 May 2009 01:44:00 -0700, Ra <Ra@discussions.microsoft.com> wrote:

>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

>
>Thank you for your help,
>
>
>"Gord Dibben" wrote:
>
>> 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
>> 
>> On Wed, 20 May 2009 07:31:01 -0700, Ra <Ra@discussions.microsoft.com> wrote:
>> 
>> >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.
>> >
>> >
>> >"Gord Dibben" wrote:
>> >
>> >> 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
>> >> 
>> >> 
>> >> On Wed, 20 May 2009 05:51:02 -0700, Ra <Ra@discussions.microsoft.com> wrote:
>> >> 
>> >> >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,
>> >> > 
>> >> 
>> >> 
>> 
>> 

0
Gord
5/21/2009 1:45:42 PM
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" wrote:

> See in-line replies.
> 
> On Thu, 21 May 2009 01:44:00 -0700, Ra <Ra@discussions.microsoft.com> wrote:
> 
> >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
> 
> >
> >Thank you for your help,
> >
> >
> >"Gord Dibben" wrote:
> >
> >> 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
> >> 
> >> On Wed, 20 May 2009 07:31:01 -0700, Ra <Ra@discussions.microsoft.com> wrote:
> >> 
> >> >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.
> >> >
> >> >
> >> >"Gord Dibben" wrote:
> >> >
> >> >> 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
> >> >> 
> >> >> 
> >> >> On Wed, 20 May 2009 05:51:02 -0700, Ra <Ra@discussions.microsoft.com> wrote:
> >> >> 
> >> >> >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,
> >> >> > 
> >> >> 
> >> >> 
> >> 
> >> 
> 
> 
0
RA6535 (12)
5/21/2009 2:34:11 PM
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

On Thu, 21 May 2009 07:34:11 -0700, Ra <Ra@discussions.microsoft.com> wrote:

>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" wrote:
>
>> See in-line replies.
>> 
>> On Thu, 21 May 2009 01:44:00 -0700, Ra <Ra@discussions.microsoft.com> wrote:
>> 
>> >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
>> 
>> >
>> >Thank you for your help,
>> >
>> >
>> >"Gord Dibben" wrote:
>> >
>> >> 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
>> >> 
>> >> On Wed, 20 May 2009 07:31:01 -0700, Ra <Ra@discussions.microsoft.com> wrote:
>> >> 
>> >> >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.
>> >> >
>> >> >
>> >> >"Gord Dibben" wrote:
>> >> >
>> >> >> 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
>> >> >> 
>> >> >> 
>> >> >> On Wed, 20 May 2009 05:51:02 -0700, Ra <Ra@discussions.microsoft.com> wrote:
>> >> >> 
>> >> >> >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,
>> >> >> > 
>> >> >> 
>> >> >> 
>> >> 
>> >> 
>> 
>> 

0
Gord
5/21/2009 4:05:05 PM
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" wrote:

> 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
> 
> On Thu, 21 May 2009 07:34:11 -0700, Ra <Ra@discussions.microsoft.com> wrote:
> 
> >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" wrote:
> >
> >> See in-line replies.
> >> 
> >> On Thu, 21 May 2009 01:44:00 -0700, Ra <Ra@discussions.microsoft.com> wrote:
> >> 
> >> >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
> >> 
> >> >
> >> >Thank you for your help,
> >> >
> >> >
> >> >"Gord Dibben" wrote:
> >> >
> >> >> 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
> >> >> 
> >> >> On Wed, 20 May 2009 07:31:01 -0700, Ra <Ra@discussions.microsoft.com> wrote:
> >> >> 
> >> >> >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.
> >> >> >
> >> >> >
> >> >> >"Gord Dibben" wrote:
> >> >> >
> >> >> >> 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
> >> >> >> 
> >> >> >> 
> >> >> >> On Wed, 20 May 2009 05:51:02 -0700, Ra <Ra@discussions.microsoft.com> wrote:
> >> >> >> 
> >> >> >> >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,
> >> >> >> > 
> >> >> >> 
> >> >> >> 
> >> >> 
> >> >> 
> >> 
> >> 
> 
> 
0
RA6535 (12)
5/22/2009 8:15:01 AM
I don't what else to offer that I have not already posted for copying and
hiding things.


Gord

On Fri, 22 May 2009 01:15:01 -0700, Ra <Ra@discussions.microsoft.com> wrote:

>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" wrote:
>
>> 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
>> 
>> On Thu, 21 May 2009 07:34:11 -0700, Ra <Ra@discussions.microsoft.com> wrote:
>> 
>> >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" wrote:
>> >
>> >> See in-line replies.
>> >> 
>> >> On Thu, 21 May 2009 01:44:00 -0700, Ra <Ra@discussions.microsoft.com> wrote:
>> >> 
>> >> >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
>> >> 
>> >> >
>> >> >Thank you for your help,
>> >> >
>> >> >
>> >> >"Gord Dibben" wrote:
>> >> >
>> >> >> 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
>> >> >> 
>> >> >> On Wed, 20 May 2009 07:31:01 -0700, Ra <Ra@discussions.microsoft.com> wrote:
>> >> >> 
>> >> >> >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.
>> >> >> >
>> >> >> >
>> >> >> >"Gord Dibben" wrote:
>> >> >> >
>> >> >> >> 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
>> >> >> >> 
>> >> >> >> 
>> >> >> >> On Wed, 20 May 2009 05:51:02 -0700, Ra <Ra@discussions.microsoft.com> wrote:
>> >> >> >> 
>> >> >> >> >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,
>> >> >> >> > 
>> >> >> >> 
>> >> >> >> 
>> >> >> 
>> >> >> 
>> >> 
>> >> 
>> 
>> 

0
Gord
5/23/2009 6:16:21 PM
Reply:

Similar Artilces:

Second Posting for Extending Formula
Sorry if this question stumped the experts. Or maybe no one saw the question. I'll try posting it again. Using Excel 2003 and am having trouble getting a formula to extend to new rows. I ran a trial formula that simply adds the number in A1 to the number in B1 and puts the result in C1. I copied that formula down five rows and filled data into Column A and Column B. When I added new rows, the formula extended into the new rows just perfectly. Results appeared in Column C. Then I ran a different trial formula. I entered a number into C1 (100) and I put a number in A2 (10) and a ...

how get rid of cells with unused formulas
Hi all thanx in advance I created a macro to create a new sheet and clone cells from an older sheet like this ='Sheet1'!B1 then the macro does autofill until row 2000 the reason of doing that is because sheet1 for example is designed to hold info until that no. of cells and I want to sort only those columns copied to that sheet, but the problem is when doing sort with autofilter on, all the empty cells which contains a value but is not used because on the older sheet this cell is empty (but on this sheet it shows a zero 0) goes to the top, My question is how do I delete all the ...

Formula Help #10
A friend of mine asked me if the following formula can be shortened? =IF(D2="","",CONCATENATE("host ",L2,IF(C2="","","."),IF(OR(B2="",B2> 30,),"",CONCATENATE(B2,"-")),C2," { hardware ethernet ",(CONCATENATE(LEFT (D2,2),":",RIGHT(LEFT(D2,4),2),":",RIGHT(LEFT(D2,6),2),":",RIGHT(LEFT (D2,8),2),":",RIGHT(LEFT(D2,10),2),":",RIGHT(D2,2),"; fixed-address ",M2,"; }")))) FYI: The cell D2 contains: 00E06F734032 Is there a way? ...

Apply view to all folders
Greetings, A user has numerous folders created under her Inbox in Outlook 2000. She woule like to create a custom view and then apply it to all folders automatically. I can create a custom view easily, but can't find a way to apply it to all folders other than on a one-by-one basis. Is there a way to apply the view to all folders at one time? Thanks much, Justin You can't apply it to all at once - unless you customize the Messages view. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Coauthor, OneNote 2003 for Windows (Visual QuickStart Gu...

How do I apply a combo box to multiple cells in Excel so that it .
I would like to apply a combo drop down box in Excel. However I only want the box to appear when the cursor is over the cell can anyone offer me some help on this matter? Thank you, Dwain Hi Dwain, Perhaps you are looking for Data Validation, available on the Data menu. See Debra Dalgleish's tutorial at: http://www.contextures.com/xlDataVal01.html --- Regards, Norman "DB" <DB@discussions.microsoft.com> wrote in message news:6939ADC2-CF1C-46AA-B163-BEE27E23356A@microsoft.com... >I would like to apply a combo drop down box in Excel. However I only w...

Copying formulas #12
Hi all, I have a row of forumulas, which relate to columns of data. Each formula needs to be copied down the sheet, but I need the column value to change, not the row value. eg C3 : =sum(c2:c31) C4 : =sum(d2:d31) C5: =sum(e2:e31) etc. How can I replicate the formulas down, but get the Column values to change ? TIA Stu Ignoring the issue of Circular references (the formula in C3 references a range that includes C3)... I *think* this will work for you Put the formula =SUM(C$2:C$31) into an empty row in your spreadsheet and copy it across to all the columns you want to su...

Convert text into formula.
Is there a way, without resorting to a macro, to convert a text string into a formula? Example: A1 contains the text string "'B1+C1" In A2, I would like a formula that converts the string in A1 into a formula. Does such a function exist? Ultimately, what I want to achieve is to concatenate two text string, from two cells, to create a longer formula. This UDF can do simple things as your example. Not sure what your "real" text strings consist of. Function EvalCell(RefCell As String) Application.Volatile EvalCell = Evaluate(RefCell) End Fun...

formula help!!
Hello there, I am desperate for some help on a excel project....basicaly I have 2 columns, one will be a "Y" and the other a "N"...I need to come up with a formula to add these up at the end of the column...I don't know how to write a formula that tells it that, for example Y=1 and then add columns C3 to C175..I would really appreciate it if some kind soul could help me out...thanks, Mike Hi Mike i think you're after the SUMIF function =SUMIF(A3:A175,1,C3:C175) will add up the values in C3:C175, where the corresponding cell in column A has a 1 in it or =SU...

view applied?
What does the following WLM beta message mean? View applied not connected to imap4.xxxxxx.co.uk I had already clicked on this account and a couple of emails were downloaded. ...

Query Formula Help
I have a field "Last Contact Date" in a query. I need to create a formula to show me the records that have a last contact date more than 30 days old. Any thoughts??? Thanks. WHERE DateDiff("d", [YourTable].[Last Contact Date], Now()) > 30 HTH; Amy "Jimmy" <Jimmy@discussions.microsoft.com> wrote in message news:820174F0-445C-472D-8B1C-F6711482D8C0@microsoft.com... >I have a field "Last Contact Date" in a query. I need to create a formula >to > show me the records that have a last contact date more than 30 days old. > Any ...

M2005 Cannot Apply Epayment To Invoice
This is a multi-part message in MIME format. ------=_NextPart_000_000C_01C4AD73.81F30C10 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I create invoices for payments from customers. I have those = automatically charged to their bank accounts and credited to my bank = account. When the payments hit my bank account and are downloaded into = Money, I go into the transaction and enter "Payment For Invoice" and = then try to go to the apply payment to an invoice screen but a nice = little pop up comes up in Money 2005 that says &qu...

I've applied a Fill and now can't see the gridlines....
Does anyone know how to show the Gridlines through a fill? I've searched and searched but can't find the answer, yet the silly thing is I know it can be done as I have done it before (by accident) Any help mostgratefully received! bestregards, Tim Apply borders... In article <62E1EA83-287A-4FE3-BEC6-7C0155EA1850@microsoft.com>, "FizzyBunghole" <FizzyBunghole@discussions.microsoft.com> wrote: > Does anyone know how to show the Gridlines through a fill? I've searched and > searched but can't find the answer, yet the silly thing is I know it c...

How do I do formulas?
Hi, How do I do formulas? I want to change numbers to forlumas. How do I do this? Can it do done? Thank You. Bryan If you mean you want the sheet to display the formulas instead of th reults you can do it by pressing the Ctrl key & the ' key or Tools Menu > Options > View Tab > Tick Formula -- mudrake ----------------------------------------------------------------------- mudraker's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=247 View this thread: http://www.excelforum.com/showthread.php?threadid=27107 Also, make sure your Formula b...

Formula assistance required
Hi guys, am sure you'll be able to help with this but I just cant figure it out; Worksheet looks a little like this; A P U 14 27/02/10 Y 10.00 15 04/03/10 Y 15.00 16 05/03/10 N 0.00 17 06/03/10 N 0.00 18 07/03/10 Y 12.00 My data is in rows 14 to 1000. Col A is the date the claim has been submitted, Col P is Yes or No to whether claim has been accepted and Col U is the Loss value. I have a summary table set up and am wanting to have a weekl...

Cross Apply problem
How do I get the desired result for the following: SOURCE TABLE: USSoilLyr FIELDS: TAXOR PARTSIZE PCT_R Alfisols ashy 60 Alfisols ashy 57 Alfisols ashy 38 Alfisols clayey 85 Alfisols clayey 79 Andisols medial 50 Andisols medial 36 DESIRED RESULT (TARGET TABLE): T_Order FIELDS: TAXOR PARTSIZE PCT_R Alfisols ashy AVG(60+57+38) Alfisols clayey Avg(85+79) Andisols medial Avg(50+36) My T-SQL Code, as follows, returns wrong calculations when I manually check the results. UPDATE T_Order SET ...

How do I merge worksheets?
How do I merge worksheets and keep the columns separate. I do not want the data to merge into one column, but keep the columns separate once merged.. hi MGD Worksheets (sheets or workbooks ?) See the columns option here http://www.rondebruin.nl/copy2.htm -- Regards Ron de Bruin http://www.rondebruin.nl "MGD" <MGD@discussions.microsoft.com> wrote in message news:D616A081-2452-46CC-96B4-3FA3F75EABA9@microsoft.com... > How do I merge worksheets and keep the columns separate. I do not want the > data to merge into one column, but keep the columns separate once merg...

Average Multiple Worksheets without blanks or zeros
Have a workbook where each worksheet is a day of the month. The last worksheet contains a running tally of each day;s information. I need to average the same cells from different worksheets while omitting the blanks and zeros, in case there is no data for an individual day. Thanks. Assume that you are having values in A1:A10 like the below (A1:A10) 66 0 88 0 (Blank) 0 0 99 (Blank) 0 Use the formula like this, which will ignore blank cells and 0 (zero) values. =SUM(A1:A10)/(COUNT(A1:A10)-COUNTIF(A1:A10,0)) Remember to Click Yes, if this post helps! ----------...

Install V10 HR after applying service pack 3
I am currently on V10, SP3 and now have purchased the HR module. I am not sure of the steps I need to take to load the HR module (we have the V10 DVD that is not sp3) Are these my steps? 1. Load the HR module from V10 DVD on SQL Server install (I was told previously to use the Add/Remove programs option and change but it still asks for the DVD) 2. Do I need to reload SP3 on server install? 3. Then load the HR module (from V10 dvd) on additional workstations? If I have to re run the SP3 I am concerned it is going to take hours to complete like original instal of SP3 If anyone has a...

Apply Multicurrency Invoices in Bank Management
I oppened a ticket to know why I cant apply Multicurrency Invoices to payment transactions in Bank Managment as it is available in Payable Management. I've been suggested to post a suggestion for that. Simply, while doing a payment in Bank Management. If the Chequebook Currency is diffrenet than the invoices currencies that you are paying againist, then you can't apply these invoices. Thus, you cant see them in the remittance advice. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestio...

Formula error
HI! Please I need help for this... I tried to put more conditions, but excel says that formula contains an error. You know an alternative for this ? =IF(AND(G2>=C2;G2<=D2);E2;IF(AND(G2>=C3;G2<=D3);E3;IF(AND(G2>=C4;G2<=D4);E4;IF(AND(G2>=C5;G2<=D5);E5;IF(AND(G2>=C6;G2<=D6);E6;IF(AND(G2>=C7;G2<=D7);E7;IF(AND(G2>=C8;G2<=D8);E8;0)))))) Thanks for yor help. Look in HELP for the VLOOKUP function -- Kind regards, Niek Otten "Pasmatos" <Pasmatos@discussions.microsoft.com> wrote in message news:96035192-F376-4871-87A3-323EF13887AF@microsof...

possible countif formula?
Hi Again everyone I am trying to get a formula in (possible countif) which doesn't seem to be working In column D I have the following dates D42 30/03/2009 D43 01/06/2005 D44 06/02/2009 D45 30/03/2009 D46 19/10/2009 D47 08/03/2010 D48 15/07/2010 D49 15/07/2010 D50 15/09/2010 D51 15/09/2010 In I3 I have 31/01/10, J3 28/02/10 etc etc running along to AR3 which has 31/12/12 I want to write a formula that simply says to check the dates in column D42 - D51 against the date in I3 and if the date in ...

unposted applied credits
how can list all unposted applied credits in AR. Thanks, Scott If you have SmartList Builder, you could use it to create a SmartList based on the Cash receipts work file. "Scott Rusoff" wrote: > how can list all unposted applied credits in AR. > > Thanks, > Scott > Create a report off RM10201 (RM Cash Receipts Work File). Add a restriction where Current Transaction Amount <> Original Transaction Amount. "Scott Rusoff" wrote: > how can list all unposted applied credits in AR. > > Thanks, > Scott > Which smartlist contains t...

using formula =sum('*'!A1) in a macro does not work.
I am trying to use the formula above in a macro that is ran once a button is clicked. the problem is that the formula goes from =sum('*'!A1) to =sum('*''!A1'), this results in an error message (#NAME), does anyone know why this is happening when it is used in a macro? If i use this in a cell and type it myself it works great! Thanks in advance, TG I found this on a website called The Code Cage.com You can read the whole thread here. http://www.thecodecage.com/forumz/excel-miscellaneous/176808-sum-cell-value= -across-multiple-worksheets.html ...

Payables issue with Applied Payments and Void Check
Hi, We had a user a number of months ago apply a number of Credit Memos from a vendor to a large invoice while creating a check. Apparently the check was then void, without a normal check number leaving us with "REMIT0000000000xx" as the reference to it. We now have a situation where the large invoice and all of the credits are still showing in the system as open. The individual credit memos do not show as having amounts applied, but when we try to apply them we're told that the amount is already applied. Everything seems to be stuck in limbo, with no way to post to resol...

How to protect ALL formulas in a WORKBOOK
Is there an easy way to protect all formulas in an entire workbook at once? I have a workbook with over 100 sheets and would like to protect all the formulas on each sheet without having to protect each sheet individually. liebzeit, here is one way, Sub Lock_All_Formulas() 'will lock & hide all cells with formulas in them 'and also unlock all cells without formulas in them 'and protect the sheet Dim cell As Range Dim sht As Worksheet Application.ScreenUpdating = False For Each sht In Worksheets sht.Activate For Each cell In sht.UsedRange If cell.HasFormula = True Then cel...