MIN IF function across multiple worksheets

[Uomoviso]

I have a series of worksheets containing invoices, one per year. In each, column A contains a list of projects names, column B contains a date, and column C contains the amount invoiced.

I'd like to set up a formula in the summary sheet that automatically returns the date of the first invoice for each project.

To work out the first invoice date for Project A in only the 2015 worksheet, I've been using:

{=MIN(IF('2015'!A2:A21=A2,'201'!B3:B21,"-"))}

This works fine, but I'd like to modify this to allow me to look at all three worksheets, 2015, 2016 and 2017.

Thanks!

[Glenn Kennedy]

3D arrays are sometimes a bit more complicated to set up!!

Using a Named Range (Sheets) - CTRLF3 to view it, use this array formula:

=MIN(IF(T(OFFSET(INDIRECT("'"&TRANSPOSE(Sheets)&"'!A2:A21"),ROW($A$2:$A$21)-ROW($A$2),))=A2,N(OFFSET(INDIRECT("'"&TRANSPOSE(Sheets)&"'!B2:B21"),ROW($B$2:$B$21)-ROW($B$2),))))

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[alansidman]

What if you built three columns with the year across the top and applied the formula for each year then in a fourth column used the Min value for the three columns? Alternatively, you could develop an IF then statement in VBA to do this for you.

[oeldere]

I consolidated all data in 1 sheet. After that a pivot table with the min of de date.

See I get a differant result for job F.

Below is the code and the attachment.

Please Login or Register  to view this content.

[Uomoviso]

Thanks for your input everyone. My preference is to avoid pivot tables and I'm trying to automate the function so I can keep adding years without having to rewrite the function... Still working on it...

[Glenn Kennedy]

Did you look at the sheet/solution at Post 2? In what way did it NOT do what you want?

[Uomoviso]

My apologies Glenn, I'm not sure how I missed your earlier response. The formula you've used has a few functions I'm not familiar with... Will check it out now.

[Uomoviso]

Okay, I'm still getting a #VALUE response. Unless I'm mistaken Glenn, where you've written TRANSPOSE(Sheets), I'm supposed to swap Sheets for a series of cells that contain the name of the sheets?

i.e.

=MIN(IF(T(OFFSET(INDIRECT("'"&TRANSPOSE(D1:D3)&"'!A2:A21"),ROW($A$2:$A$21)-ROW($A$2),))=A2,N(OFFSET(INDIRECT("'"&TRANSPOSE(D1:D3)&"'!B2:B21"),ROW($B$2:$B$21)-ROW($B$2),))))

Where D1:D3 contain a list of sheet names.

Is that right?

[Glenn Kennedy]

No. If you don't want to use a named range, then you need to anchor the cell references, so that the range does not change as you copy downwards.
The advantage of a named range is that it can be made fully dyanmic, to expand/contact automatically. that said, the Named Range that i used was Not fully dynamic..:

=MIN(IF(T(OFFSET(INDIRECT("'"&TRANSPOSE($J$1:$J$3)&"'!A2:A21"),ROW($A$2:$A$21)-ROW($A$2),))=A2,N(OFFSET(INDIRECT("'"&TRANSPOSE($J$1:$J$3)&"'!B2:B21"),ROW($B$2:$B$21)-ROW($B$2),))))

[Uomoviso]

Thank you Glenn, solved.

[Glenn Kennedy]

You're welcome. Though in future, please don't open parallel threads. As you can see they waste time and can cause mayhem!!!

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[nzopa]

Hello Glenn,
This is the first time I write in this forum (possibly this is the 1st time in my life that I write something in a thread ..) and I am sorry if I am re-opining something solved years ago, but I have been struggling on the same issue for hours and finally I found your answer.
It perfectly works, but when I look into your formula, some steps are not clear to me.
I see that without the T and the OFFSET formulas, nothing works, but I am not able to understand why. do they have any specific job here or they are just workarounds?
In case you read this, do you think it would be possible to give me some hint on how they work?

Thank you in advance
nzopa

[Glenn Kennedy]

Well... I'm still alive and well, and still here from time to time. I'm away from my PC now... but I'll reply tomorrow am. If I forget, send me a PM in 12 hr from now to wake me up!!

[Glenn Kennedy]

Multi-sheet formulae can be a) complicated, b) very complicated) and c) daunting.

Essential or workarounds? There's a question. Of course the answer is they are BOTH as they are essential to work around the fact that other syntax versions don't work....

T and N they look at an array and return TEXT (T) or a NUMBER (N) from that array.... or nothing (""). T is used in column A... the text array and N in column B... the number array.

The OFFSET is capturing the entire range of text values. Now that I look at it again, one bit is redundant.

