Using a cell reference as list title when transposing a named range

[Keelin]

Hello All

I would really appreciate some help on a little conundrum I have!

I am building an interactive sheet to profile a number of divisions based on a summary sheet of about 1200 rows and 90 columns of profiling criteria

I have attached an example but essentially:

1. I would like to be able to select from a list a value which would also be a named range title
2. Have the range information transposed into a row

What works:
1. I can transpose the range information when I use the following formula
I used OFFSET(Governance,COLUMN()-MIN(COLUMN(HGovernance)),0)

The problem:
I cant get it to use a cell reference (i.e. When I select Governance out of a Drop down list of Finance, HR, Governance etc). Having to type in "Governance" to bring back governance range information is fine, but not great when I am trying to profile the Finance or HR division as it then becomes too manual.

Would love some help!

Thanks so much everyone

Keelin

[Glenn Kennedy]

Hi. Try selecting Governance in E8 and see if this is what you had in mind.


I've deleted a dead named range. I renamed Hgovernance - as I thought it might cause a problem. You can change it back - but if it ain't broke, don't fix it.


I fixed your nice offset equation by including an indirect function. It was difficult to see what sort of INDEX:MATCH you wanted, so I just made something up.

Try it out & let me know...

[azumi]

=OFFSET(B5:C7,0,MATCH(E8,OFFSET(A4:C7,0,0,1,COLUMNS(A4:C7)),0)-2,ROWS(B5:C7),1)

When implemented this formula, follow this step:
1. Copy above formula
2. Paste in your workbook, then will show #VALUE!, but thats okay
3. And hold your that cell with formula with mouse and drag down as necessary, for your case 4 or 5 rows
4. When is blocked, press F2, and final step press CTRL-SHIFT-ENTER button together, and the results will showed up

[Keelin]

OMG that is such a lovely solution I came across the Indirect and managed to create the longest and most complicated one in the world to make it work!!

This is absolutely great, I am sooo happy and thankful.

I appreciate it soo very much,

Keelin

[Keelin]

OMG that is such a lovely solution I came across the Indirect and managed to create the longest and most complicated one in the world to make it work!!

This is absolutely great, I am sooo happy and thankful.

I appreciate it soo very much,

Keelin

[Keelin]

Hi Glenn,

I have another question, I have updated the example and reattached....It now includes information in the cells under the named range (that is not part of the named range)

E.g.
Governance
Strategy & Planning
Policy
Secretariat
w
x
y


Is there any way to not bring w, x, y as its not part of the actual range? If we could do this awesome otherwise I will have to set up separate tables for all of my ranges which would make it a bit manual if I have to change anything

Thanks so much,

Keelin

[Glenn Kennedy]

I'm not sure if the answer can be as simple as this, but... Why not just delete the formulae in H13:J13??

[Keelin]

Would love to but some times I will bring back range information for a division/function that will have up to 7 fields, so some will have 3 some 7

[Glenn Kennedy]

Worth a try... Give me a minute.

[Glenn Kennedy]

Others will be able to help wit an elegant formula-based solution. This works, but it's a total cheat. Use conditional formatting in E13:J13, whereby if the cell value is 0 (which is what you get when there are empty cells at the bottom of the sub-head range) the text colour is white.

There zeros are there, but if the cell background is white, you can't see them...

[Keelin]

Thank-you will try that

I am now having issues with the index match formula!! Gosh what a day I cant seem to get anything working today!

I posted a new threat with a clearer example of what I am trying to achieve but essentially I need to bring back a value based on 4 specific criteria.

Thanks so much for all your help, sooo greatly appreciated

K

[Glenn Kennedy]

If it works, then can you mark this thread as solved and (preferably) click the Add Reputation button at the foot of this post.

If not, shout...