Filling formulas when not all rows have data

blush

New Member
Joined
Nov 25, 2005
Messages
3
I have a spreadsheet where some rows have data in each column, and some rows only in certain columns. Here's a very simplified example - the actual files usually have up to 1500 rows.

What I need to do is fill in the formulas in columns C, H, and I. H is no problem, it goes on every row. But the others only go on the first row of each sub-chunk of data. I want to be able to fill down without going through thousands of rows, deleting the in-between zeros. I did find that if I filtered out the blanks from column D, manually filling down would do that, but if I tried it via a macro, it would put the formula in every row.

Furthermore, the formula in column I takes the minimum from all the rows in each chunk. It varies, it could be two rows in one chunk and fifty in the next. I'd like to see if it's possible to have it fill down only into the appropriate rows AND figure out the correct range to take the minimum from. Again, I'd rather not go through thousands of rows adjusting the range.

Is there any way to do this?
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
blush

That link doesn't work.

By the way is this linked to your post on OzGrid here.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
blush

It's not really a problem, just really a courtey to post a link to the other forum.

I actually see the same problem with this worksheet as the one on OzGrid.

On OzGrid you stated my solution didn't work because the size of the ranges for the MIN were changing.

In both examples the size of the range is not changing.:)
 

blush

New Member
Joined
Nov 25, 2005
Messages
3
Oh! I'm sorry, I didn't know that.

As for the range - it does change, but perhaps not in my first example because I made it up too quickly and happened to make them the same size. Whoops. In actuality it COULD be the same size three or four times in a row, 4-6 is the average, but it does vary widely.

In this example they are different, it is 8 cells, then 6, then 4.

edit - nevermind, I see what you mean. haha. Ok, that is my fault too. I'll fix it.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,018
Messages
5,569,652
Members
412,286
Latest member
kychemist00
Top