I currently have a spreadsheet that simply consists of data that I dump in it
from another internet based application. From there I have a second
worksheet that references certain columns/rows in the first spreadsheet to
extract just the data I need. The next worksheet I have ranks the
information and then displays it in an organized fashion on a different
worksheet. The situation I am running into is that the data I extract from
the internet based application can have a different # of rows based on the
day I pull it and my formulas only work if the # of rows on the first sheet
are the same. For instance, if I have 700 rows today and I pull it tomorrow
and there are 701, the 701st row is not taken into consideration when ranking
the data and therefore my information displayed is incorrect. Is there a way
to have my reference cells recognize there is an extra row and update all my
formulas to account for the extra row as well??

You could use defined names for the ranges or use a formula that determines
the row.
Here we are looking for a number larger than possible in column L

=SUM(INDIRECT("L2:L"&MATCH(9999999999,L:L)))

Don Guillett
SalesAid Software

"JB12" wrote in message
...
I currently have a spreadsheet that simply consists of data that I dump in
it
from another internet based application. From there I have a second
worksheet that references certain columns/rows in the first spreadsheet to
extract just the data I need. The next worksheet I have ranks the
information and then displays it in an organized fashion on a different
worksheet. The situation I am running into is that the data I extract
from
the internet based application can have a different # of rows based on the
day I pull it and my formulas only work if the # of rows on the first
sheet
are the same. For instance, if I have 700 rows today and I pull it
tomorrow
and there are 701, the 701st row is not taken into consideration when
ranking
the data and therefore my information displayed is incorrect. Is there a
way
to have my reference cells recognize there is an extra row and update all
my
formulas to account for the extra row as well??

Don,

I am not sure exactly what you mean by using defined names for the range. I
am sorry if that is a simple concept, I am just not aware of exactly how to
do so. I tried the formula you provided and I might be applying it
incorrectly b/c it is giving me a ref# error. I am not sure I expalined
myself correctly so I'm going to try pasting some of the data to see if that
makes sense:

Group ID Action no.
2150 1
2150 2
2150 3
2150 4
2150 5
2150 7
2150 8
2150 9

Those are columns B/C from my data dump. Then in another worksheet they
keep the same values, but are referenced by =sheet1B2, etc....I am having a
hard time figuring out how to make my sheet2 take into account any extra
fields w/o hard coding the cells to an additional 50 and getting NA# for
anything w/o information. That then causes my ranking tab which uses a
vlookup to be hard coded as well, which causes my display tab to show NA# as
the bottom x# of cells. Did I just not apply your formula correctly? Thanks
for your help and I hope that makes sense.

"Don Guillett" wrote:

You could use defined names for the ranges or use a formula that determines
the row.
Here we are looking for a number larger than possible in column L

=SUM(INDIRECT("L2:L"&MATCH(9999999999,L:L)))

Don Guillett
SalesAid Software

"JB12" wrote in message
...
I currently have a spreadsheet that simply consists of data that I dump in
it
from another internet based application. From there I have a second
worksheet that references certain columns/rows in the first spreadsheet to
extract just the data I need. The next worksheet I have ranks the
information and then displays it in an organized fashion on a different
worksheet. The situation I am running into is that the data I extract
from
the internet based application can have a different # of rows based on the
day I pull it and my formulas only work if the # of rows on the first
sheet
are the same. For instance, if I have 700 rows today and I pull it
tomorrow
and there are 701, the 701st row is not taken into consideration when
ranking
the data and therefore my information displayed is incorrect. Is there a
way
to have my reference cells recognize there is an extra row and update all
my
formulas to account for the extra row as well??

I guess it is not clear to me what you want. If you like, you may send me a
SMALL workbook with a clear explanation of what you are trying to do and an
example of what is correct.

--
Don Guillett
SalesAid Software

"JB12" wrote in message
...
Don,

I am not sure exactly what you mean by using defined names for the range.
I
am sorry if that is a simple concept, I am just not aware of exactly how
to
do so. I tried the formula you provided and I might be applying it
incorrectly b/c it is giving me a ref# error. I am not sure I expalined
myself correctly so I'm going to try pasting some of the data to see if
that
makes sense:

Group ID Action no.
2150 1
2150 2
2150 3
2150 4
2150 5
2150 7
2150 8
2150 9

Those are columns B/C from my data dump. Then in another worksheet they
keep the same values, but are referenced by =sheet1B2, etc....I am having
a
hard time figuring out how to make my sheet2 take into account any extra
fields w/o hard coding the cells to an additional 50 and getting NA# for
anything w/o information. That then causes my ranking tab which uses a
vlookup to be hard coded as well, which causes my display tab to show NA#
as
the bottom x# of cells. Did I just not apply your formula correctly?
Thanks
for your help and I hope that makes sense.

"Don Guillett" wrote:

You could use defined names for the ranges or use a formula that
determines
the row.
Here we are looking for a number larger than possible in column L

=SUM(INDIRECT("L2:L"&MATCH(9999999999,L:L)))

Don Guillett
SalesAid Software

"JB12" wrote in message
...
I currently have a spreadsheet that simply consists of data that I dump
in
it
from another internet based application. From there I have a second
worksheet that references certain columns/rows in the first spreadsheet
to
extract just the data I need. The next worksheet I have ranks the
information and then displays it in an organized fashion on a different
worksheet. The situation I am running into is that the data I extract
from
the internet based application can have a different # of rows based on
the
day I pull it and my formulas only work if the # of rows on the first
sheet
are the same. For instance, if I have 700 rows today and I pull it
tomorrow
and there are 701, the 701st row is not taken into consideration when
ranking
the data and therefore my information displayed is incorrect. Is there
a
way
to have my reference cells recognize there is an extra row and update
all
my
formulas to account for the extra row as well??