# Auto-Filling Based on Previous Cells

1. ## Auto-Filling Based on Previous Cells

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

2. ## Re: Auto-Filling Based on Previous Cells

Welcome to the forum.

I think this does what you want. Put it in B3 and drag across.
Formula:
`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.

3. ## Re: Auto-Filling Based on Previous Cells

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.

4. ## Re: Auto-Filling Based on Previous Cells

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.

5. ## Re: Auto-Filling Based on Previous Cells

B3 array formula , Drag right

HTML Code:

There are currently 1 users browsing this thread. (0 members and 1 guests)