Summarizing data from multiple sheets without a pivot table as data is text

Good afternoon,

I have a spreadsheet which contains multiple sheets showing deployment/availability of vehicles on a daily and hourly basis as per the example sheet below.

I would like to make a summary sheet which will give the availability for all vehicles from all the sheets on an hourly basis on a particular day (probably chosen by a filter) in a similar way to the summary example below. What I really want to do is make a pivot table, however of course, pivot tables do not allow text in their values fields

Is there a relatively easy way to achieve this without a pivot? or is there an alternative to a pivot that allows text?

Many thanks

thank you for your messages. Power Query is something that I have long heard of but never used. I don't think that I have access to it in my version of excel...

this example looks great and like it works perfectly. Does one just change the date manually?

thank you so much, i will go and try it in my main sheet

Thank you that is what i thought!

Originally Posted by JohnTopley

In C8

=INDEX(INDIRECT("'"&$A8&"'!$D$8:$AA$259"),MATCH($B$4,INDIRECT("'"&$A8&"'!$B$8:$B$259"),0),COLUMNS($A$1:A$1))

"Summary" in same workbook as vehicle sheets

Thank you John, this has worked perfectly! Very much appreciated.