MIN(IF(T(OFFSET(INDIRECT("'"&TRANSPOSE($J$1:$J$3)&"'!A2:A21"),ROW($A$2:$A$21)-ROW($A$2),))=A2etc, etc, etc

The bit in red is redundant and it's easier to explain if it's not there:

MIN(IF(T(OFFSET(INDIRECT("'"&TRANSPOSE($J$1:$J$3)&"'!A2),ROW($A$2:$A$21)-ROW($A$2),))=A2,etc, etc, etc.

Red: start from A2 on all 3 sheets

Green: this returns numbers 2:21 in an array, telling Excel how many rows down from A2 to look (2,3, 4, 5,etc). Obviously you don't want it to start from 2 rows down, so a correction factor needs to be applied.

Cyan: the correction factor... 2. So Excel now receives the instruction: start from A2 and look down 0, 1, 2 ,3, etc rows

Blue: and return TEXT or blank. At this point you have half of the MIN-IF built up, you have the text values from all 3 sheets (or blanks) ready to add the criterion.

Purple: the criterion. So this returns TRUE if if the text value is equal to the value in A2, or otherwise FALSE.

The other half of the formual works in exactly the same way. Essential, or a workaround? You tell me!!

[nzopa]

I'd say 3D references seem to me the only solution to the question above if you don't/can't/are not able to use VBA.
Surprisingly to me, now you deleted the red redundancy :A21, then the formula seemed to be more affordable for my limited knowledge of excel.
So, I applied it to a similar problem to the one that Uomoviso submitted, but on a bigger cell range: 12 sheets (12 months), 3 conditions to check and 485 rows per sheet. In each sheet there is a formula comparing a specific value (different for each row) with those included in all of them. Thus, in my worksheet I have 485x12 if-(3)nested formulas.
I could have possibly figured out in advance what it is happening now to me excel file: it takes more than 30 sec to calculate everything..

I have been readying elsewhere that OFFSET is a volatile function that can have an heavy impact on the performance.
May I ask you if there is any way to substitute OFFSET with a non-volatile function like INDEX, for example?

Thank you in advance

[nzopa]

Hello Glenn,
Finally, I abandoned my original idea to work with 3D references cause of huge consumption of resources needed and I chose a different approach.
Just FYI, my aim was to check when it has been the last time that each tag/nr of a long list (almost 500) had been surveyed in the previous months (12 months in the worksheet, from Jan to Dec)
You can find below the original formula.
Since i would have liked to take note of "Running" only items, and items can be univocally identified through both plant & TAG/nr only, then I wrote formula below, based on your post:
E14=MAX(IF(T(OFFSET(INDIRECT("'"&TRANSPOSE(Months)&"'!$C$14");ROW($C$14:$C$499)-ROW($C$14); ))=C179;IF(T(OFFSET(INDIRECT("'"&TRANSPOSE(Months)&"'!$B$14");ROW($B$14:$B$499)-ROW($B$14); ))=B179;IF(T(OFFSET(INDIRECT("'"&TRANSPOSE(Months)&"'!$J$14");ROW($J$14:$J$499)-ROW($J$14); ))="Running";N(OFFSET(INDIRECT("'"&TRANSPOSE(Months)&"'!$I$14");ROW($I$14:$I$499)-ROW($I$14); ))))))

=IF($D14="";"";IF(OR($E14>EOMONTH($H$2;0);$E14=0);"";$E14))

In the original worksheet there where 483 lines like this per each tab (12 tab for 12 months).
The above formula calculates the last surveying date of each item from Jan to Dec, but if you consider that when I am in February I wanted to list only dates included in tan Jan and Feb, then formula you can see that above was wasting a lot of calculation resources without any reason.

This is way I passed to (iterative) different approach:

E14=IF(MAX((B14=INDIRECT("'"&INDEX(Months;MATCH($J$12;Mesi;0)-1)&"'!$B$14:$B$499"))*(C14=INDIRECT("'"&INDEX(Months;MATCH($J$12;Mesi;0)-1)&"'!$C$14:$C$499"))*ROW($J$14:$J$499)-ROW($J$13))<0;
From Feb to Dec, this formula calculates the latest surveying data taken from the previous tab/month
F14=IF(MAX((B14=$B$14:$B$499)*(C14=$C$14:$C$499)*(Calcolo!$AJ$2=$K$14:$K$499)*ROW($J$14:$J$499)-ROW($J$13))<0;0;
This formula compares each item with surveying dates taken in the current month
F14=IF(MAX($F14;$E14)=0;"";MAX($F14;$E14))
Finally this formula takes the highest value between previous one

Bye and thank you again for your support.