Excel 2002
I am setting up a Day End report of sales and expences to pull information
from various spreadsheets.
If the source sheet row moves the result is not accurate. For example if
within the source sheet rows are added or deleted and the "total" row moves
up or down, the report gives the wrong answer or a 0.
Is is possible to make a reference to a cell that is not static?

The formulas normally adjust when rows are deleted or added. For example if
in Cell A1 you have a formula =A40 and you add or delete rows between the 2nd
and teh 39th row the formula adjusts automatically. Am I missing something?

Alok

"k9kapers" wrote:

Excel 2002
I am setting up a Day End report of sales and expences to pull information
from various spreadsheets.
If the source sheet row moves the result is not accurate. For example if
within the source sheet rows are added or deleted and the "total" row moves
up or down, the report gives the wrong answer or a 0.
Is is possible to make a reference to a cell that is not static?

Well that is what I thought too, but it is not happening. Let me look at it
again. What is happening is that the source cell number changes from A40 to
A38 or A52 as you add or subtract rows, so if you put in A40 it becomes a
useless cell.

"Alok" wrote:

The formulas normally adjust when rows are deleted or added. For example if
in Cell A1 you have a formula =A40 and you add or delete rows between the 2nd
and teh 39th row the formula adjusts automatically. Am I missing something?

Alok

"k9kapers" wrote:

Excel 2002
I am setting up a Day End report of sales and expences to pull information
from various spreadsheets.
If the source sheet row moves the result is not accurate. For example if
within the source sheet rows are added or deleted and the "total" row moves
up or down, the report gives the wrong answer or a 0.
Is is possible to make a reference to a cell that is not static?

It seems what you want is to always use say Cell A40 in the formula
irrespective of any changes to the spreadsheet. IN that case you have to use
the following formula
=INDIRECT("A40")

"k9kapers" wrote:

Well that is what I thought too, but it is not happening. Let me look at it
again. What is happening is that the source cell number changes from A40 to
A38 or A52 as you add or subtract rows, so if you put in A40 it becomes a
useless cell.

"Alok" wrote:

The formulas normally adjust when rows are deleted or added. For example if
in Cell A1 you have a formula =A40 and you add or delete rows between the 2nd
and teh 39th row the formula adjusts automatically. Am I missing something?

Alok

"k9kapers" wrote:

Excel 2002
I am setting up a Day End report of sales and expences to pull information
from various spreadsheets.
If the source sheet row moves the result is not accurate. For example if
within the source sheet rows are added or deleted and the "total" row moves
up or down, the report gives the wrong answer or a 0.
Is is possible to make a reference to a cell that is not static?

OK, I think I understand but I am still doing something wrong or am missing
something in the string as it sends back a #ref error, both workbooks are
open.

Here is where I am at:
=INDIRECT("'[Daily Accruals.xls]Sept Accruals'!$E$26+'[Daily
Accruals.xls]Oct Accruals'!$E$26")

The current cell where I want the final answer is I1086. (not in the above
referece)

The cell where I am getting the information are on 2 sheets
1.[Daily Accruals.xls]Sept Accruals'!$E$26
2.[Daily Accruals.xls]Oct Accruals'!$E$26
These 2 sheets are the ones whose cells move as you insert or delete rows.

Hopefully you can follow this. :/

"Alok" wrote:

It seems what you want is to always use say Cell A40 in the formula
irrespective of any changes to the spreadsheet. IN that case you have to use
the following formula
=INDIRECT("A40")

"k9kapers" wrote:

Well that is what I thought too, but it is not happening. Let me look at it
again. What is happening is that the source cell number changes from A40 to
A38 or A52 as you add or subtract rows, so if you put in A40 it becomes a
useless cell.

"Alok" wrote:

The formulas normally adjust when rows are deleted or added. For example if
in Cell A1 you have a formula =A40 and you add or delete rows between the 2nd
and teh 39th row the formula adjusts automatically. Am I missing something?

Alok

"k9kapers" wrote:

Excel 2002
I am setting up a Day End report of sales and expences to pull information
from various spreadsheets.
If the source sheet row moves the result is not accurate. For example if
within the source sheet rows are added or deleted and the "total" row moves
up or down, the report gives the wrong answer or a 0.
Is is possible to make a reference to a cell that is not static?

Hi,
What you have is pretty close. I beleive it should be

=INDIRECT("[Daily Accruals.xls]Sept Accruals'!$E$26")+Indirect("[Daily
Accruals.xls]Oct Accruals'!$E$26")

"k9kapers" wrote:

OK, I think I understand but I am still doing something wrong or am missing
something in the string as it sends back a #ref error, both workbooks are
open.

Here is where I am at:
=INDIRECT("'[Daily Accruals.xls]Sept Accruals'!$E$26+'[Daily
Accruals.xls]Oct Accruals'!$E$26")

The current cell where I want the final answer is I1086. (not in the above
referece)

The cell where I am getting the information are on 2 sheets
1.[Daily Accruals.xls]Sept Accruals'!$E$26
2.[Daily Accruals.xls]Oct Accruals'!$E$26
These 2 sheets are the ones whose cells move as you insert or delete rows.

Hopefully you can follow this. :/

"Alok" wrote:

It seems what you want is to always use say Cell A40 in the formula
irrespective of any changes to the spreadsheet. IN that case you have to use
the following formula
=INDIRECT("A40")

"k9kapers" wrote:

Well that is what I thought too, but it is not happening. Let me look at it
again. What is happening is that the source cell number changes from A40 to
A38 or A52 as you add or subtract rows, so if you put in A40 it becomes a
useless cell.

"Alok" wrote:

The formulas normally adjust when rows are deleted or added. For example if
in Cell A1 you have a formula =A40 and you add or delete rows between the 2nd
and teh 39th row the formula adjusts automatically. Am I missing something?

Alok

"k9kapers" wrote:

Excel 2002
I am setting up a Day End report of sales and expences to pull information
from various spreadsheets.
If the source sheet row moves the result is not accurate. For example if
within the source sheet rows are added or deleted and the "total" row moves
up or down, the report gives the wrong answer or a 0.
Is is possible to make a reference to a cell that is not static?