software

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(quot;L2:Lquot;amp;MATCH(9999999999,L:L)))

Don Guillett
SalesAid Software

quot;JB12quot; gt; wrote in message
news
gt;I currently have a spreadsheet that simply consists of data that I dump in
gt;it
gt; from another internet based application. From there I have a second
gt; worksheet that references certain columns/rows in the first spreadsheet to
gt; extract just the data I need. The next worksheet I have ranks the
gt; information and then displays it in an organized fashion on a different
gt; worksheet. The situation I am running into is that the data I extract
gt; from
gt; the internet based application can have a different # of rows based on the
gt; day I pull it and my formulas only work if the # of rows on the first
gt; sheet
gt; are the same. For instance, if I have 700 rows today and I pull it
gt; tomorrow
gt; and there are 701, the 701st row is not taken into consideration when
gt; ranking
gt; the data and therefore my information displayed is incorrect. Is there a
gt; way
gt; to have my reference cells recognize there is an extra row and update all
gt; my
gt; 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 IDAction no.
21501
21502
21503
21504
21505
21507
21508
21509

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.

quot;Don Guillettquot; wrote:

gt; You could use defined names for the ranges or use a formula that determines
gt; the row.
gt; Here we are looking for a number larger than possible in column L
gt;
gt; =SUM(INDIRECT(quot;L2:Lquot;amp;MATCH(9999999999,L:L)))
gt;
gt; Don Guillett
gt; SalesAid Software
gt;
gt; quot;JB12quot; gt; wrote in message
gt; news
gt; gt;I currently have a spreadsheet that simply consists of data that I dump in
gt; gt;it
gt; gt; from another internet based application. From there I have a second
gt; gt; worksheet that references certain columns/rows in the first spreadsheet to
gt; gt; extract just the data I need. The next worksheet I have ranks the
gt; gt; information and then displays it in an organized fashion on a different
gt; gt; worksheet. The situation I am running into is that the data I extract
gt; gt; from
gt; gt; the internet based application can have a different # of rows based on the
gt; gt; day I pull it and my formulas only work if the # of rows on the first
gt; gt; sheet
gt; gt; are the same. For instance, if I have 700 rows today and I pull it
gt; gt; tomorrow
gt; gt; and there are 701, the 701st row is not taken into consideration when
gt; gt; ranking
gt; gt; the data and therefore my information displayed is incorrect. Is there a
gt; gt; way
gt; gt; to have my reference cells recognize there is an extra row and update all
gt; gt; my
gt; gt; formulas to account for the extra row as well??
gt;
gt;
gt;

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

quot;JB12quot; gt; wrote in message
...
gt; Don,
gt;
gt; I am not sure exactly what you mean by using defined names for the range.
gt; I
gt; am sorry if that is a simple concept, I am just not aware of exactly how
gt; to
gt; do so. I tried the formula you provided and I might be applying it
gt; incorrectly b/c it is giving me a ref# error. I am not sure I expalined
gt; myself correctly so I'm going to try pasting some of the data to see if
gt; that
gt; makes sense:
gt;
gt; Group ID Action no.
gt; 2150 1
gt; 2150 2
gt; 2150 3
gt; 2150 4
gt; 2150 5
gt; 2150 7
gt; 2150 8
gt; 2150 9
gt;
gt; Those are columns B/C from my data dump. Then in another worksheet they
gt; keep the same values, but are referenced by =sheet1B2, etc....I am having
gt; a
gt; hard time figuring out how to make my sheet2 take into account any extra
gt; fields w/o hard coding the cells to an additional 50 and getting NA# for
gt; anything w/o information. That then causes my ranking tab which uses a
gt; vlookup to be hard coded as well, which causes my display tab to show NA#
gt; as
gt; the bottom x# of cells. Did I just not apply your formula correctly?
gt; Thanks
gt; for your help and I hope that makes sense.
gt;
gt; quot;Don Guillettquot; wrote:
gt;
gt;gt; You could use defined names for the ranges or use a formula that
gt;gt; determines
gt;gt; the row.
gt;gt; Here we are looking for a number larger than possible in column L
gt;gt;
gt;gt; =SUM(INDIRECT(quot;L2:Lquot;amp;MATCH(9999999999,L:L)))
gt;gt;
gt;gt; Don Guillett
gt;gt; SalesAid Software
gt;gt;
gt;gt; quot;JB12quot; gt; wrote in message
gt;gt; news
gt;gt; gt;I currently have a spreadsheet that simply consists of data that I dump
gt;gt; gt;in
gt;gt; gt;it
gt;gt; gt; from another internet based application. From there I have a second
gt;gt; gt; worksheet that references certain columns/rows in the first spreadsheet
gt;gt; gt; to
gt;gt; gt; extract just the data I need. The next worksheet I have ranks the
gt;gt; gt; information and then displays it in an organized fashion on a different
gt;gt; gt; worksheet. The situation I am running into is that the data I extract
gt;gt; gt; from
gt;gt; gt; the internet based application can have a different # of rows based on
gt;gt; gt; the
gt;gt; gt; day I pull it and my formulas only work if the # of rows on the first
gt;gt; gt; sheet
gt;gt; gt; are the same. For instance, if I have 700 rows today and I pull it
gt;gt; gt; tomorrow
gt;gt; gt; and there are 701, the 701st row is not taken into consideration when
gt;gt; gt; ranking
gt;gt; gt; the data and therefore my information displayed is incorrect. Is there
gt;gt; gt; a
gt;gt; gt; way
gt;gt; gt; to have my reference cells recognize there is an extra row and update
gt;gt; gt; all
gt;gt; gt; my
gt;gt; gt; formulas to account for the extra row as well??
gt;gt;
gt;gt;
gt;gt;