Excell named cells or labeled cells question
 

Hi,

-I also have a cell that lists the Total Income, which i name GSI
-I also have collumn listing Expenses and at the bottom, sums the total
expenses, which iname TOTAL_EXPENSE
-Lastly, below that, i calculate the Net Income by subracting the
expenses from the income, which i call NET_INCOME
-All are in one column.

Both in formulas elsewhere and gode, i refer to GSI and TOTAL_EXPENSE
cells by name. For instance the NET_INCOME cell is GSI - TOTAL_EXPENSE.

My issue here is, when i now cut and past that column to create a new
column, the forumla in the new column still refers to the named column
in the original column. So in this example, for column two, the net
income cell refers to the column cells, since it is reference
TOTAL_EXPENSE.

My question is, how do i handle this and still be able to use named
columns, which are so much more clearer if someone were looking at my
formula or code. Is there a way to create ARRAYs of named cells in
this case? so that the first column would be referred to as
TOTAL_EXPENSE[0] and the second column would be referred to as
TOTAL_EXPENSE[1]?

i hope i explained myself clear. Please advise if you are able to.

thank you

Excell named cells or labeled cells question
 

reposting because of typos in my earlier posts:

Hi,

-I have a cell that lists the Total Income, which i name GSI
-I also have column listing Expenses and at the bottom, sums the total
expenses, which i name TOTAL_EXPENSE
-Lastly, below that, i calculate the Net Income by subracting the
expenses from the income, which i call NET_INCOME
-All are in one column.

In both formulas and code elsewhere , i refer to GSI and TOTAL_EXPENSE
cells by name. For instance the NET_INCOME cell is GSI - TOTAL_EXPENSE.

My issue here is, when i now cut and past that column to create a new
column, the forumla in the new column still refers to the named column
in the original column. So in this example, for column two, the net
income cell refers to the column 1 cell, since it is stiull referencing
TOTAL_EXPENSE after the cut and paste.

My question is, how do i correct this and still be able to use named
columns, which are so much more clearer if someone were looking at my
formula or code. Is there a way to create ARRAYs of named cells in
this case? so that the first column would be referred to as
TOTAL_EXPENSE[0] and the second column would be referred to as
TOTAL_EXPENSE[1]?

i hope i explained myself clear. Please advise if you are able to.

thank you

Excell named cells or labeled cells question
 

Hi farseer,

You could make your defined names column-relative so they refer to absolute
rows only. This will allow copying the formula(s) across other columns (ie.
fill across) and have them refer absolutely to the rows in their respective
column containing the formula(s). For example:

If the RefersTo box shows: =Sheet1!$A$2:$A$8

change it (remove the $ before the column) to show as: =Sheet1!A$2:A$8

Now the formula will refer to rows 2 to 8 of the column containing the
formula. This assumes the rows alloted to each section are constant across
the sheet. For example, if you copy the formula(s) to column D, the
respective .RefersTo will be:

=Sheet1!D$2:D$8
and..,
selecting the name in the name dropdown will select cells of rows 2 to 8 in
whichever column the active cell is currently located.

HTH
Regards,
Garry

Excell named cells or labeled cells question
 

That's great Gary and exactly what i am looking for. However i am have

one difficulty doing this.

i click on the cell named "EXPENSE_TOTAL", go to menu
Insert-Name-Define. Find the EXPENSE_TOTAL name, remove the "$" as
you suggested and then click ok. However, when i do this, the name is
no longer associated with that cell and it does not seem as though i
can reassociate it with that cell.

Any suggetions?

GS wrote:
Hi farseer,

You could make your defined names column-relative so they refer to absolute
rows only. This will allow copying the formula(s) across other columns (ie.
fill across) and have them refer absolutely to the rows in their respective
column containing the formula(s). For example:

If the RefersTo box shows: =Sheet1!$A$2:$A$8

change it (remove the $ before the column) to show as: =Sheet1!A$2:A$8

Now the formula will refer to rows 2 to 8 of the column containing the
formula. This assumes the rows alloted to each section are constant across
the sheet. For example, if you copy the formula(s) to column D, the
respective .RefersTo will be:

=Sheet1!D$2:D$8
and..,
selecting the name in the name dropdown will select cells of rows 2 to 8 in
whichever column the active cell is currently located.

HTH
Regards,
Garry

Excell named cells or labeled cells question
 

That's exactly how it should behave. Removing the $ before the column label
makes the defined name range column-relative, row-absolute. This means it
will reference the cell in the same row, but whichever column the active cell
is located in. If you want to constantly reference an absolute cell, give it
another name than the one used in the formulas.

Maybe the following will help. It assumes that you want to determine
identical calculations for Income, Expense, and NetIncome across several
columns. This is a typical scenario for say comparing a P&L or Balance Sheet
for multiple periods, each located in their own columns. Here's the layout:

In the Define Names dialog:
Define income cells:
NameBox entry: SheetName!Income (makes it sheet level)
RefersTo: entry =SheetName!B$2:B$8
(makes it absolutely rows 2 to 8 across all columns)

Define total income cell:
NameBox entry: SheetName!TotalIncome
RefersTo entry: =SheetName!B$9
(makes it absolutely row 9 across all columns)

Define expense cells:
NameBox entry: SheetName!Expenses
RefersTo entry: =SheetName!B$10:B$20
(makes it absolutely rows 10 to 20 across all columns)

