Cell Absolute $ References Keep Changing themselves

[Guest]

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.

 

[Guest]

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.

 

[Jim Rech]

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
| 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.

 

[Guest]

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.

 

[Guest]

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