Formula help! Find and replace in formulas

[Davin]

I created concatenated fields of multipe other fields and then counts them
based on what is in the concatenated fields.

=COUNTIF('Master Recruit'!AC:AC,"24,de,sc,FAST TRACK")

I have to do this on 3,000 times on 15 different tabs and the only part that
needs to change is the "de". Next it will be "dc" then "dw" etc...

Is there a way I can just find and replace for each tab?

I don't want to spend my Holidy going, F2...backarrow...delete...delete...

Help me Obi-Wan, you're my only hope...

Thanks

 

[Ron Rosenfeld]

I created concatenated fields of multipe other fields and then counts them
based on what is in the concatenated fields.

=COUNTIF('Master Recruit'!AC:AC,"24,de,sc,FAST TRACK")

I have to do this on 3,000 times on 15 different tabs and the only part that
needs to change is the "de". Next it will be "dc" then "dw" etc...

Is there a way I can just find and replace for each tab?

I don't want to spend my Holidy going, F2...backarrow...delete...delete...

Help me Obi-Wan, you're my only hope...

Thanks

What do you mean by a "tab"? Is that an abbreviation for table, or do you mean
a separate worksheet?

In any event, you could put your list of "d's" in a column of cells (or in an
individual cell if by tabs you mean a worksheet), and rewrite your formula:

=COUNTIF('Master Recruit'!AC:AC,"24," & cell_ref & ",sc,FAST TRACK")

where cell_ref contains de or dc or dw or ...


--ron

 

[Davin]

They are tabs and each tab is for a different set (like "de" and "dc" and
"dw"). Otherwise the formulas for each tab will be exactly the same.
So the formulas for each tab will be exactly the same except that I have to
change those two little characters.

 

[Ron Rosenfeld]

They are tabs and each tab is for a different set (like "de" and "dc" and
"dw"). Otherwise the formulas for each tab will be exactly the same.
So the formulas for each tab will be exactly the same except that I have to
change those two little characters.

The only definition for "tab" that I can find that is specific to Excel (USA)
is a reference to the label for a worksheet. Since the only data in that
location is the name of the worksheet, I presumed you meant something else,
although I'm still not sure what.

In any event, you should be able to adapt the solution I posted to your
problem. Let me know how it works.


--ron

 

[Davin]

Sorry Ron,

I should have clarified:

I have a workbook containing multiple worksheets, one for each set of
two-letter characters (de, dc, dw...) that are counting from a "master"
worksheet. The problem is that in the field that it is counting there are a
number of different variables, including one that specifies a date. Otherwise
I could just do a find and replace.
Each worksheet has the exact same format and formulas except the
two-character code.

I have resigned myself to manually changing each individual field at this
point as all of my attempts at automating the process have failed.

Thanks for your help, if you think of anything I would love to know about it.

Happy Holidays!

 

[Ron Rosenfeld]

Sorry Ron,

I should have clarified:

I have a workbook containing multiple worksheets, one for each set of
two-letter characters (de, dc, dw...) that are counting from a "master"
worksheet. The problem is that in the field that it is counting there are a
number of different variables, including one that specifies a date. Otherwise
I could just do a find and replace.
Each worksheet has the exact same format and formulas except the
two-character code.

I have resigned myself to manually changing each individual field at this
point as all of my attempts at automating the process have failed.

Thanks for your help, if you think of anything I would love to know about it.

Happy Holidays!

OK, now I understand what you mean by "tabs".

I'm still not sure exactly what you are doing, though.

Does all the data get entered into 'Master Recruit'!AC:AC with the COUNTIF
formula on the individual worksheets?

If so, and if, for example you had the appropriate two-letter code on each
worksheet -- let us say in cell A1, then you should be able to use the formula
I posted in my initial response.

=COUNTIF('Master Recruit'!AC:AC,"24," & cell_ref & ",sc,FAST TRACK")

substituting A1 for cell_ref.

Just use this formula on each worksheet and it should pull out of A1 the
appropriate d.. reference.


--ron

 

[Davin]

Wonderful!!!

You made me a happy boy this holiday season.

I should've fully tried your initial idea. That worked like a charm.

You're the best, Thanks a ton!

 

[Ron Rosenfeld]

Glad it worked! Thanks for the feedback. And I hope you enjoy the rest of the
holiday, too.

Wonderful!!!

You made me a happy boy this holiday season.

I should've fully tried your initial idea. That worked like a charm.

You're the best, Thanks a ton!

--ron