You wrote:

But technicially speaking, I'm wondering if the blue highlighted one (below) could be all in one formula (so that there is only one cell).
---------
One should always be cautious arguing that something is impossible in Excel, but here I think such a claim is justified. At least I don't know of any way to get what you want.

And by the way, my formula does not at all work in general, only for the sheet presently specified in B1. If for example you insert Sheet3 and change B1 to Sheet3 you will need to have a similar formula as the one in H1 in for example I1 referring to Sheet3 and change the formula to: =INDIRECT("'"&B$1&"'!"&ADDRESS(ROW()+1;I1))

(It may be possible to automate the change of the last argument, H1, I1,.... as you change B1).