I have a spreadsheet with 4 tabs of quarterly data (representing four
quarters in the fiscal year-Quarter 1 July-Sept, Quarter 2 Oct-Dec, etc).
That same spreadsheet also has a tab which sums the data on a quarterly basis
(with the idea of creating 4 additional tabs reprersenting a summary of the
data for each quarter). I just finished developing the tab which will
reference the first quarters data (for requirement purposes, the summary tab
is entitled "Quarter 1 Summary", and it is referencing data on the tab
entitled: Quarter 1 July-Sept).

My question is getting help in finding a quick way to create these
"additional" summary tabs that doesn't require me to re-enter all of my data
again. For example, the first "Summary Tab" (referencing the first quarter)
has formulas such as:

=COUNTIF('Quarter 1 July-Sept'!$C$4:$C$500,"OP")

When I create the next summary tab, it will reference 'Quarter 2" data:

=COUNTIF('Quarter 2 Oct-Dec'!$C$4:$C$500,"OP")

If I had to go through each individual formula to edit the references above
(consistent with the spreadsheet they are pulling data from) it would take me
forever. There must be a quicker way. Help!

Thanks,

Dan

If you are referring to your workbook as a spreadsheet? That is very
confusing if you are. A workbook contains spreadsheets. I'm also assuming
what you refer to as tabs are in fact spreadsheets.

If so, just copy the Quarter 1 Summary sheet. Select its tab, press CTRL and
drag to make a copy. Select the copy. Rename it to whatever you want
(Quarter 2 Summary, I presume).. Then on that sheet, click the cell just
above row 1 and to the left of column A . This will select the entire
spreadsheet. Then use find & replace (Excel 2003 - Edit menu, Replace --
Excel 2007, home tab, editing, find & select, replace) or simply press
CTRL+H for both versions. Then find Quarter 1 July-Sept and replace with
Quarter 2 Oct-Dec. Select Replace all. This will change all the formulas.
Select cell A1 to deselect the entire sheet. Visually check a couple of
formulas to make sure they look ok. Repeat the process for the other sheets.

"Dan the Man" wrote in message
...
I have a spreadsheet with 4 tabs of quarterly data (representing four
quarters in the fiscal year-Quarter 1 July-Sept, Quarter 2 Oct-Dec, etc).
That same spreadsheet also has a tab which sums the data on a quarterly
basis
(with the idea of creating 4 additional tabs reprersenting a summary of
the
data for each quarter). I just finished developing the tab which will
reference the first quarters data (for requirement purposes, the summary
tab
is entitled "Quarter 1 Summary", and it is referencing data on the tab
entitled: Quarter 1 July-Sept).

My question is getting help in finding a quick way to create these
"additional" summary tabs that doesn't require me to re-enter all of my
data
again. For example, the first "Summary Tab" (referencing the first
quarter)
has formulas such as:

=COUNTIF('Quarter 1 July-Sept'!$C$4:$C$500,"OP")

When I create the next summary tab, it will reference 'Quarter 2" data:

=COUNTIF('Quarter 2 Oct-Dec'!$C$4:$C$500,"OP")

If I had to go through each individual formula to edit the references
above
(consistent with the spreadsheet they are pulling data from) it would take
me
forever. There must be a quicker way. Help!

Thanks,

Dan

