Dynamically Update a Range of Cells based on another Cell's Value?

[WSBirch]

Good afternoon all,
This may sound like a silly question. I think I've done this before on accident and I absolutely cannot remember what I did or find anything on Google pertaining to it, given I lack the knowledge of what I did to explain it in a Google Search.

I feel like it had something to do with named ranges and data validation, but nothing I see is coming to the solution I'm looking for.

I want to have a few named ranges all with static information. Let's say 1 column wide, 10 rows tall. I want to select some cell on the same worksheet and reference one of the named ranges. When this happens, all of the information inside of that named range is populated from the chosen cell. Best way I could explain it, is a drop down menu without the drop down. It just autofills in the data from a named range.


With the example below. Let's say I have 3 named ranges: LETTERS (A1:A5), NUMBERS (B1:B5), and COLORS (C1:C5)
If I select cell E1 and reference the named range "LETTERS" in some way (INDIRECT?), I want (starting at cell E1) the Named Range of values for LETTERS to populate into the cells below E1. So E1=A, E2=B, E3=C, and so on. Much like what it looks like in column H in the example. (Let's pretend column H is what I want column E to look like.) I'm looking for the data that is displayed there to change based on what Named Range is referenced. Therefore, if I instead referenced "COLORS", then I'd want E1=Red, E2=Green, etc. I'm aware that if I do this, I would always need enough vertical space below the cell that's referencing the ranges to be capable of filling in the cells below it.

If this isn't done with named ranges or anything related to what I said, that's totally fine. Just looking for how to get the outcome I have described.

Thank you for any help!

 

[LouisT]

Use this formula: =IFERROR(INDEX(INDIRECT($E$1),ROW(),1),"")
However you can't have it in E1 because that's where you're choosing your named range, so i entered it in F1

 

[WSBirch]

Good morning,
Thanks for the response.
I'm not actually certain what the formula there is doing. Using your above formula in my example below, it returns the value 2.
I placed the formula in Cell R2 and referenced cell C1 ("T2A"). I'm thinking it's showing me the value in the upper left corner of the Named Range "T2A" that's one row from the top, which is 2. (Cell B3).

Maybe it'll help if I have the actual example here. The link is the actual file shared on google drive, named "NamedRangesHelp".
https://drive.google.com/file/d/1e--A9CdwSTYrck5jSHLkBDnB_GaN8VGw/view?usp=sharing

Named Ranges above are from B2:D16 named "T2A", E32:G16 named "T2C", H2:J16 named "T2B", and K2:M16 named "T3A"

Essentially, I want to know how to take the data from the Named Range "T2A" and display it in cell range O2:Q16, based on what is in cell C18.
If cell C18 is "2A", then show Named Range "T2A" in O2:Q16. If cell C18 is "2C", then show Named Range "T2C" in range O2:Q16. If cell C18 is "None", then display no Named Ranges and leave the range O2:Q16 blank.

I have no preference on formatting at all. Any formatting above is just for demonstration.

As an example of what would happen:
If cell C18="2A", then range O2:Q16 will update to show the below sceen:

I hope this helps clarify!
Thank you!

 

[LouisT]

Good morning, my apologies but my company's security won't let me download a file or access it on a shared folder. However based on your reply my formula should still work and appears to be working. You said you entered the formula and referenced the named range "T2A" and it returned "2" which is the value in cell B3 so that would be correct wouldn't it? what did you expect it to return?

 

[Fluff]

How about in O2 copied down & across
=INDEX(INDIRECT("T"&$C$18),ROW(O1),COLUMN(A1))

 

[WSBirch]

How about in O2 copied down & across
=INDEX(INDIRECT("T"&$C$18),ROW(O1),COLUMN(A1))

Hi thanks for the reply.
That returned the word "Sample" as a value in cell O2.

What I can maybe best describe, is something that essentially copy/pastes what's in a Named Range and displays it somewhere else - but not actually using Copy/Paste.
So far the solutions show data only in a single cell, whereas whatever I did somehow just mirrored the entire range of a Named Range and filled in a different range of empty cells in a 1:1 ratio - limited by the actual amount of available empty space that's going to be filled in by the Named Range.

So not looking for filling a single cell with information. Looking to display a whole range of cells.

 

[Fluff]

In that case you did not follow my instructions

How about in O2 copied down & across

 

[WSBirch]

In that case you did not follow my instructions

You are very correct. I didn't! Sorry!

And thank you!! That worked

 

[Fluff]

You're welcome & thanks for the feedback