Need to drag a cell reference down but increment columns

replace A$3 with OFFSET($A$3, 0, ROW(A1)-1), thus incrementing (1) column 0 rows as you drag down

Sorry I can't check your entire formula without a sample workbook

Thanks, carsto.

This does produce the correct values but it's not quite what I need. I did the same with the index function:
=INDEX($A$3:$Z$3,1,ROW(A1))

Unfortunately, I'm a new member and there appears to be restrictions on attachments or uploading a link to the workbook.

In summary, I think I need to drag down cell references and not values.

-paul

Hi Ali,

Sorry, I did reply to your post but I must have messed up somehow.

The result I am looking for is on rows 10-12. Rows 5-7 is my attempt.

Hopefully, there'll be an attachment this time.

-paul

Thanks, Ali,

Row 3 is required. The challenge is the frequency between the number sequences in Row 3 is a variable.

Scenario:
Row 1 is ad spend at one location (which reduces over time). Row 3 is instances of a new location starting. The columns would be months. There'll be a sum of the months to produce a monthly total of ad spend.

There's probably better logic to achieve this but in this method, Row 6 needs to offset its use of Row 3 by -1, Row 7 by -2 - we can shift the whole thing way to the right if we need to reference cells currently unavailable on the left.

For instance, F6 needs to be multiplied by E3, G6 by E3 to produce the example on Rows 11 and 12.

I'm not expert level, there may be a much easier way of achieving the same result.

Hope this helps.

All,

Sorry for the confusion in the earlier workbook I posted. Ignore that going forward. I was also incorrect (in my original post) that dragging down A$3 to make B$3, C$3 etc would work. It would not. Actually the column letters would have needed to go in the opposite direction.

I came up with a solution but it's far from ideal. Basically I used the INDEX function (CELL BP11 of attached worksheet "Ad Spend" in the workbook "advertising-array-v1.1.xlsx) This cell can be dragged up or down to fill the array.

Although this works, the solution breaks if rows or columns are subsequently inserted or deleted.

Here's a brief explanation of the attached file:

On the Ad Spend worksheet:

Row 2: Months (just numbers)
Row 5: represents Ad Spend on one location per month. These numbers are controlled by variables on the Variables worksheet. Cell BP5 (Variables!$B$10) is the first in the sequence, (Variables!$B$11) is used at month 24 on onwards when the location is mature. Between BP5 and CM5 is a gradient reduction of ad spend.
Row 7: Running total of locations that are open per month. These numbers are controlled by variables on the Variables worksheet, Th variables are: number of new locations (Variables!$B$15) and frequency of opening (Variables!$B$14) in months 1-24 and (Variables!$B$19) and frequency of opening (Variables!$B$14) in months 24 on onwards
Row 9: The increment count of new locations per month in order to calculate rows 11-70
Row 72: Sum of all 60 months ad spend for each month
Row 74: adds the additional location whose instance initiates the process

Cell BP11: The cell where the topic of this thread is required

If anyone has a better solution, I would be happy to learn. Maybe my solution will be of use to someone else.

Here's the formula:
=INDEX($A$5:$DW$5,ROW(A68))*INDEX(A$9:DW$9,0,72-ROWS($A$1:$A4))