Taking in account additional rows

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
dguillett1@austin.rr.com
"JB12" <JB12@discussions.microsoft.com> wrote in message
news:D215B25F-58FE-4488-946D-9ECFDACF039D@microsoft.com...
>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
> dguillett1@austin.rr.com
> "JB12" <JB12@discussions.microsoft.com> wrote in message
> news:D215B25F-58FE-4488-946D-9ECFDACF039D@microsoft.com...
> >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
dguillett1@austin.rr.com
"JB12" <JB12@discussions.microsoft.com> wrote in message
news:475A2621-0505-4187-928E-693F3AAD28F3@microsoft.com...
> 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
>> dguillett1@austin.rr.com
>> "JB12" <JB12@discussions.microsoft.com> wrote in message
>> news:D215B25F-58FE-4488-946D-9ECFDACF039D@microsoft.com...
>> >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??
>>
>>
>>