Substitute Function?

Allison_S

Hello, 

I have a reporting sheet that I have created for individuals within our company. The original formulas need to be adjusted with different initials for each person. This means each individual needs their own sheet to track their numbers/orders. The formula is this, 

=COUNTIFS({WH2 - ARCHIVED Orders (2019 Bend) Range 28}, "12", {WH2 - ARCHIVED Orders (2019 Bend) Range 15}, "BM") + COUNTIFS({WH2 - ARCHIVED Orders (2019 Bend) Range 29}, "12", {WH2 - ARCHIVED Orders (2019 Bend) Range 18}, "BM") + COUNTIFS({WH2 - ARCHIVED Orders (2019 Bend) Range 30}, "12", {WH2 - ARCHIVED Orders (2019 Bend) Range 21}, "BM")

The initials in this example are "BM" and I need to replace "BM" with "SM" or "CT". Is there a way to mass replace the initials within the formula, in different equations, within cells? I've been attempting to use the Substitute function, but it doesn't seem to be working. Any assistance would be greatly appreciated! 

Thanks again. 

Andrée Starå

Hi Allison,

If possible, I would have a specific cell or column with the initials and then point the formula to that cell or column when you want to reference the initials.

Would that work?

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Allison_S

Hi Andree, 

 

Thank you for your suggestion. The initials column is separate, I have a column for initials and a column for the date the job is completed. Being fed from the date column is the month/day/week columns which I use in this formula to see who completed what and when. The problem is that we have 15 employees and I need to adjust the initials for each person's reporting sheet and I was wondering if there was a way to substitute on initial for another faster than doing it manually 52 times for each work week of the month? 

Any additional ideas would be greatly appreciated!

Andrée Starå

Happy to help!

What I meant was that you should replace the initials in the formula with the cell reference where the initials would be and then it will be much easier to maintain.

Or am I not understanding what you want to do?

Best,

Andrée

Paul Newcome

I agree with Andree. Somewhere on each of the sheets you could put the employee's initials. For this example I am just going to say you added a new column named "Initials" for this and have put the initials in the cell of that new column in row 1 (just for example on how to do what Andree is suggesting).

 

Wherever you have the initials, simply use @cell = Initials$1.

 

If you replicate that column on all sheets and put the corresponding initials in row one of that column, the formula will automatically update itself to look for whatever initials are there instead of having to manually change them in multiple formulas across multiple sheets.

 

 

Allison_S

Yes, I fully understand what you are both suggesting and that it a brilliant idea! Why did't I think of that!?! :-) 

 

Thank you both for all your help - this will simplify our reporting sheets for each person dramatically! I appreciate your help! 

Allison

Paul Newcome

Sometimes the simple answers are the hardest to find. Haha

 

Happy to help! 

Andrée Starå

I'm always happy to help!

Have a fantastic weekend!

Best,

Andrée