Auto Adjusting # of rows between 2 worksheets

  • Thread starter Thread starter Kevin McKellar
  • Start date Start date
K

Kevin McKellar

I have a spreadsheet where I import daily data into
worksheet A. Sometimes there will be 5 rows of data and
sometimes 1000+. Then inside the spreadsheet I have
Worksheet B that runs some formulas based on Worksheet A.

My question is how do I get Worksheet B to automatically
extend or reduce the # of rows to match Worksheet A?

Please help or give me some direction! thanks
 
hi,
yes i looked at dynamic ranges but could not get it to
work.

basically Worksheet A is a series of $$ and dates.
Sometimes there will be 50 rows and other times just 4.

Worksheet B's formulas are basically subtraction (dates -
dates or $ - $). The problem is if yesterday it was 50
rows and today Worksheet A is 5 rows then my formulas in
Worksheet B for row 6 - 50 are all errors because there
is no data in Worksheet A anymore. (my pivot charts also
then show the errors).

I would like Worksheet B to automatically "reduce" the
number of rows it preforms functions on to be exactly
what Worksheet A has.

If you have any advice I would be greatful
 
I see 2 (maybe 3) choices:

1. Copy your formulas down as far as the expected max.
number of rows of data and embed your formulas within the
formula:

=IF(A30="","",your_formula)

Of course the downside to this is you'll eat up a lot
more processing power. Also, the expected max. number of
rows could change.

2. You could run a macro that copies the formulas down
from row 1 to the last filled row seen on worksheet A.
This would probably be the best method but requires VBA.
I could help with option #2, but you'll have to provide
some actual formulas and describe your worksheet layouts.

3. Dynamic ranges are also feasible, depending on what
your formulas are trying to accomplish.

HTH
Jason
Atlanta, GA
 
Back
Top