+ Reply to Thread
Results 1 to 4 of 4

How to drag/fill a column with a 3D formula

  1. #1
    Registered User
    Join Date
    07-17-2015
    Location
    London. England
    MS-Off Ver
    Excel 2013
    Posts
    3

    How to drag/fill a column with a 3D formula

    Thanks to JBeauclaire here http://www.excelforum.com/excel-form...le-sheets.html I have the right formula, but can't fill the column.

    In my monthly sheets columns B and C have Qty and UnitPrice. My annual summary sheet has the total Qty in B3, and I have used his method in C3 to find the average Unit Price for the year:

    =SUMPRODUCT(N(INDIRECT("'"&Months&"'!B3")),N(INDIRECT("'"&Months&"'!C3")))/B3

    My problem is how to replicate this formula down column C for all products. When dragged the row number does not increment - how can I do that?

  2. #2
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,869

    Re: How to drag/fill a column with a 3D formula

    Maybe this?

    =SUMPRODUCT(N(INDIRECT("'"&Months&"'!B"&ROW(A3))),N(INDIRECT("'"&Months&"'!C"&ROW(A3))))/B3
    Cheers!
    Deep Dave

  3. #3
    Registered User
    Join Date
    07-17-2015
    Location
    London. England
    MS-Off Ver
    Excel 2013
    Posts
    3

    Re: How to drag/fill a column with a 3D formula

    Yes, thanks

  4. #4
    Forum Expert NeedForExcel's Avatar
    Join Date
    03-16-2013
    Location
    Pune, India
    MS-Off Ver
    Excel 2016:2019
    Posts
    3,869

    Re: How to drag/fill a column with a 3D formula

    Glad I could help..

+ 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. [SOLVED] Drag fill not recognising formula pattern
    By steveneedshelp in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 03-12-2015, 09:06 AM
  2. [SOLVED] Can I make excel automatically drag-down a formula as you fill in data?
    By Yoshi64 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 01-16-2014, 07:28 PM
  3. Help With Correct Drag Formula In Order To Fill Blank Cells
    By Seán_Ireland in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-19-2013, 05:43 PM
  4. [SOLVED] Drag/Fill column while skipping multiple reference rows in another worksheet
    By chandlerrw in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-22-2013, 07:59 PM
  5. [SOLVED] Identify the last filled row in Column A and then drag formula in Column B till the same
    By bonny24tycoon in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-13-2012, 10:50 AM
  6. Seconday Worksheet Reference Formula Drag Fill incrementing Issue
    By DavidCharles1224 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-08-2010, 04:05 PM
  7. how to fill formula in any cell and drag it?
    By vumian in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-10-2006, 10:46 AM

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