Data is imported to a sheet labeled "summary" from a sheet labeled "detail".
(Particular cells will sum all of the rows containing dates that fall between
a particular week - this works fine.) My problem is that whenever I update
the "detail" sheet, the cell references that point to a particular data range
always change themselves, on both the "detail" and "summary" sheets.

For instance, as soon as I update the values on my "detail" sheet, (all
values are a data query imported from a Peach Tree Excel Export file - this
works fine), a cell refering to "$L$5:$L$1000" will change to "$L:$4:$L$992"
for no reason (it seems). This can result in a VALUE error.

I want to stop this from happening, can I do this, or is there a better way
to do one of the things I mentioned above? (I need the data to flow in a
chain because it gets manipulated between the PeachTree Excel export file and
the "summary" sheet. This is what the "detail" sheet is for: give each row an
additional value of "reimbursable" or "non-reimbursable" from a drop down
list.)

Thank you for your time, Marc.

Some other things that might be helpful to know:

1. the "detail" and "summary" sheets are in the same workbook. Only the
PeachTree export file is external.
2. I used the SUMPRODUCT function coupled with absolute references to ranges
on another sheet to capture the dates. I tried to make them all uniform, but
to no avail because the references change themselves.
3. There must be something I can use in place of the colon in "$L$5:$L$1000"
like a "+" or a range name instead of reference.

"MarcP" wrote:

Data is imported to a sheet labeled "summary" from a sheet labeled "detail".
(Particular cells will sum all of the rows containing dates that fall between
a particular week - this works fine.) My problem is that whenever I update
the "detail" sheet, the cell references that point to a particular data range
always change themselves, on both the "detail" and "summary" sheets.

For instance, as soon as I update the values on my "detail" sheet, (all
values are a data query imported from a Peach Tree Excel Export file - this
works fine), a cell refering to "$L$5:$L$1000" will change to "$L:$4:$L$992"
for no reason (it seems). This can result in a VALUE error.

I want to stop this from happening, can I do this, or is there a better way
to do one of the things I mentioned above? (I need the data to flow in a
chain because it gets manipulated between the PeachTree Excel export file and
the "summary" sheet. This is what the "detail" sheet is for: give each row an
additional value of "reimbursable" or "non-reimbursable" from a drop down
list.)

Thank you for your time, Marc.

Just a shot in the dark as this is not an area I know much about, but if
you're doing a Data, Import, one of the Data Range Properties (second button
on the External Data toolbar) controls what happens when the number of rows
of data changes on a refresh. The first option deletes unused rows, which
might cause formulas referencing the range to change.

--
Jim
"MarcP" wrote in message
...
| Some other things that might be helpful to know:
|
| 1. the "detail" and "summary" sheets are in the same workbook. Only the
| PeachTree export file is external.
| 2. I used the SUMPRODUCT function coupled with absolute references to
ranges
| on another sheet to capture the dates. I tried to make them all uniform,
but
| to no avail because the references change themselves.
| 3. There must be something I can use in place of the colon in
"$L$5:$L$1000"
| like a "+" or a range name instead of reference.
|
| "MarcP" wrote:
|
| Data is imported to a sheet labeled "summary" from a sheet labeled
"detail".
| (Particular cells will sum all of the rows containing dates that fall
between
| a particular week - this works fine.) My problem is that whenever I
update
| the "detail" sheet, the cell references that point to a particular data
range
| always change themselves, on both the "detail" and "summary" sheets.
|
| For instance, as soon as I update the values on my "detail" sheet, (all
| values are a data query imported from a Peach Tree Excel Export file -
this
| works fine), a cell refering to "$L$5:$L$1000" will change to
"$L:$4:$L$992"
| for no reason (it seems). This can result in a VALUE error.
|
| I want to stop this from happening, can I do this, or is there a better
way
| to do one of the things I mentioned above? (I need the data to flow in a
| chain because it gets manipulated between the PeachTree Excel export
file and
| the "summary" sheet. This is what the "detail" sheet is for: give each
row an
| additional value of "reimbursable" or "non-reimbursable" from a drop
down
| list.)
|
| Thank you for your time, Marc.

