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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,344
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,344
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,118
Messages
5,835,495
Members
430,360
Latest member
Ajonode

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
Top