Thread: [Solved] INDIRECT and Relative Referencing?
View Single Post
 
Old 03-03-2016, 10:49 PM
xor xor is offline Windows 10 Office 2016
Expert
  
Join Date: Oct 2015
Posts: 1,101
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default
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).
Reply With Quote