Jim's shot in the dark seems the correct one - and one that you can give a
try. Put a couple of markers in cells that are not affected by the import and
see if they change position - ie find out if indeed rows are deleted.

"Jim Rech" wrote:

Just a shot in the dark as this is not an area I know much about, but if
you're doing a Data, Import, one of the Data Range Properties (second button
on the External Data toolbar) controls what happens when the number of rows
of data changes on a refresh. The first option deletes unused rows, which
might cause formulas referencing the range to change.

--
Jim
"MarcP" wrote in message
...
| Some other things that might be helpful to know:
|
| 1. the "detail" and "summary" sheets are in the same workbook. Only the
| PeachTree export file is external.
| 2. I used the SUMPRODUCT function coupled with absolute references to
ranges
| on another sheet to capture the dates. I tried to make them all uniform,
but
| to no avail because the references change themselves.
| 3. There must be something I can use in place of the colon in
"$L$5:$L$1000"
| like a "+" or a range name instead of reference.
|
| "MarcP" wrote:
|
| Data is imported to a sheet labeled "summary" from a sheet labeled
"detail".
| (Particular cells will sum all of the rows containing dates that fall
between
| a particular week - this works fine.) My problem is that whenever I
update
| the "detail" sheet, the cell references that point to a particular data
range
| always change themselves, on both the "detail" and "summary" sheets.
|
| For instance, as soon as I update the values on my "detail" sheet, (all
| values are a data query imported from a Peach Tree Excel Export file -
this
| works fine), a cell refering to "$L$5:$L$1000" will change to
"$L:$4:$L$992"
| for no reason (it seems). This can result in a VALUE error.
|
| I want to stop this from happening, can I do this, or is there a better
way
| to do one of the things I mentioned above? (I need the data to flow in a
| chain because it gets manipulated between the PeachTree Excel export
file and
| the "summary" sheet. This is what the "detail" sheet is for: give each
row an
| additional value of "reimbursable" or "non-reimbursable" from a drop
down
| list.)
|
| Thank you for your time, Marc.

This has worked well for some time and my problem is no longer an issue,
Thanks.

"rdwj" wrote:

Jim's shot in the dark seems the correct one - and one that you can give a
try. Put a couple of markers in cells that are not affected by the import and
see if they change position - ie find out if indeed rows are deleted.

"Jim Rech" wrote:

Just a shot in the dark as this is not an area I know much about, but if
you're doing a Data, Import, one of the Data Range Properties (second button
on the External Data toolbar) controls what happens when the number of rows
of data changes on a refresh. The first option deletes unused rows, which
might cause formulas referencing the range to change.

--
Jim
"MarcP" wrote in message
...
| Some other things that might be helpful to know:
|
| 1. the "detail" and "summary" sheets are in the same workbook. Only the
| PeachTree export file is external.
| 2. I used the SUMPRODUCT function coupled with absolute references to
ranges
| on another sheet to capture the dates. I tried to make them all uniform,
but
| to no avail because the references change themselves.
| 3. There must be something I can use in place of the colon in
"$L$5:$L$1000"
| like a "+" or a range name instead of reference.
|
| "MarcP" wrote:
|
| Data is imported to a sheet labeled "summary" from a sheet labeled
"detail".
| (Particular cells will sum all of the rows containing dates that fall
between
| a particular week - this works fine.) My problem is that whenever I
update
| the "detail" sheet, the cell references that point to a particular data
range
| always change themselves, on both the "detail" and "summary" sheets.
|
| For instance, as soon as I update the values on my "detail" sheet, (all
| values are a data query imported from a Peach Tree Excel Export file -
this
| works fine), a cell refering to "$L$5:$L$1000" will change to
"$L:$4:$L$992"
| for no reason (it seems). This can result in a VALUE error.
|
| I want to stop this from happening, can I do this, or is there a better
way
| to do one of the things I mentioned above? (I need the data to flow in a
| chain because it gets manipulated between the PeachTree Excel export
file and
| the "summary" sheet. This is what the "detail" sheet is for: give each
row an
| additional value of "reimbursable" or "non-reimbursable" from a drop
down
| list.)
|
| Thank you for your time, Marc.