How to rename a formula

[m1zz13]

Hi all,

I thought this might be simple, but it seems to be more difficult than it sounds.

If I have 2 formulas in the one cell. Example:
=sum(A1:A5)*IF(A1>0,A1*B1,0)

Is there a way to rename each formula as such:
=TOTAL*VARIABLE

Where;
TOTAL=sum(A1:A5)
and
VARIABLE=IF(A1>0,A1*B1,0)


BUT... The tricky part is, I don't want the renamed formulas to be absolute. I.e. If I drag the formula down, I would like the cell referencing to drag down also.


I have attached an example for reference.

Thanks in advance.
D.

[icestationzbra]

use the functionality of Named Ranges.

create one Named Range called Total and another called Variable with exactly the formula you want, and then use those two Named Ranges in a formula just like you desire.

[m1zz13]

Thanks icestationzbra,

I am trying to avoid using cell referencing by placing each individual formula in different cells, then renaming them for future referencing.

I am trying to get the two formulas to be in the one cell only (apart from the data ranges mentioned within these formulas).

Is that possible without cell referencing?

[FDibbins]

I believe that icestationzebra answered your question for you already then. If you use "define name", give the ramne the name TOTAL, and in the "refers to" section, use =SUM(Sheet1!$A$1:$A$5), then every time you enter =total, it will give you the total of A1:A5

use the functionality of Named Ranges.

create one Named Range called Total and another called Variable with exactly the formula you want, and then use those two Named Ranges in a formula just like you desire.

[m1zz13]

I am familiar with the functionality of renaming cells or ranges, then being able to refer them when using new formulas.

The only matter is that I don't want the SUM(A1:A5) to be absolute.

Essentially what I am trying to achieve is shorter formulas in the Formula Bar to make it a bit clearer and more workable when using very long formulas.

see attached example.

[FDibbins]

so you want a formula that doesnt reference cells, and you want a formula in each cell....but you want them both in the same cell?

sorry, that doesnt make any sense to me, perhaps you can try top explain in more detail what you are trying to achieve?

[m1zz13]

so you want a formula that doesnt reference cells, and you want a formula in each cell....but you want them both in the same cell?

sorry, that doesnt make any sense to me, perhaps you can try top explain in more detail what you are trying to achieve?

FDibbins,

Essentially I am trying to rename a mathematical formula within a cell. I.e. I want this formula 'SUM(A1:A5)' to be renamed to the word TOTAL. So that every time I use the word 'TOTAL' in the formula bar, it will refer to that formula.

Does that make more sense?

[FDibbins]

OK, I understand now. Im not sure if what you want is possible using just regular functions/formulas, perhaps some-one can put some VBA code together for you

[m1zz13]

Thanks FDibbins,

It's a toughy, but it's an answer worth waiting for because some of the formulas I am currently working on are getting quite long and confusing to work with.

[vjharry]

Go to name manager and define name there u can do that
Attached file is there to check
do =answer() and put enter and there would no cell referencing in that

[m1zz13]

Go to name manager and define name there u can do that
Attached file is there to check
do =answer() and put enter and there would no cell referencing in that

Thanks VJ,

Where can I find the name manager?

This is exactly what I am after.

[FDibbins]

@vjharry, if you do the naming correctly, it will give you the total you want. However, the ranges do not adjust if copied/dragged

@m1zz13, I would question why you need this in the 1st place. If you create the formula, like you have, and then just copy/drag, I dont see the problem. If your formulas are that long, maybe they can be condensed?

[vjharry]

@FDibbins .....i have shown all it want by m1zz13..

@m1zz13.. pl let me now if any thing left
Thanks Again,

[vjharry]

please do Alt+M+M
there you will find that

or in Formulas tab there u will find that ...

[m1zz13]

FDibbins,
I am trying to make a 400 character+ formula into something more manageable. The only issue, once this is finished, I would like to copy this onto more cells, and when I drag it down down, whichever cells that aren't absolute, I need them to continue referencing down the column.

it is merely to make it more visually manageable and less confusing.


VJay,
Thanks it works perfectly.

[FDibbins]

yes I know how to get into the name manager...your method does not work. inthe file you uploaded, type in =answer into any cell - try G19 - it gives 0

[m1zz13]

@FDibbins, if you place the formula =Answer in G19, the referencing drags across, which is exactly what I was after. If the cells within the formula I renames are absolute, I want to to stick (e.g. $A$1 will always remain). but if the cells aren't locked, I need them to move across when dragged.

[FDibbins]

ok cool, glad you got the answer you want

[m1zz13]

Thanks for all your efforts FD and VJ