-1

Example sheet

https://docs.google.com/spreadsheets/d/1yk6GugO_wU6nHdbEuLwqAZ1HLfzm2xyLQo5xQx02_sQ/edit?usp=sharing

I am making an interactive sheet that uses dropdowns to select data sources. I have split the sheet into "Visualization", "Variables" and "Source Data" sheets

enter image description here

The output sheet can select the data via a drop down

enter image description here

The drop downs are generated from the "Variables" sheet which contains translations of the column name to its corresponding column number and letter.

The visualizations sheet uses the column letter within QUERY statements to generate their results.

enter image description here

To get the column names I use TRANSPOSE() over the heading row on the "data" sheet.

To get the column numbers I am able to use SEQUENCE() on the column with the column names as they are in order.

I am able to convert the row number to the row letter using =SUBSTITUTE(ADDRESS(1, B4, 4), "1", "") - this translation isn't automatic (like SEQUENCE) so I have to manually update the rows when I add more data.

I would like to have the column letter automatically populate when I add a new data row (just like the column name and column numeric address).

Is there a way to pipe my SUBSTITUTE() function into a SEQUENCE(COUNTA(B:B)) loop so I can auto-generate the column letters from the address number?

David Alsh
  • 6,747
  • 6
  • 34
  • 60

3 Answers3

2

The above answers are both correct, but I think this might be a bit of an XY problem because you actually you don't need the use of QUERY (and hence the 'Variables' tab and these formulas) to get the behaviour you want as everything can be done more directly:

  1. Change the source range for the data validation rules in the 'Name' column of the 'Output' tab to point at Data!$1:$1
  2. Replace your nested QUERY within each MIN/QUARTILE/MAX on the Output tab with (for instance) FILTER(Data!$2:$1000,Data!$1:$1=A2), changing A2 as appropriate

We are simply filtering the Data tab directly by the column names. Add more columns to the data tab and they will automatically be selectable in the Name column of the Output tab.

The God of Biscuits
  • 2,029
  • 2
  • 3
  • 10
1

You can do it the same way if you enter it as an array formula and make the ranges dynamic:

=ArrayFormula(SUBSTITUTE(ADDRESS(1,indirect("B1:B"&counta(A1:A)),4),"1",""))

enter image description here

Tom Sharpe
  • 30,727
  • 5
  • 24
  • 37
0

Insert this formula in "Variables", Cell B1:

=arrayformula(iferror(regexextract(address(1,SEQUENCE(COUNTA(A:A))),"[A-Z]+"),))

Adapted from Convert column index into corresponding column letter. Credit @wronex

Tedinoz
  • 5,911
  • 3
  • 25
  • 35