Using tab names in formulas

[Harrytheb]

I use a formula against the names in column A ie:

=COUNTIFS('Adams'!B4:B101,"<="&TODAY(),'Adams'!B4:B101">="&TODAY()-60)

in this case Adams. The datails for Adams is stored in the tab called Adams as you can see from the formula. I have another 20 names to add to column A, with the associated tabs. Just dragging the formula down against the names will still leave Adams in the formula in columns B onwards. It seems a slow method to then have to change the name in each formula to reference the new tab.

Is there a way to get the formula to look to column A to tell it which tab to go to, and then carry out the CONTIFS calc using this tab name? In the future as the names change on the tab and column A it would be good not to have to have to amend the formula in the various columns.

Thanks in advance, H

[Jonmo1]

You can use INDIRECT to do that..

=COUNTIFS(INDIRECT("'"&A1&"'!B4:B101"),"<="&TODAY(),INDIRECT("'"&A1&"'!B4:B101"),">="&TODAY()-60)

A1 = Adams

[judgeh59]

this is a little bizarre and not seeing your layout, you could try this....create a row above all the data....in row 1 column A type 'Adams' without the quotes and then do that for every column you are going have/need....then in your formula reference that cell with 'Adams' in it....like so....

=COUNTIFS(A1!B4:B101,"<="&TODAY(),A1!B4:B101">="&TODAY()-60)

also you can set the color of the lettering to the same as background so you can't even see it.....then if change/add names/worksheets you just have to add a new column or change the name in the first row....I hope this makes sense.....

[judgeh59]

ignore mine....not sure it'll work....I didn't test it.....bad me

[Harrytheb]

Hi Jonmo1, the syntax above came up with an error. As I typed had green for the second, seventh, eighth and ninth brackets if that helps!

Rgds H

[Harrytheb]

Hi judge59, Thanks for trying anyway!

[Jonmo1]

Hi Jonmo1, the syntax above came up with an error.

What error?
Don't 'type' it in..do a direct copy paste from the forum to your cell.

Assuming your original formula works
=COUNTIFS('Adams'!B4:B101,"<="&TODAY(),'Adams'!B4:B101">="&TODAY()-60)

and A1 = Adams
Then this works for me
=COUNTIFS(INDIRECT("'"&A1&"'!B4:B101"),"<="&TODAY(),INDIRECT("'"&A1&"'!B4:B101"),">="&TODAY()-60)

[Harrytheb]

Ok, good with the paste. The reason I typed it in is because the example is not the real spreadsheet. I have about 30 columns in the real one before I drag them down. The error was in my typing however. Many thanks for your help. This has helped me no end! Rgds H

[Jonmo1]

Great, glad to help.