Autofill a dynamic range with a formula.

[Daniel Uribe]

I have created a dynamic named range using the offset function, but now I want to fill that range with a formula, so that if the dynamic named range consist of 20 cells, the 20 cells will have the formula, and if I change thenumber of cells to 1000, the 1000 cells would have the formula. Is it possible to do this without using VBA?

Thank you.

 

[Ben McClave]

Hi Daniel,

You could try a few things.

First, you could use the GoTo Reference dialog (F5 on your keyboard) and type the named range name into the Reference field. This will highlight the entire range, allowing you to then paste the formula (or type it and use CTRL+Enter to fill the selected range).

Alternatively, you could enter the formula in as many cells as you think you'd ever need and then use conditional formatting to change the font color and background for any cells outside the named range to white (i.e. format the unused part of the range to look invisible).

Another alternative is to include your formula in an IF statement. For example, if your offset is set to 1000 rows, then your formula might read:

=IF(ROW()>1000, "", Your_Formula)

Then, copy this formula as far down the page as you need to ensure it picksup the entire range.

If none of these work for you, then VBA might be the way to go.

Ben