If a cell contains a certain word change the data

Hello,

Please see attached example spreadsheet (this is not the official spreadsheet I am using just a basic version of what my main spreadsheet needs to achieve; I am trying to simplify the data for easy viewing)

What I am trying to achieve is, in the Green cell (C3) I have a data validation list, when I select one of the options in the list, the monthly totals will appear from the table to the right for that option, then appear in the 'Monthly Totals' table (column C). This would need to match with each seperate option, I managed to get one option to work using IF function but could get each seperate to work in one table.

Cheers

You can use the following formula in the col C for the months.

this one goes in C4 for Apr

Formula:

Please Login or Register  to view this content.

$F$3:$Q$8 is your values area of the table
$C$3 is the chosen value from dropdown
$E$3:$E$7 is the different items
the 0 in the match formula tells the function to only give a result for an exact match to the value in C3
ROW(A1) result in 1 as cell A1 is on row 1. but in place of the INDEX function it is used as value for the first column f tour values are so column F

Every time you go down a row it changes to 2-3 upto 12 so that you show the next column (month) on the next row.

so my solution is not bound to the names of the months to change the date.

that would be possible too.. then you would need to replace the ROW(A1) with

MATCH($B4;$F$2:$Q$2;0)

then it will search for the column that matches the word in column B.
but since months never tend to be in random order you can also use the ROW() trick.

Also try in "C4"

Formula:

Please Login or Register  to view this content.

copy paste down