Ciara

Create a Defined Name for all the formulas you use often.

Then in your sheet instead of typing the formula you have
=dfSelectMonth

Be carefull when editing a defined Formula as their references are all
relative .. do it only from a cell where your formula is used.


both file size and recalc speed should imporve dramatically.



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"CiaraG" wrote:

I am designing a reports template based on imported data.
The imported data comprises of 12 months figures for a
number of variables.

Normally I would design a template based on a fixed
structure so if a new variable was added to the list it
would need to be added manually. My formulae within the
template would take the form of a nested if statement, so
depending on the month selected, the relevant cell vale
would be displayed - see below.

=IF(A4="January",NominalCSS!D153,IF
(A4="February",NominalCSS!E153,IF(A4="March",Nomin alCSS!
F153,IF(A4="April",NominalCSS!G153,IF(A4="May",Nom inalCSS!
H153,IF(A4="June",NominalCSS!I153,IF(A4="July",Nom inalCSS!
J153,IF(A4="August",NominalCSS!K153))))))))+IF
(A4="September",NominalCSS!L153,IF(A4="October",No minalCSS!
M153,IF(A4="November",NominalCSS!N153,IF
(A4="December",NominalCSS!O153,0))))

This large formulae would normally leave my file very
large. Has anyone any ideas how I may best design a
flexible reporting model or has any examples that I amy
review.

Thanks in advance.

Regards,

Ciara

Ciara,

this stuff is for a different NG.
Excel.WorksheetFunctions is more appropriate)


but anyway try:

=vlookup($a4,sheet2!$d$153:$o$153,2,False)


instead of

=IF(A4="January",NominalCSS!D153,IF
(A4="February",NominalCSS!E153,IF(A4="March",Nomin alCSS!
F153,IF(A4="April",NominalCSS!G153,IF
(A4="May",NominalCSS!
H153,IF(A4="June",NominalCSS!I153,IF
(A4="July",NominalCSS!
J153,IF(A4="August",NominalCSS!K153))))))))+IF
(A4="September",NominalCSS!L153,IF
(A4="October",NominalCSS!
M153,IF(A4="November",NominalCSS!N153,IF
(A4="December",NominalCSS!O153,0))))



Also when posting questions like this it's easier for us here if you
generalize your function and replace NominalCSS with something for
general use like Sheet2 :)

What would PWC's customers think to pay you $100 an hour to make
templates and you don't even know the bloody basics of Excel....

I'd buy a good Excel book if I were you and read at least the first 100
pages... you're bound to be introduced to the Vlookup function on page
20 or so.


Sorry for lashing out.... it's too bloody hot here.




keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"CiaraG" wrote:

Thanks for replying. I don't know if I fully understand
what you are saying.

When linking the imported data to my predefined reports I
use the same type of formula, except the row reference
will always change. Also sometimes, I may have to add
certain rows together. Therefore, none of my formulae are
unique and thus creating a defined name would have no
benefit?

Please provide an example based on the formulae I have
given.

Thanks,

CiaraG


-----Original Message-----
Ciara

Create a Defined Name for all the formulas you use often.

Then in your sheet instead of typing the formula you have
=dfSelectMonth

Be carefull when editing a defined Formula as their
references are all
relative .. do it only from a cell where your formula is
used.


both file size and recalc speed should imporve
dramatically.



keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"CiaraG" wrote:

I am designing a reports template based on imported
data.
The imported data comprises of 12 months figures for a
number of variables.

Normally I would design a template based on a fixed
structure so if a new variable was added to the list it
would need to be added manually. My formulae within
the
template would take the form of a nested if statement,
so
depending on the month selected, the relevant cell vale
would be displayed - see below.

=IF(A4="January",NominalCSS!D153,IF
(A4="February",NominalCSS!E153,IF(A4="March",Nomin alCSS!
F153,IF(A4="April",NominalCSS!G153,IF
(A4="May",NominalCSS!
H153,IF(A4="June",NominalCSS!I153,IF
(A4="July",NominalCSS!
J153,IF(A4="August",NominalCSS!K153))))))))+IF
(A4="September",NominalCSS!L153,IF
(A4="October",NominalCSS!
M153,IF(A4="November",NominalCSS!N153,IF
(A4="December",NominalCSS!O153,0))))

This large formulae would normally leave my file very
large. Has anyone any ideas how I may best design a
flexible reporting model or has any examples that I amy
review.

Thanks in advance.

Regards,

Ciara


.