Forum Discussion
Return a range by looking up one cell.
Have you tried naming the various ranges of data, so that the entire range can be displayed? I don't know for sure that it'll work, but it occurs to me; at least it's something you've not named so far.
Hmmm, that does indeed work, but I'm not sure how I'd implement it.
I've run into this type of issue before. I need some way to access a list of those named ranges.
If I could put them in a table or list and use a lookup function that'd be great but I wasn't able to find a way to do that. They have to be inserted into a formula, so my workaround was to make a long IFS formula.
Unfortunately that wont work here, the list is much too long.
Would you happen to know a way of to accomplish this?
Would you happen to know a way of to accomplish this?
You need to be a bit more descriptive of what the "this" is. You've talked of needing to access a list of the names. You've talked of using the names in a formula. I can envision a dropdown (data validation) as a way to access a list. Also one of many lookup-type functions. But then you include the notion of including the names (presumably one at a time) in a formula, but you don't say what it is that the formula is supposed to do.
Talk us through the full process (describing it in English, not in "Excelese"; let us worry about the translation into Excel functions or procedures) ... This would probably be easy if we were sitting down face-to-face, because we could go back and forth, clarifying as we go along. Instead, I'm asking you to describe pretty thoroughly what the whole process is here. It might help if you assume:
- you're talking with a relatively intelligent person
- that person knows nothing of gaming, game characters and what one does with them
- that person does know Excel, but wants you to not talk Excel per se, just to talk of the information and what needs to happen to it, with it, etc.
My apologies for being unclear. I will try to do a better job.
If I were to use your idea of making named ranges for each character, I would then have 40+ named ranges.
What I'm not sure of, is how I would access/reference them; so one could be used in a function to call up the associated data.
As a crude example, if a user chose Centurion from this list, I would then need a way to pull up Centurion's named range so the data could be displayed.
I hope that makes more sense. I really appreciate your time.
I believe I found a solution!
I made a new table with the character names and their respective named ranges.
Then used xlookup wrapped in indirect.
So the final formula looks like this:=INDIRECT(XLOOKUP(A1, CharData[Characters], CharData[Named Range]))I'm very grateful for you help. You definitely got me on the right path.
Thank you!- That's good to hear. Glad I could play a part.
