Permanent Headers using ={"ColA", "ColB", ColC"} and repeat for all sheets-CodePudding

In Sheet1A1, I have the following as my headers for the entire 1st row:

={"filmURL", "title", "year", "runtime", "director", "country", "language"}

This is a wonderful trick I learned here. You can clear an entire column by selecting the Col Letter, without affecting the header. I want to use the exact same header rows across all my sheets in this workbook. So in Sheet2A1 and Sheet3A1 etc I want what's in Sheet1A1 (then I only need go to one place to edit).

I went to Sheet2A1 and tried =Sheet1!A1 but that didn't work. I tried naming Sheet1A1 and then in Sheet2A1 I tried ="Titles", and that didn't work. In both cases I'm only getting the first title in the 'list': filmURL and nothing more. I also tried =Sheet1!A1:A10 but I got the same results.

I remember yet another trick I learned here, something that amounts to a shortcut to a cell so that you don't always have to repeat the Sheet! bit in formulas, but I can't find it, so I haven't tried that yet. I can't use importrange or any of that because everyhing is in the one cell.

Am I snookered?

CodePudding user response：

In your situation, how about the following sample formula?

In this case, it supposes that ={"filmURL", "title", "year", "runtime", "director", "country", "language"} is put in the cell "A1" of "Sheet1".

Sample formula 1:

=ARRAYFORMULA(Sheet1!A1:G1)

This sample retrieves the values of the cells "A1:G1" of "Sheet1".

Sample formula 2:

=SPLIT(REGEXREPLACE(FORMULATEXT(Sheet2!A1),"[\=\{\}"" ]",""),",")

This sample retrieves the formula of cell "A1" of "Sheet1" and each value is retrieved from the formula.