Define total expense cell:
NameBox entry: SheetName!TotalExpense
RefersTo entry: SheetName!B$21
(makes it absolutely row 21 across all columns)

Define net income cell:
NameBox entry: SheetName!NetIncome
RefersTo entry: =SheetName!B$22
(makes it absolutely row 22 across all columns)

Adjust the rows for income and expense ranges to suit.

Now you can set up your sheet for mutiple lists, each for separate periods.
Not all income or expense cells need to have values. Put descriptions in
column A for each income/expense category, and the totals. Here's what it
will look like:

1 Period1 Period2
2 Income1 0.00 0.00
3 Income2 10.00 15.00
4 Income3 10.00 20.00
....
9 Total Income 20.00 35.00 (Formula is: "=SUM(Income)"
10 Expense1 2.00 0.00
11 Expense2 7.00 10.00
....
21 Total Expense 9.00 10.00 (Formula is: "=SUM(Expenses)"
22 Net Income 11.00 25.00 (Formula is:
"=TotalIncome-TotalExpenses")

If you need more help then post back to email a sample file.

HTH
Garry

Excell named cells or labeled cells question
 

My apologies! There's a typo in the formula for Net Income. It should read:

=SUM(TotalIncome-TotalExpense)

Regards,
Garry

Excell named cells or labeled cells question
 

Well that's not right either. Geez, ..I better get another cup of coffee!
This IS right:

=TotalIncome-TotalExpense
---
Garry



"GS" wrote:

My apologies! There's a typo in the formula for Net Income. It should read:

=SUM(TotalIncome-TotalExpense)

Regards,
Garry

Excell named cells or labeled cells question
 

Ok, thanks Gary. I guess what threw me is i was hoping i could still
identify an "Income" cell by name visually still. FOr instance, when i
click on the income cell, regardless if it is in the first or second
column, it would be nice to see what it's name is.

In a complex spread sheet (like the one i have), it makes it quite a
bit easier to be able to quickly look back and determine what cells
contain what.

i suppose we can't have it all though...thanks once more for your help.
I really appreciate it.

GS wrote:
That's exactly how it should behave. Removing the $ before the column label
makes the defined name range column-relative, row-absolute. This means it
will reference the cell in the same row, but whichever column the active cell
is located in. If you want to constantly reference an absolute cell, give it
another name than the one used in the formulas.

Maybe the following will help. It assumes that you want to determine
identical calculations for Income, Expense, and NetIncome across several
columns. This is a typical scenario for say comparing a P&L or Balance Sheet
for multiple periods, each located in their own columns. Here's the layout:

In the Define Names dialog:
Define income cells:
NameBox entry: SheetName!Income (makes it sheet level)
RefersTo: entry =SheetName!B$2:B$8
(makes it absolutely rows 2 to 8 across all columns)

Define total income cell:
NameBox entry: SheetName!TotalIncome
RefersTo entry: =SheetName!B$9
(makes it absolutely row 9 across all columns)

Define expense cells:
NameBox entry: SheetName!Expenses
RefersTo entry: =SheetName!B$10:B$20
(makes it absolutely rows 10 to 20 across all columns)

Define total expense cell:
NameBox entry: SheetName!TotalExpense
RefersTo entry: SheetName!B$21
(makes it absolutely row 21 across all columns)

Define net income cell:
NameBox entry: SheetName!NetIncome
RefersTo entry: =SheetName!B$22
(makes it absolutely row 22 across all columns)

Adjust the rows for income and expense ranges to suit.

Now you can set up your sheet for mutiple lists, each for separate periods.
Not all income or expense cells need to have values. Put descriptions in
column A for each income/expense category, and the totals. Here's what it
will look like:

1 Period1 Period2
2 Income1 0.00 0.00
3 Income2 10.00 15.00
4 Income3 10.00 20.00
...
9 Total Income 20.00 35.00 (Formula is: "=SUM(Income)"
10 Expense1 2.00 0.00
11 Expense2 7.00 10.00
...
21 Total Expense 9.00 10.00 (Formula is: "=SUM(Expenses)"
22 Net Income 11.00 25.00 (Formula is:
"=TotalIncome-TotalExpenses")

If you need more help then post back to email a sample file.

HTH
Garry

Excell named cells or labeled cells question
 

AFAIK The NameBox only displays the name for absolute references. You can
still select the name in the NameBox dropdown. The referenced cell(s) will be
selected relevent to the position of the active cell when the name was
selected.

As you say, on "a complex spreadsheet...", the column and row labels should
serve to identify what cells are where. (ie. Period1, Income1 indicates B2
in my example) You could also give them names in a similar fashion. For
example:

In the Define Name dialog:
With B2 selected
NameBox entry: SheetName!Period1
RefersTo entry: =SheetName!$B2
(this is now column-absolute, row-relative. Repeat for all columns)

NameBox entry: SheetName!Income1
RefersTo entry: =SheetName!B$2
(this is now column-relative, row absolute)

HTH
Garry
---

" wrote:

Ok, thanks Gary. I guess what threw me is i was hoping i could still
identify an "Income" cell by name visually still. FOr instance, when i
click on the income cell, regardless if it is in the first or second
column, it would be nice to see what it's name is.

In a complex spread sheet (like the one i have), it makes it quite a
bit easier to be able to quickly look back and determine what cells
contain what.

i suppose we can't have it all though...thanks once more for your help.
I really appreciate it.