Help, Index, Match, IF, Min formula across multiple sheets with changeable data in cells.

Not sure I fully understand, but let me try this. This solution uses some helper columns because it seems that the MINIFS formula doesn't work well with dynamic arrays. There's a helper column for each column you referred to - Item Description, Earliest Expiry Date, and Stock on hand. They are:

For Expiry Date:

Please Login or Register  to view this content.

For item Description:

Please Login or Register  to view this content.

For Stock on hand:

Please Login or Register  to view this content.

For this example, the above formulas were put in these cells:
Expiry Date - Cell Q2
Item Description - Cell R2
Stock on hand - Cell S2

Then, in F2, try this:
=MINIFS($Q$2#,$R$2#,[@[Item Description]])

In G2:
=SUMIFS($S$2#,$R$2#,[@[Item Description]])

See attached:

Many thanks,

Seems to work with the current data thats in the sheet if I update the values.

But does not seem to work if I add data to another sheet or new data on the total sheet.

You just need to expand the formulas according. Let's say you added another sheet with Table136. The one helper column would be changed to:

Please Login or Register  to view this content.

and the other helper columns would be changed accordingly.

It does work if you add data on the total sheet.

I have tried to update the formula to reflect new data being added to the screen tab for instance but I cannot for some reason reference this in the original formula?

That's Table139. did you reference that table and did you change the choose from {1,2,3} to {1,2,3,4}? Please attach the file and I can look at it.

FWIW, another alternative, which mirrors Gregb11's results:

Formula:

Please Login or Register  to view this content.

whilst you could say this approach is 'simpler' (i.e. single cell formula) there are some very notable limitations when compared to the LET approach - a) we're using Ranges rather than structured references, and b) presumption that sheet index positions (for inclusion) will be sequential (so can use 3d reference like C1:C3)
the traditional approach for 3d calcs would be to use 2 blank tabs (start and end) which would act as bookends - such that every sheet in between was included in the calc