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

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

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.

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.

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.

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

Formula:

=COUNTIFS(INDEX(Data!$B$4:$P$33,,MATCH(Dashboard!$D$3,Data!$B$3:$P$3,0)),">0",INDEX(Data!$B$4:$P$33,,MATCH(Dashboard!$D$4,Data!$B$3:$P$3,0)),">0",INDEX(Data!$B$4:$P$33,,MATCH(Dashboard!$D$5,Data!$B$3:$P$3,0)),">0")

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 %...

Formula:

=COUNTIFS(INDEX(Data!$B$4:$P$33,,MATCH(Dashboard!$D$3,Data!$B$3:$P$3,0)),">0",INDEX(Data!$B$4:$P$33,,MATCH(Dashboard!$D$4,Data!$B$3:$P$3,0)),">0",INDEX(Data!$B$4:$P$33,,MATCH(Dashboard!$D$5,Data!$B$3:$P$3,0)),">0",INDEX(Data!$Q$4:$Y$33,,MATCH(Dashboard!B8,Data!$Q$3:$Y$3,0)),"yes")/$H$2

copied down