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?
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
blush

That link doesn't work.

By the way is this linked to your post on OzGrid here.
 
Upvote 0
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.:)
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top