How to make a function recognize a text in the function?

[Ericng]

Eg.

I name a few ranges as TotalExposure, TotalScore... This ranges contain numbers.

Then I input "TotalExposure" to a cell(A1)

In cell(A2), I type in =sum(A1). With the hope that it will become =sum(TotalExposure)

But it turns out nothing.

[acerrhod]

You need to define a name range, in formulas tab go to Name Manager, then select new, on the NAME type TotalExposure and on the Refers to (bottom part of the name range window) select A2:A1000 (defends on the ranges you like) then click OK and CLOSE, now on B2 you can type the formula =SUM(TotalExposure) then Enter: note while typing =SUM(total - a drop down list will appear and looking your TotalExposure

[Ericng]

Hi there, thanks for the reply.

I understand what you are trying to show. But I want to key in "TotalExposure" into a cell(A1). Then in A2 I will type in =sum(A1). With this way I can change my target range easily without have to type the formula again.

[duanzhuanming]

Hi,
see attached file for your reference,

Hope this helps!

[hemesh]

ok checking

[Pepe Le Mokko]

Hi there, thanks for the reply.

I understand what you are trying to show. But I want to key in "TotalExposure" into a cell(A1). Then in A2 I will type in =sum(A1). With this way I can change my target range easily without have to type the formula again.

You need to use the INDIRECT function in this case

Please Login or Register  to view this content.

ALso avoid using volatile functions like OFFSET to define ranges, especially if you also use the INDIRECT function which is also volatile. You can name ranges using the INDEX function

[hemesh]

still it is slight confusing. Do you want to type "TotalExposure" in cell A1 and then in A2 want the result as Sum(TotalExposure)

[Ericng]

Hi Everyone, Sorry for the late reply. Thanks for helping!

Please refer to the attached file for better understanding.
Sorry for the inconvenience.


ex.xls

[hemesh]

you can do =sum(indirect(g7))

[hemesh]

if you find this helpful say thanks with "*".

[Ericng]

OMG!!! Thanks so much, everyone, especially Pepe Le Mokko and hemesh!

Helped me solved a lot of problem.