Shortcut for Referencing Tab Data?

[Dan the Man]

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 Thomas]

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]

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

 

[Roger Govier]

Hi Dan

One way, having copied the sheet, Edit>Replace>Replace 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.

 

[Dan the Man]

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]

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!

 

[Dan the Man]

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

 

[Dave Thomas]

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]

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]

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

 

[Gord Dibben]

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