How do I use cell reference instead of name in a formula?

[Guest]

I have a spreadsheet I am using for financial statements. In one area, I have
to create sub-totals based on the rows above.

The problem is that some of the cells are set up as named ranges for use
elsewhere, and some are not. As a result, the formulas for my sub-totals
contain both names and cell references. When I try to copy these formulas to
adjacent cells for other months, some formula components change (eg. C35
becomes D35) while others don't (eg. Jan_Sales stays as Jan_Sales).

This makes a complicated edit, overly prone to errors. It is also not
something I am comfortable turning over to someone else to use on an ongoing
basis.

I could create names for the un-named cells, but this would result in a HUGE
number of names to set up. Is there any way to use the cell reference for the
named cells in the formulas I am creating?

 

[R.VENKATARAMAN]

if use absolute addresses the address will not chane. for e.g. $C$35 will
remain as c35.

another function <indirect> also will be usefu. see help

use some such method of using cell addresses instead of names of ranges.

====================

 

[Biff]

Hi!

Just because a cell or range has a defined name doesn't mean you can't still
refer to the cell(s) by their address.

If A1 has a defined name of TYPE, you can still use A1 as a formula
reference.

Just my personal preference, but I very rarely use defined names. Here's
why:

=IF(Type=10,1,"")

=IF(A1=10,1,"")

Looking at the 2nd formula I can instantly tell where the references are.

Biff

 

[Guest]

Dear Steve,

You have to use cell address reference LIKE A1:A10 Instead of range name XYZ.

you have to change it manually from range name to cell ref. address

 

[Arvi Laanemets]

Hi

There is no need to define a separate name for every range. You can create
dynamic named ranges, which use the target cell as one argument - the
returned range depends on cell, where it is used as function argument, or on
some other parameter.

An example how to create such named ranges you can find in my answer to
thread 'How to start a database' from yesterday in microsoft.public.excel
NG - ranges ResultArray, PointsArray, RaceTable etc.

 

[Guest]

hello,

perhaps you can help me with this:

in column A i have different values, for example:
A B
1 aa
2 bb
3 cc

in column B i want to define cells as reference to some named cells located
elsewhere. for example: cell B1: =aatotal and so on in other cells:
bbtotal, cctotal...
how can i define formulas in B column based on values in A in order not to
retype aa bb cc in all the cells. something like B1: =A1total ... ?

thanks,

Alex.