Merge partial character before specific word across sheets based on headers

Hi,
I want merge data based on matching headers across sheets .
if the cells B4,C4 are empty then should match header(DETAILES) and merge amounts for column TOTAL based on partial character before word "NO"
if the cells B4,C4 are DATES then should match headers(DATE,DETAILES) and merge amounts for column TOTAL based on before word "NO"
as you see the structure for each sheet is not the same . so that's why I would match headers.
so the result will be in RESULT sheet based on what I put it if dates in DATES sheet and if there is no date in WITHOUT DATES sheet.
in row 7 will be headers from column C based on sheets names and will merge based on two cases as I mentioned also insert total row to sum each column .
I would macro to deal with big data for each sheet , I will add new sheets every time before RESULT sheet . so before bring report should delete data in RESULT sheet.
also posted here
https://www.mrexcel.com/board/thread.../#post-6204828

A formula solution .....

in H8 of DATES

Formula:

Please Login or Register  to view this content.

See attached: sample results in G:J of "DATES"

INDIRECT could be used to match headers in C7:F7 but if these are constant then using the fixed sheet names will suffice.

Numbers will automatically refresh as input sheets are updated but if this is a problem then VBA is the option (as you requested!)

NOTE: some dates in "SALES" are mis-typed!

@Amer omar

This is not the first time that you have addressed someone trying to help you in a disparaging tone. If you wish to continue being allowed to get help here, this must STOP.

Thank you.

The "Dates" sheet was purely used as an example (for testing) but I will now leave it to others as you evidently find it too onerous to transfer the formulae to RESULT sheet!

The tone of this:

Please Login or Register  to view this content.

and of some comments in earlier threads by you is not very polite. You seem to be accusing your helper of incompetence. It may be that your first language is not English, so you are unaware of how this comes across. You can look up the word 'dosparaging' online.

Thank you for modifying your tone to make it less critical. Thank you.

See attached: and yes, you need VBA!

Is there any reason why ALL your input sheets do not start in column A instead of a "random" set of selction A,B or C?

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Missing full stop at beginning

Need to set blank cells in array to zero

Please Login or Register  to view this content.

Sorry - updated file