INDIRECT function

[Mary Bizmax]

Hi guys

Love Mr Excel and usually I can figure out what I need but am going round in circles here, so help would be much appreciated.

In my workbook I have a sheet called "Summary" where I am picking up information for each month. The names of the months are in this sheet in cells B1 to M1. e.g. "Apr" in is cell B1, "May" in C1 etc.

I then have a sheet for each month with same naming convention e.g. "Apr", "May" etc. I've got various formula in the summary sheet to reference the individual sheets, just wondering how I can insert the name of the sheet within the formula by referencing the names of the months? Example formula is =IFERROR(INDEX(Apr!$V:$V,MATCH(Summary!$A3,Apr!$U:$U,0)),"") So I would like to substitute the Apr bit with B1, but not sure how to do this.

Thanks

Mary

 

[JoeMo]

Assuming your month names are strings and not dates formatted as "mmm", try:

=IFERROR(INDEX(INDIRECT(B1&"!$V:$V"),MATCH(Summary!$A3,INDIRECT(B1&"!$U:$U"),0)),"")

 

[Mary Bizmax]

Thanks so much Joe, works beautifully.

 

[JoeMo]

Thanks so much Joe, works beautifully.

You are welcome - thanks for the reply.