Conditional Formatting change only sheet number

[georgegram]

Hello everyone,

I got a workbook, where i have 31 sheets (one for each day of the month). Then there is a final sheet where i got a column with every day of the month.
When i set a formula in the 1st of the month to read a specific text from the 1st sheet, everything goes ok.
Instead of doing that for every day, is there a way to auto fill the following days by changing only the sheet number?

Sheets are named like: 01,02,03,04...31

The formula i use in conditional formatting is =COUNTIF('01'!$C$19:$AG$19;"*"&$AC$32&"*")>0
All i ask if there is a way to keep everything the same and change the '01' to '02','03' etc.

Thanks in advance,
George

 

[etaf]

you could use an indirect() and have cells with 01,02 etc
not quite sure how your sheet is setup

assuming the cell is set to text and in B1, C1, D1 , E1 you had
01 , 02 , 03 , 04 etc

you could use

=COUNTIF(INDIRECT(B1&"!$C$19:$AG$19");"*"&$AC$32&"*")>0

where B1 will change to use 01 in the formula
and as you copy across use 02, 03 etc

 

[georgegram]

Thanks for your time,

The setup imagine is like that:
There are 31 sheets. Each sheet represents a day of the month 1st of April is 01 sheet for example. In all those 31 sheets there are 30 names in columnn A. For every name in each row there is assigned a specific letter.
Now in that final sheet every name has a unique column numbered from 1-31 for each sheet. The cell right to the date has the conditional formatting formula and according to what letter has in each sheet, i want to color it.
I tried what you suggested but it didnt let me use that kind of formula.

 

[etaf]

should work - did you try
=COUNTIF(INDIRECT(B1&"!$C$19:$AG$19");"*"&$AC$32&"*")
and get a number
make sure the numbers in the summary sheet are formatted as text as they need to be 01 and not 1
works on my test
https://www.dropbox.com/s/5rcul0lvhwp7hm7/indirect_condfrmt_etaf.xlsx?dl=0

using Column I instead of AC32 - just to keep visible on sheet
and conditional format B2 and C2 only

 

[georgegram]

this is the file if you want you can check the conditional formatting at the final sheet with the countif formula.

https://drive.google.com/open?id=12WGvC3uuEUxevvpn8rVXFSlIgw4zqNk5

 

[etaf]

using the last sheet it works
if i enter a value in sheet 30 and 31 as a test in cel Q125

then D35 & D36 populates
i dont see any conditional formatting in the sheet

you need to be precise on what you want me to look at

 

[georgegram]

I am sorry for the inconvenience, i tried to share it via google drive, but it is true that the formatting does not appear (!).
I could send you the excel via email if you are ok with that.

 

[etaf]

i have the spreadsheet OK via googledrive
But its the understanding I'm having issues with

you said

if you want you can check the conditional formatting at the final sheet with the countif formula.

but i cannot see that in the sheet

 

[georgegram]

at the final sheet in B36 there is a formula in conditional formatting manage rules.
=COUNTIF('01'!$C$19:$AG$19;"*"&$AC$32&"*")>0

 

[etaf]

ok, see that rule
changed to use indirect and stayed coloured