Expand range based on a cell value, Excel 2016

[PhilS2520]

Hello,

In A1 I have 27.

Is it possible to make 27 (the value in cell A1) cells in Row 2 from B2 to AA2 populated with a formula?

Yes, I know how to do this in VBA, which will be my last resort, but is it possible without VBA?

Thanks for any advice.

 

[tdh777]

Hi Phil.

Sorry, you want to have <N> cells with a formula where N = the value stored in Column A for each Row - is that right ?

Generally speaking you can set formulas by dragging/copying/expanding them manually through the Excel User Interface, but to do automatically create new formulas yes you would need to use VBA.

But you might not need to do that at all. You could create formulas that don't do anything if the column number is greater than the value in A1 for example. Then you would not have to adjust the formulas when the value in A1 changed.

Out of interest, what exactly is it that you trying to achieve overall ? What is the "formula" in cells B2-AA2 (or B2-K2 if eg A1=10) going to look like ?

We might be able to achieve the final outcome you want without having to automatically create formulas at all. Please share some more info.

Cheers,
Warren K.

 

[PhilS2520]

Thanks Warren,

I am trying to populate a range of cells equal to a schedule. For example if a task takes 2 days I need two cells, but if a task takes 10 days then I need 10 cells in that same range.

So, it is kinda a timecard. I was wondering if I MUST use VBA for such a thing, like an event driven by the change in that particular cell’s value, or if I could avoid this and just use some of the super duper formulas and functions some great XLers can produce.

 

[tdh777]

Hi Phil,

I get that, but I was hoping that you would actually post a formula for the first couple of your cells in your row so we could see the best way to achieve your outcome.

Never mind, here's a generic answer, without using VBA, that may , (or may not depending on what your actual formula you need), solve your problem.

Put this formula into B2 and drag it across the row to the maximum number of cells you could need (ie 1 more than maximum value going into A1).

=if(COLUMN() <= $A$1+1, =[put your desired formula here], "")

This will give you <n> cells that have an active formula, and all the remaining cells in the row will return an empty string if the number in A1 is smaller.

If there could be multiple instances of the A1-type box in your s/sheet (specifying number of instances) and thus multiple instances of the B2-B<n> type Row underneath, then simply change the start of the formula to :

=if(COLUMN() <= $A1+1, ...

which means that you can copy the formula to other rows in your sheet as it now checks the value in Column A but 1 row above where the formulas are.

Now if your desired FORMULA itself varies depending on the value in Cell A1 then we might need to use VBA , but I can't tell you that until I see your actual formula.

Cheers,
Warren K.</n></n>