Using the RIGHT function on a FORMULA within a cell?

Question

I'm reformatting a sheet so I can utilise the data for some graphs. The existing formatting is a bit clunky to "transpose" to the required format, so I'm trying to figure out an easier way.

"Graphs" tab should transpose specific data from the "Statistics" tab - as can be seen. I'm trying to avoid creating manual cell references for the "Decks" column and instead extract the Col reference from the formula, increment it by 3, thus creating the next cell reference.

My attempt can be seen in A9.

I've tried googling how I can reference formula in a cell on a RIGHT function, but the keyword is giving a tonne of results that don't address my issue.

Here's the sheet!

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

what are you after and whats your desired / expected output in A9? — player0
Sorry for not being clear. If you look at sheet "Statistics" it's the headers across the top of Row 1 (sans "Total"). Comparing that to the existing formulas in the Deck column on the "Graphs" tab you will see that each cell reference to "Statistics" increments by 4 letters. As the sheet is very wide, I'm a bit sick of manually clicking on each cell to create the new references, so was hoping to use a formula. — Aaron Irvine
So I in the "Graphs" sheet I want cell E5 to: - Reference cell E4 - Extract the 2nd last character - Increment that character by 4 (H -> L) - Use the new letter in the formula — Aaron Irvine
*** Extract the 2nd last character from the FORMULA (returning "H") — Aaron Irvine

player0 player0 · Accepted Answer · 2019-02-18T13:05:36

0

votes

=TRANSPOSE(FILTER(Statistics!H1:1, Statistics!H1:1<>""))

Using the RIGHT function on a FORMULA within a cell?

1 Answers