Posts by stuartgb100

Hi,

Working in Excel 2010 and I'm looking for a formula in column P which will autofill when new rows are inserted.

Working with row 10, the basic formula is:

P10=SUM(K10:O10)

where the Range(K10:P10) is formatted as Numeric, to 2 decimal places.

The formula should return "0.00" if the Range(K10:O10) contains no numeric values.

I have this (overcomplicated) formula from another project, but if I insert rows, then the formula does not fill down into those new rows:

=IF(OR(NOT(COUNT(I13))),0,SUM(K13:O13))

Many thanks.

Re: Template - protect after opening

Many thanks.

I create a template where all sheets are hidden except for a Warning sheet.
On opening the template (actually a copy of the template), user is presented with the Warning sheet.
This advises that macros must be enabled to give full functionality from the workbook.

If the user clicks "Enable Content", "Enable Macros" etc., then the Workbook_Open Event will fire,
and I hope to be able to control proceedings thereon.

However, if they do not enable macros, and start exploring their display .................
they might click "Close" or "Save" or "SaveAs" etc.

My (limited) understanding was that when a true template file is opened, then it is actually
a copy of the original file that is opened, and thus the original file is preserved.

For some reason, that does not seem to be the case for my template file - at least not always.
This happens when the user chooses not to enable macros and tries to do something else !

Complete beginner here, so how do I stop the user from doing anything other than;
a) enable content/macros
or
b) close - without modifying the original template file

Thanks in advance for your interest/help.

Re: Alter a Formula to take a Variable

Found it !

Earlier in the routine, a range is pasted into the sheet in question, and that imposes the text format.
Your formula is later pasted into one of the cells in that particular range
- hence the text format error.

Many thanks for your help.
Much appreciated.

Re: Alter a Formula to take a Variable

I tried using a "general" format on column "A" and also tried ClearFormats,
but I still get the same result.

How do I reset Formula view, please ?

Re: Alter a Formula to take a Variable

Thanks rory.

The result I'm getting is as follows:

='[VBA Billing 2 Sheets side by side.xlsm]Test Billing Sheet'!$C$81

This is the 'string' displayed in A10.

Is this as intended, or should the value in the referenced sheet be displayed ?

Thanks.

Re: Alter a Formula to take a Variable

Hi rory,

The formula I tried was as follows:

TargetSht.Range("D65536").End(xlUp) _
    .Offset(-4, -3).Formula = _
    "=INDIRECT(""C"" & (Target.Row))"

which first finds (in this case) A10, and then pastes the reference into it.

However, I get the error #NAME.

Also, there's no reference to the different worksheet (ie Test Sheet).

Thanks.

Hi,

I have two windows open side by side, and two sheets from the same workbook are displayed.
Sheet name is Dim Sheet in the left window, and Test Sheet in the right

I have ='Test Sheet'!C16 in A10 in Dim Sheet., which is referencing C16 in Test Sheet.

I am using A10 to keep track of C16 as it moves up and down that worksheet when rows
are inserted/deleted.

However, at runtime I will know it to be col C but NOT know it to be row 16.

I will however have Target (dimmed as Range) pointing to C16.

How can I get Target into the formula, or even (say)
"C" & Target.Row, or just the row number, for example ?

Thanks.