Taking in account additional rows

[Guest]

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 Guillett]

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
(e-mail address removed)

 

[Guest]

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]

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.