How can I define a column header in a formula, using the contents of another cell?

[jackhooper92]

Hi everyone.

I'm building a dashboard in Excel and would like to know whether there's a way to define a column header within a formula, using the text contained in a given cell - I've attached a screenshot which will hopefully help explain what I'm looking to do.

Ultimately, I need to be able to toggle the list of barriers, such that the values in the table underneath change according to whether the barriers are present or not. The drop down list corresponds to a series of columns in another table, which are valued on a scale from 0-5.

I'd normally use a COUNTIFS or an AVERAGEIFS to calculate the value I want to return, but if I try to use the cell reference for the drop down menu, I just get a #VALUE error.

Screen Shot 2018-06-04 at 16.31.51.png

Hopefully this all makes sense, but do let me know if you need more info from me!

Cheers

[FDibbins]

Hi, welcome to the forum

You may be able to use INDIRECT to do that reference, but it's a bit hard to see what you are doing fromthat pic.

I suggest that you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[jackhooper92]

Thanks for replying so quickly!

So here's a smaller dummy version of the sheet.
I would want to define three barriers from the list in cells D3:D5. Then, I would need to be able to figure out the number of people who have achieved each outcome, who also present each barrier (which is decided by the score 0-5 given; 0 means there is no barrier, 1 and above means there is a barrier). I.e., cells F8:F18 would all return a percentage, which would correspond to the number of people who present all of the barriers defined (i.e. score 1 or more for each of the defined barriers) and who have achieved that specific outcome.

Hopefully that makes it clearer, and the file has survived the upload process...

Edit: thanks also for the welcome, didn't spot that at first!

[FDibbins]

Thanks for the file.

1. Can you give some sample answers to what you show there?
2. Would it be possible to use the same "terms" in the headings on Data sheet, as you have in Dash B8:B18? It makes it easier to match if the headings match.

[jackhooper92]

Here's the file with matching headings to the Data sheet. I'm not sure what you means by sample answers, though?

[FDibbins]

What I meant was - what answers where you expecting, based on your DD selection (and maybe explain how you got them?). That way, I can (hopefully) work backwards and figure out how to do what you want.

Basically, walk me through, step by step, how you would do this manually.

[jackhooper92]

I see! Ok, here's v3. I've added in the COUNTIFS I'd use if the barriers were static, but I'd like to be able to toggle the selection of three barriers.

To explain the manual calculation:
- I'd narrow the population to calculate which people had reported that they experience all three of the barriers selected (in this case there are 20 people reporting "Jobless household", "Housing exclusion" and "Health conditions" together)
- I'd then calculate how many of those people have answered Yes to each outcome - i.e. how many people that experience those three barriers have gone on to achieve "Improved motivation", "Improved interpersonal skills" etc.
- I'd then represent that as a percentage.

[FDibbins]

Here is the basis of the formula you would need...

Please Login or Register  to view this content.

This gives the "20" you have in H2
=(COUNTIFS(Data!$B$4:$B$33,">0",Data!$C$4:$C$33,">0",Data!$D$4:$D$33,">0"))

This would then the the formula for the %...

Please Login or Register  to view this content.

copied down

[jackhooper92]

Ah amazing, thanks so much!