Create Dashboard from data

[nparsons75]

Hi,

I have a range of data laid out like this. B16:AH21

Dates in the headers of the columns
Data in the rows below the dates.

On my dashboard page I would like to select two criteria.

First, a value from first column.
Second, a date from the column headers.

For example,
In column B I have 5 rows, each row contains a vehicle colour.

RED
GREEN
SILVER
BLACK
WHITE

Across the columns under each date from 1 to 31 depending on the month, there is data. Basically telling me how many of each colour car was sold on that day.

So, from the criteria I choose, I would like to choose colour and data. This will then show all my data in relation to this.

 

[nparsons75]

Hi, thanks for this. Im just working through it now, i get the following error.

Currently I have set up only two sheets to test. MAR and APR. When I select either of these two months from the dropdown I get the error, #N/A. If I select a month from the dropdown for which I have not set up a new sheet, I receive the error #REF !.

So, I think this tells me that the drop down reference is working, or doing something at least.

Im just not sure how to get the correct data in, the references within the formula do not refer to the sheets, does the drop down do this for me I guess?

Your formulas in the example refer to cells in the same sheet, I think..

 

[Dryver14]

Not sure what you have set up, are the ranges for the index and match correct without the indirect?

 

[nparsons75]

The new formulas you sent, I have adapted to link correctly to my cells. I wish there was a way to show you the file, make it easier to explain. In the original formulas I had to link to the cells in the march cheets, simply by clicking the link and then the cell. example: =INDEX(MAR!$D$16:$AH$21,MATCH(B9,MAR!$B$16:$B$21,0),MATCH($E$5,MAR!$D$15:$AH$15,0))

In the new formula, I cant do this as there are, or will be 12 sheets. I assume that the new drop down menu will take care of this.

I now have two selection, date and month. I type in the date, and select the month separate, is that how I should have created it?

The index, is that the dropdown?


Also, I dont see any reference to the date field in the new formula. (ignore this, just seen it)

 

[Dryver14]

No the index is the inner range of the table, that is fixed, the only odd thing in you formula is the first match I would have thought that would have been C16:C21 but it
doesn't matter if that is where you have your list of colours. I did think about the date and had an idea just to label the top row from 1 to 31, that way you wouldn't have to fully qualify the date just select day number and month in drop downs

 

[nparsons75]

I have just worked through the formula again, im sure its so close....

The list of colours, do i refer to the list of colours on my dashboard, and then the formula will look at the sheets to return the value. when you tried this at your end, did you have MARCH on a different sheet tab?

 

[Dryver14]

I've sent you a PM

 

[nparsons75]

So sorry, I have just gone through it numerous times, finally got it working....

You were bang on, it was my mistake, the final range was on the wrong row.

Thank you so much

 

[Dryver14]

Good stuff, come back to me with any other issues