+ Reply to Thread
Results 1 to 2 of 2

Dynamic Formula Help. Need Sheet name to be a Month and Fill down

  1. #1
    Registered User
    Join Date
    08-31-2016
    Location
    Australia
    MS-Off Ver
    Office 2016
    Posts
    3

    Dynamic Formula Help. Need Sheet name to be a Month and Fill down

    I have a main workbook below, which pulls data from another workbook called data.xlsx.
    Excel 2016 (Windows) 32 bit
    A
    B
    1
    Date Number
    2
    Friday, 1 January 2016
    500.00
    3
    Thursday, 5 May 2016
    300.00
    4
    Saturday, 9 July 2016
    400.00
    Sheet: workbook1.xlsx


    The formula I use in B2 is:

    =INDEX([data.xlsx]Sheet1!$A$2:$A$30,MATCH(MIN(IF([data.xlsx]Sheet1!$D$2:$D$30=$A2,[data.xlsx]Sheet1!$C$2:$C$30)),IF([data.xlsx]Sheet1!$D$2:$D$30=$A2,[data.xlsx]Sheet1!$C$2:$C$30),0))

    The problem I am now having with this formula is that my data.xlsx has exceded the excel row limit of 1mil+
    So I have now broken my data.xlsx Workbook into Sheets named Jan, Feb, Dec etc inside data.xlsx

    Is there anyway I can have my formula when it fills down column B change the "Sheet1" to the Month in column A ??

    data.xlsx
    A
    B
    C
    D
    1
    Number Time Date
    2
    500
    5:00:00
    Friday, 1 January 2016
    3
    100
    5:00:00
    Friday, 1 January 2016
    4
    200
    5:00:00
    Friday, 1 January 2016
    5
    300
    5:00:00
    Friday, 1 January 2016
    6
    500
    5:00:00
    Friday, 1 January 2016
    7
    300
    5:00:00
    Thursday, 5 May 2016
    8
    500
    5:00:00
    Thursday, 5 May 2016
    9
    400
    5:00:00
    Thursday, 5 May 2016
    10
    600
    5:00:00
    Thursday, 5 May 2016
    11
    500
    5:00:00
    Thursday, 5 May 2016
    12
    400
    5:00:00
    Saturday, 9 July 2016
    13
    800
    5:00:00
    Saturday, 9 July 2016
    14
    200
    5:00:00
    Saturday, 9 July 2016
    15
    300
    5:00:00
    Saturday, 9 July 2016
    16
    600
    5:00:00
    Saturday, 9 July 2016
    Sheet: data.xlsx
    Last edited by umirin; 09-23-2016 at 09:51 AM.

  2. #2
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Dynamic Formula Help. Need Sheet name to be a Month and Fill down

    Hi
    Can you clarify what is A2 in your main workbook? Is a date time value or only date (integer value)
    In sheet1 of data.xlsx C2 is a date or only time and D2 is a date time value?
    Can you upload a small example?

+ 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] Macro to extract Rows Colored with Yellow Fill and create a new sheet as per current month
    By prkhan56 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-10-2016, 04:54 AM
  2. Fill in Formula to Dynamic Range
    By Mut in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-14-2009, 04:08 AM
  3. create dynamic balance sheet..with days of month..
    By GenTarkin in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-12-2009, 03:44 AM
  4. Replies: 0
    Last Post: 02-28-2006, 09:10 PM
  5. Replies: 0
    Last Post: 02-28-2006, 09:10 PM
  6. Replies: 0
    Last Post: 02-28-2006, 09:10 PM
  7. Auto fill dynamic range based on month
    By [nordis] in forum Excel General
    Replies: 0
    Last Post: 01-14-2005, 08:53 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