+ Reply to Thread
Results 1 to 5 of 5

Auto-Filling Based on Previous Cells

  1. #1
    Registered User
    Join Date
    09-04-2020
    Location
    United States
    MS-Off Ver
    Office 365 2020
    Posts
    4

    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
    Attached Images Attached Images

  2. #2
    Forum Expert
    Join Date
    03-20-2015
    Location
    Primarily UK, sometimes NL (COVID allowing)
    MS-Off Ver
    Work: Office 365 / Home: Office 2010
    Posts
    2,402

    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: copy to clipboard
    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.
    Regards,
    Aardigspook

    Recently moved house, internationally, during COVID (!) so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
    If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
    If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
    You don't need to give me rep if I helped, but a thank-you is nice.

  3. #3
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365
    Posts
    2,595

    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. #4
    Registered User
    Join Date
    09-04-2020
    Location
    United States
    MS-Off Ver
    Office 365 2020
    Posts
    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.
    Attached Files Attached Files

  5. #5
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Malaysia
    MS-Off Ver
    365 & WPS(2022)
    Posts
    2,036

    Re: Auto-Filling Based on Previous Cells

    B3 array formula , Drag right

    HTML Code: 
    =MMULT(N(OFFSET($B2,COLUMN(C1)-{0,1,2}>3,MOD({10,9,8}+COLUMN(A1),12))),$B5:$B7)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Dependent Dropdowns & Auto Filling Cells based on table
    By Mic_ExcelNoob in forum Excel General
    Replies: 9
    Last Post: 08-03-2020, 04:01 AM
  2. Replies: 4
    Last Post: 12-01-2019, 06:49 AM
  3. Auto filling in cells based on other column drop down selections
    By mhewitson15 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-21-2016, 11:16 AM
  4. Auto-populate lookup formula based on previous cells
    By Homeslice01 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-23-2013, 11:09 PM
  5. Copying cells from one sheet to another then filling down based on previous column
    By mahowarth in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-07-2013, 08:18 AM
  6. Auto filling cells based on drop down menu selection
    By Candace84 in forum Excel General
    Replies: 1
    Last Post: 11-19-2010, 02:59 AM
  7. Replies: 3
    Last Post: 11-04-2010, 10:00 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1