SUMIF formula to identify column matching a named cell

Hi all,

Hoping someone may be able to help with this query.

I have a summary sheet and a data sheet. On the data sheet are the months Jan-Dec in columns B-M and names in column A (eg Caroline). On the summary sheet I have created a drop down list to enable me to choose the month I want and labelled the cell "CurrentMonth" in the Name Manager.

Is there a formula I can write that will know to sum everything in the "CurrentMonth" column for a specified name (eg Caroline)?

I have written the below but it is giving me a #REF! error.

=SUMIF(INDIRECT("'"&"Data Sheet"&"'!$A:$A"),"Caroline",INDIRECT("'"&"Data Sheet"&"CurrentMonth"))

Thanks!

Thank you that was really helpful. The second formula without the named range worked but not the first. The Name Manager shows that it is valid for the whole workbook..

I had wanted to use a named range in the formula so it would automatically update for a new month when i updated the drop down for the next month. Is that possible?

Thank you very much, that was worked perfectly