Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How do I use cell reference instead of name in a formula?
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? |
#3
|
|||
|
|||
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 "Steve L" <Steve wrote in message ... 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? |
#4
|
|||
|
|||
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 "Steve L" wrote: 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? |
#5
|
|||
|
|||
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. -- Arvi Laanemets ( My real mail address: arvil<attarkon.ee ) "Steve L" <Steve wrote in message ... 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? |
#6
|
|||
|
|||
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. "R.VENKATARAMAN" wrote: 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. ==================== Steve L <Steve wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Tab name / reference cell? | Excel Worksheet Functions | |||
Flexible Cell Reference | Excel Discussion (Misc queries) | |||
reference cell value from fixed column with variable row | Excel Discussion (Misc queries) | |||
Using a Text / Data output as a cell reference | Excel Discussion (Misc queries) | |||
Cell Formula reference to cell Based On third Cell Content | Excel Discussion (Misc queries) |