Auto-Filling Based on Previous Cells

[Danielllmzmz]

Hello guys, I hope you are doing well!

I have a question for which I am not sure how I am supposed to put it in words, but I will try.

So I have this table (screenshot attached), and the first object in LINE 2 (i.e. January), is based on the three previous cells (OCT, NOV, and DEC in LINE 1).

The equation for cell B3 is as follows:

(M2 * $B$5) + (L2 * $B$6) + (K2 * $B$7).

BUT, when I copy the formula into the next cell (C3), the cell values the formula refer to become M2, N2, and O2 - since the formula assumes there are values in those cells.

How can I write my equation so that when I COPY the formula in cell B3 into cell C3 through cell M3, the cell values will refer to previous cells and not following cells?

In other words, C3 will be = (B3 * $B$5) + (M2 * $B$6) + (L2 * B$7$)...and so on for each cell from C3 to M3?

I am sorry if it's a bit confusing - I tried my best

[Aardigspook]

Welcome to the forum.

I think this does what you want. Put it in B3 and drag across.

Please Login or Register  to view this content.

It works by checking the column number (A=1, B=2, etc). If that's greater than 4 (which it will be for columns E onwards), then the formula looks to the left by 1, then 2, then 3 cells to multiply by B5, B6 and B7 respectively. If the column is 4 (col D), it looks to the left for B5 and B6, then looks up a row and along for the B7 multiplier. The same logic applies for column numbers 3 and 2 (C and B).

It's a bit long-winded; if I had more time I might be able to come up with something more elegant, but it does the job I think.

Hope that helps.

[Limor_OP]

Hi,
you will need to upload the sample workbook in excel cause no one likes to create the data from scratch

Look at the yellow banner for more instructions.

[Danielllmzmz]

Hey guys,

First of all, I'd like to thank you! I have been extremely busy, so I couldn't really take care of that.
Aardigspook, I took a look at your worksheet- it looks great sir!

I have also attached my worksheet so that you can directly work on it. I would greatly appreciate it, lads.

Thanks again.

[wk9128]

B3 array formula , Drag right