Conditional formatting for multiple sheets

[ringocheng618]

Hello,

So I am given an excel file, with 24 sheets. Each sheet represent a quarter, for instance sheet 1 would be year 1 quarter 1, while sheet 5 would be year 2 quarter 1. If I want to add column for each sheets representing which quarter they are. Do I have to do it manually by inputing 24 sheets of data and add a formula tool with it. Or is there a way I can input and use dynamic input tool to combine everything. After that, I do a conditional format: If sheet name is 1, 5, 9..., then it's quarter 1. Else if .... etc?

Thanks a lot, appreciate your help!

[AngelosPachis · Accepted solution]

Hi @ringocheng618 ,

If the different tabs in your excel file have the same schema (same number of columns, same column headers, same order that headers appear) then you can do the former by using a dynamic input tool.

[ringocheng618]

Thanks so much, it works! Sorry I am still a beginner. I understand that Regex_Replace is to replace strings. Can you clarify the pattern part? I realize you typed ".*\/" what does that mean exactly?

[AngelosPachis]

@ringocheng618 that means that you want to replace everything (.*) until the last | symbol.

.* is greedy so it will automatically match all characters until the last occurrence of whatever comes after, so in this case it will match everything until the last | appears. The backslash \ before the | is because the pipe is a special character to recognise it as a text, you have to add a \ in front.

Hope that makes sense, let me know if you have any other questions.

Cheers,

Angelos

[ringocheng618]

Thanks so much for your response, makes a lot of sense! Really appreciate it!