Sorry for the confusion (I'm an Excel newbie). Your suggestion was great, and
it worked. Much appreciated!

"Dave Thomas" wrote:

If you are referring to your workbook as a spreadsheet? That is very
confusing if you are. A workbook contains spreadsheets. I'm also assuming
what you refer to as tabs are in fact spreadsheets.

If so, just copy the Quarter 1 Summary sheet. Select its tab, press CTRL and
drag to make a copy. Select the copy. Rename it to whatever you want
(Quarter 2 Summary, I presume).. Then on that sheet, click the cell just
above row 1 and to the left of column A . This will select the entire
spreadsheet. Then use find & replace (Excel 2003 - Edit menu, Replace --
Excel 2007, home tab, editing, find & select, replace) or simply press
CTRL+H for both versions. Then find Quarter 1 July-Sept and replace with
Quarter 2 Oct-Dec. Select Replace all. This will change all the formulas.
Select cell A1 to deselect the entire sheet. Visually check a couple of
formulas to make sure they look ok. Repeat the process for the other sheets.

"Dan the Man" wrote in message
...
I have a spreadsheet with 4 tabs of quarterly data (representing four
quarters in the fiscal year-Quarter 1 July-Sept, Quarter 2 Oct-Dec, etc).
That same spreadsheet also has a tab which sums the data on a quarterly
basis
(with the idea of creating 4 additional tabs reprersenting a summary of
the
data for each quarter). I just finished developing the tab which will
reference the first quarters data (for requirement purposes, the summary
tab
is entitled "Quarter 1 Summary", and it is referencing data on the tab
entitled: Quarter 1 July-Sept).

My question is getting help in finding a quick way to create these
"additional" summary tabs that doesn't require me to re-enter all of my
data
again. For example, the first "Summary Tab" (referencing the first
quarter)
has formulas such as:

=COUNTIF('Quarter 1 July-Sept'!$C$4:$C$500,"OP")

When I create the next summary tab, it will reference 'Quarter 2" data:

=COUNTIF('Quarter 2 Oct-Dec'!$C$4:$C$500,"OP")

If I had to go through each individual formula to edit the references
above
(consistent with the spreadsheet they are pulling data from) it would take
me
forever. There must be a quicker way. Help!

Thanks,

Dan

Hi Dan

One way, having copied the sheet, EditReplaceReplace what Quarter 1
Replace with Quarter 2 Replace All.

I haven't forgotten your other problem. I will mail the book back as
soon as I get a chance.

--
Regards

Roger Govier


"Dan the Man" wrote in message
...
I have a spreadsheet with 4 tabs of quarterly data (representing four
quarters in the fiscal year-Quarter 1 July-Sept, Quarter 2 Oct-Dec,
etc).
That same spreadsheet also has a tab which sums the data on a
quarterly basis
(with the idea of creating 4 additional tabs reprersenting a summary
of the
data for each quarter). I just finished developing the tab which will
reference the first quarters data (for requirement purposes, the
summary tab
is entitled "Quarter 1 Summary", and it is referencing data on the tab
entitled: Quarter 1 July-Sept).

My question is getting help in finding a quick way to create these
"additional" summary tabs that doesn't require me to re-enter all of
my data
again. For example, the first "Summary Tab" (referencing the first
quarter)
has formulas such as:

=COUNTIF('Quarter 1 July-Sept'!$C$4:$C$500,"OP")

When I create the next summary tab, it will reference 'Quarter 2"
data:

=COUNTIF('Quarter 2 Oct-Dec'!$C$4:$C$500,"OP")

If I had to go through each individual formula to edit the references
above
(consistent with the spreadsheet they are pulling data from) it would
take me
forever. There must be a quicker way. Help!

Thanks,

Dan

Hi Roger!

Always appreciate your input. No worries on my other problem. as I know you
are very busy with your job. It is just so very nice of you to offer the
support and input you do to "newbies" like myself. This group has been of
wonderful learning for me thanks to people like you, Max, Driller and some of
the others.

Best to you,

Dan

"Dan the Man" wrote:

I have a spreadsheet with 4 tabs of quarterly data (representing four
quarters in the fiscal year-Quarter 1 July-Sept, Quarter 2 Oct-Dec, etc).
That same spreadsheet also has a tab which sums the data on a quarterly basis
(with the idea of creating 4 additional tabs reprersenting a summary of the
data for each quarter). I just finished developing the tab which will
reference the first quarters data (for requirement purposes, the summary tab
is entitled "Quarter 1 Summary", and it is referencing data on the tab
entitled: Quarter 1 July-Sept).

My question is getting help in finding a quick way to create these
"additional" summary tabs that doesn't require me to re-enter all of my data
again. For example, the first "Summary Tab" (referencing the first quarter)
has formulas such as:

=COUNTIF('Quarter 1 July-Sept'!$C$4:$C$500,"OP")

When I create the next summary tab, it will reference 'Quarter 2" data:

=COUNTIF('Quarter 2 Oct-Dec'!$C$4:$C$500,"OP")

If I had to go through each individual formula to edit the references above
(consistent with the spreadsheet they are pulling data from) it would take me
forever. There must be a quicker way. Help!

Thanks,

Dan

When I go to replace (replace all) the Update Values dialogue box opens. I
then attempt to select the appropriate file and sheet, but pressing "Open"
does nothing more than refresh that Update Values dialogue box again.

I can't seem to stop that box from popping up everytime I select "Replace
All" to start my replacement process. Urgh!

"Roger Govier" wrote:

Hi Dan

One way, having copied the sheet, EditReplaceReplace what Quarter 1
Replace with Quarter 2 Replace All.

I haven't forgotten your other problem. I will mail the book back as
soon as I get a chance.

--
Regards

Roger Govier


"Dan the Man" wrote in message
...
I have a spreadsheet with 4 tabs of quarterly data (representing four
quarters in the fiscal year-Quarter 1 July-Sept, Quarter 2 Oct-Dec,
etc).
That same spreadsheet also has a tab which sums the data on a
quarterly basis
(with the idea of creating 4 additional tabs reprersenting a summary
of the
data for each quarter). I just finished developing the tab which will
reference the first quarters data (for requirement purposes, the
summary tab
is entitled "Quarter 1 Summary", and it is referencing data on the tab
entitled: Quarter 1 July-Sept).

My question is getting help in finding a quick way to create these
"additional" summary tabs that doesn't require me to re-enter all of
my data
again. For example, the first "Summary Tab" (referencing the first
quarter)
has formulas such as:

=COUNTIF('Quarter 1 July-Sept'!$C$4:$C$500,"OP")

When I create the next summary tab, it will reference 'Quarter 2"
data:

=COUNTIF('Quarter 2 Oct-Dec'!$C$4:$C$500,"OP")

If I had to go through each individual formula to edit the references
above
(consistent with the spreadsheet they are pulling data from) it would
take me
forever. There must be a quicker way. Help!

Thanks,

Dan

Forgot to mention that the replacement process works PERFECTLY as described
by Roger and Dave when testing with various words (e.g. Treatment for TX,
countif, for counta, etc), however when I attempt to make the replacement to
the word I want to change and replace, this is what triggers popping up
"Update Values" dialogue box. What a mystery! I wonder if Excel is confused,
because I have "tabs" which include the words "Quarter 1", "Quarter 2",
"Quarter 3", and "Quarter 4", and formula which include this information as
well. I merely want to swap out the words (as described below) within the
formula reference on a specific tab (as I identfied within my search
criteria) and my goal described in my first posting for this topic.

Find what: 'Quarter 1
Replace with: 'Quarter 2



"Dan the Man" wrote:

When I go to replace (replace all) the Update Values dialogue box opens. I
then attempt to select the appropriate file and sheet, but pressing "Open"
does nothing more than refresh that Update Values dialogue box again.

I can't seem to stop that box from popping up everytime I select "Replace
All" to start my replacement process. Urgh!

"Roger Govier" wrote:

Hi Dan

One way, having copied the sheet, EditReplaceReplace what Quarter 1
Replace with Quarter 2 Replace All.

I haven't forgotten your other problem. I will mail the book back as
soon as I get a chance.

--
Regards

Roger Govier


"Dan the Man" wrote in message
...
I have a spreadsheet with 4 tabs of quarterly data (representing four
quarters in the fiscal year-Quarter 1 July-Sept, Quarter 2 Oct-Dec,
etc).
That same spreadsheet also has a tab which sums the data on a
quarterly basis
(with the idea of creating 4 additional tabs reprersenting a summary
of the
data for each quarter). I just finished developing the tab which will
reference the first quarters data (for requirement purposes, the
summary tab
is entitled "Quarter 1 Summary", and it is referencing data on the tab
entitled: Quarter 1 July-Sept).

My question is getting help in finding a quick way to create these
"additional" summary tabs that doesn't require me to re-enter all of
my data
again. For example, the first "Summary Tab" (referencing the first
quarter)
has formulas such as:

=COUNTIF('Quarter 1 July-Sept'!$C$4:$C$500,"OP")

When I create the next summary tab, it will reference 'Quarter 2"
data:

=COUNTIF('Quarter 2 Oct-Dec'!$C$4:$C$500,"OP")

If I had to go through each individual formula to edit the references
above
(consistent with the spreadsheet they are pulling data from) it would
take me
forever. There must be a quicker way. Help!

Thanks,

Dan

Find and Replace works only on the cells on the spreadsheets. Excel does not
look at the spreadsheet tabs.
You'll have to give me an example of what you're trying to do and how you
are trying to do it.


"Dan the Man" wrote in message
...
Forgot to mention that the replacement process works PERFECTLY as
described
by Roger and Dave when testing with various words (e.g. Treatment for TX,
countif, for counta, etc), however when I attempt to make the replacement
to
the word I want to change and replace, this is what triggers popping up
"Update Values" dialogue box. What a mystery! I wonder if Excel is
confused,
because I have "tabs" which include the words "Quarter 1", "Quarter 2",
"Quarter 3", and "Quarter 4", and formula which include this information
as
well. I merely want to swap out the words (as described below) within the
formula reference on a specific tab (as I identfied within my search
criteria) and my goal described in my first posting for this topic.

Find what: 'Quarter 1
Replace with: 'Quarter 2



"Dan the Man" wrote:

When I go to replace (replace all) the Update Values dialogue box opens.
I
then attempt to select the appropriate file and sheet, but pressing
"Open"
does nothing more than refresh that Update Values dialogue box again.

I can't seem to stop that box from popping up everytime I select "Replace
All" to start my replacement process. Urgh!

"Roger Govier" wrote:

Hi Dan

One way, having copied the sheet, EditReplaceReplace what Quarter 1

Replace with Quarter 2 Replace All.

I haven't forgotten your other problem. I will mail the book back as
soon as I get a chance.

--
Regards

Roger Govier


"Dan the Man" wrote in message
...
I have a spreadsheet with 4 tabs of quarterly data (representing four
quarters in the fiscal year-Quarter 1 July-Sept, Quarter 2 Oct-Dec,
etc).
That same spreadsheet also has a tab which sums the data on a
quarterly basis
(with the idea of creating 4 additional tabs reprersenting a summary
of the
data for each quarter). I just finished developing the tab which will
reference the first quarters data (for requirement purposes, the
summary tab
is entitled "Quarter 1 Summary", and it is referencing data on the
tab
entitled: Quarter 1 July-Sept).

My question is getting help in finding a quick way to create these
"additional" summary tabs that doesn't require me to re-enter all of
my data
again. For example, the first "Summary Tab" (referencing the first
quarter)
has formulas such as:

=COUNTIF('Quarter 1 July-Sept'!$C$4:$C$500,"OP")

When I create the next summary tab, it will reference 'Quarter 2"
data:

=COUNTIF('Quarter 2 Oct-Dec'!$C$4:$C$500,"OP")

If I had to go through each individual formula to edit the references
above
(consistent with the spreadsheet they are pulling data from) it would
take me
forever. There must be a quicker way. Help!

Thanks,

Dan

Hi Dave

I have workbook which is used to monitor quality assurance data from my
agency. I've dividied the workbook into four spreadsheets (tabs) which are
identified by fiscal year quarters (Quarter 1, Quarter 2, etc). I also
developed a fifth tab which acts to sum the substantive data from each
quarters outcomes (that tab is entitled Quarter 1 summary).

I want to create additional summary tabs (e.g. Quarter 2 summary, Quarter 3
summary, and Quarter 4 summary), but I want to merely use the "replace"
function of Excel to change the references in the various formulas on the
Quarter 1 Summary Tab, to say Quarter 2, 3 and 4 as I create those additional
spreadsheets. Obviously it would be incredibly tedious to edit each formula
in the spreadhseet in my attempt to create the Quarter 2, 3 and 4 tabs.

Search and replace worked great when I did a quick test (as I stated in my
previous post), however it did not want to cooperate when changing the search
option from Quarter 1 to Quarter 2 (as it kept bringing up that Data
Validation pop up box). As you mentioned, I assumed that "search" and
"replace" only worked on the specific cells of a spreadsheet, however I
questioned that when I was able to replace most things with ease (exception
being the references to Quarter 1, 2, etc.). I hope that explains what I'm
trying to do.

Dan

"Dave Thomas" wrote:

Find and Replace works only on the cells on the spreadsheets. Excel does not
look at the spreadsheet tabs.
You'll have to give me an example of what you're trying to do and how you
are trying to do it.


"Dan the Man" wrote in message
...
Forgot to mention that the replacement process works PERFECTLY as
described
by Roger and Dave when testing with various words (e.g. Treatment for TX,
countif, for counta, etc), however when I attempt to make the replacement
to
the word I want to change and replace, this is what triggers popping up
"Update Values" dialogue box. What a mystery! I wonder if Excel is
confused,
because I have "tabs" which include the words "Quarter 1", "Quarter 2",
"Quarter 3", and "Quarter 4", and formula which include this information
as
well. I merely want to swap out the words (as described below) within the
formula reference on a specific tab (as I identfied within my search
criteria) and my goal described in my first posting for this topic.

Find what: 'Quarter 1
Replace with: 'Quarter 2



"Dan the Man" wrote:

When I go to replace (replace all) the Update Values dialogue box opens.
I
then attempt to select the appropriate file and sheet, but pressing
"Open"
does nothing more than refresh that Update Values dialogue box again.

I can't seem to stop that box from popping up everytime I select "Replace
All" to start my replacement process. Urgh!

"Roger Govier" wrote:

Hi Dan

One way, having copied the sheet, EditReplaceReplace what Quarter 1

Replace with Quarter 2 Replace All.

I haven't forgotten your other problem. I will mail the book back as
soon as I get a chance.

--
Regards

Roger Govier


"Dan the Man" wrote in message
...
I have a spreadsheet with 4 tabs of quarterly data (representing four
quarters in the fiscal year-Quarter 1 July-Sept, Quarter 2 Oct-Dec,
etc).
That same spreadsheet also has a tab which sums the data on a
quarterly basis
(with the idea of creating 4 additional tabs reprersenting a summary
of the
data for each quarter). I just finished developing the tab which will
reference the first quarters data (for requirement purposes, the
summary tab
is entitled "Quarter 1 Summary", and it is referencing data on the
tab
entitled: Quarter 1 July-Sept).

My question is getting help in finding a quick way to create these
"additional" summary tabs that doesn't require me to re-enter all of
my data
again. For example, the first "Summary Tab" (referencing the first
quarter)
has formulas such as:

=COUNTIF('Quarter 1 July-Sept'!$C$4:$C$500,"OP")

When I create the next summary tab, it will reference 'Quarter 2"
data:

=COUNTIF('Quarter 2 Oct-Dec'!$C$4:$C$500,"OP")

If I had to go through each individual formula to edit the references
above
(consistent with the spreadsheet they are pulling data from) it would
take me
forever. There must be a quicker way. Help!

Thanks,

Dan

I thought you already created the four summary sheets.
I'd have to see your workbook
Can you e-mail it to me?

"Dan the Man" wrote in message
...
Hi Dave

I have workbook which is used to monitor quality assurance data from my
agency. I've dividied the workbook into four spreadsheets (tabs) which are
identified by fiscal year quarters (Quarter 1, Quarter 2, etc). I also
developed a fifth tab which acts to sum the substantive data from each
quarters outcomes (that tab is entitled Quarter 1 summary).

I want to create additional summary tabs (e.g. Quarter 2 summary, Quarter
3
summary, and Quarter 4 summary), but I want to merely use the "replace"
function of Excel to change the references in the various formulas on the
Quarter 1 Summary Tab, to say Quarter 2, 3 and 4 as I create those
additional
spreadsheets. Obviously it would be incredibly tedious to edit each
formula
in the spreadhseet in my attempt to create the Quarter 2, 3 and 4 tabs.

Search and replace worked great when I did a quick test (as I stated in my
previous post), however it did not want to cooperate when changing the
search
option from Quarter 1 to Quarter 2 (as it kept bringing up that Data
Validation pop up box). As you mentioned, I assumed that "search" and
"replace" only worked on the specific cells of a spreadsheet, however I
questioned that when I was able to replace most things with ease
(exception
being the references to Quarter 1, 2, etc.). I hope that explains what I'm
trying to do.

Dan

"Dave Thomas" wrote:

Find and Replace works only on the cells on the spreadsheets. Excel does
not
look at the spreadsheet tabs.
You'll have to give me an example of what you're trying to do and how you
are trying to do it.


"Dan the Man" wrote in message
...
Forgot to mention that the replacement process works PERFECTLY as
described
by Roger and Dave when testing with various words (e.g. Treatment for
TX,
countif, for counta, etc), however when I attempt to make the
replacement
to
the word I want to change and replace, this is what triggers popping up
"Update Values" dialogue box. What a mystery! I wonder if Excel is
confused,
because I have "tabs" which include the words "Quarter 1", "Quarter 2",
"Quarter 3", and "Quarter 4", and formula which include this
information
as
well. I merely want to swap out the words (as described below) within
the
formula reference on a specific tab (as I identfied within my search
criteria) and my goal described in my first posting for this topic.

Find what: 'Quarter 1
Replace with: 'Quarter 2



"Dan the Man" wrote:

When I go to replace (replace all) the Update Values dialogue box
opens.
I
then attempt to select the appropriate file and sheet, but pressing
"Open"
does nothing more than refresh that Update Values dialogue box again.

I can't seem to stop that box from popping up everytime I select
"Replace
All" to start my replacement process. Urgh!

"Roger Govier" wrote:

Hi Dan

One way, having copied the sheet, EditReplaceReplace what Quarter
1

Replace with Quarter 2 Replace All.

I haven't forgotten your other problem. I will mail the book back as
soon as I get a chance.

--
Regards

Roger Govier


"Dan the Man" wrote in message
...
I have a spreadsheet with 4 tabs of quarterly data (representing
four
quarters in the fiscal year-Quarter 1 July-Sept, Quarter 2
Oct-Dec,
etc).
That same spreadsheet also has a tab which sums the data on a
quarterly basis
(with the idea of creating 4 additional tabs reprersenting a
summary
of the
data for each quarter). I just finished developing the tab which
will
reference the first quarters data (for requirement purposes, the
summary tab
is entitled "Quarter 1 Summary", and it is referencing data on the
tab
entitled: Quarter 1 July-Sept).

My question is getting help in finding a quick way to create these
"additional" summary tabs that doesn't require me to re-enter all
of
my data
again. For example, the first "Summary Tab" (referencing the first
quarter)
has formulas such as:

=COUNTIF('Quarter 1 July-Sept'!$C$4:$C$500,"OP")

When I create the next summary tab, it will reference 'Quarter 2"
data:

=COUNTIF('Quarter 2 Oct-Dec'!$C$4:$C$500,"OP")

If I had to go through each individual formula to edit the
references
above
(consistent with the spreadsheet they are pulling data from) it
would
take me
forever. There must be a quicker way. Help!

Thanks,

Dan

Dave

Even less confusing would be to refer to sheets within an Excel workbook as
"worksheets".

"Spreadsheet" is a just a generic term for the type of application that includes
Excel.

Spreadsheet applications (sometimes referred to simply as spreadsheets) are
computer programs that let you create and manipulate spreadsheets
electronically. In a spreadsheet application, each value sits in a cell. You can
define what type of data is in each cell and how different cells depend on one
another. The relationships between cells are called formulas, and the names of
the cells are called labels.


Gord Dibben MS Excel MVP

On Mon, 16 Jul 2007 20:03:43 GMT, "Dave Thomas" wrote:

A workbook contains spreadsheets. I'm also assuming
what you refer to as tabs are in fact spreadsheets.