I have a main workbook below, which pulls data from another workbook called data.xlsx.
Excel 2016 (Windows) 32 bit
A B 1Date 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 1Number 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
Bookmarks