Universally changing portion of formulas, within a sheet

Posted by A on January 03, 2002 11:27 AM

Hi...-just discovered your web-site, it's fantastic!
My dilemma:
Perhaps I'm approaching this incorrectly, but given my experience, I guess it will have to do.
Given: I have Sheet(1), with data in each row.
Reporting: I want to make a "report" for some of the lines in Sheet(1), so I set up Report(1), Report(2), Report(3), etc. with the Report(#) corresponding to each specific row in Sheet(1).
I have no problem setting up the Report(#'s) [copy tab, using Ctrl & mouse click], however, in each Report(x), I reference Sheet(1) numerous times, indicating a specific row number, corresponding to the Report(#)
Question: Is there universal way to change the reference number, on each report, without going down each column, line-by-line, changing the code? (I have about 60 line changes, for each Report)
Example: in a given cell of Report(14) I have ='Sheet(1)'!A$14, in another Report(14) cell, I may have ='Sheet(1)!H$14, etc.
Help: How can I change the $number, to correspond with the Report(#), for the entire Report (sort of "change all $14 you see to $15")?
I'm only on Report(14), and I have about 50 to go.
Please make your reponse easy to understand (sorry!).
OR: If you know a better way, and it's too complicated for this site, please tell me WHAT to look for, in an Excel manual.
THANK YOU!

Posted by Jacob on January 03, 2002 1:31 PM

Highlight all the cells you want to change and do edit|replace thenreplace $14, with $15,

HTH

Jacob

Posted by A on January 05, 2002 4:39 AM

Jacob--
Thank you, so much for your help. It worked!
However, one glitch... it worked with universally changing $x figures, but it didn't seem to work when I tried to change an alpha (representing a column) to another alpha. e.g. I had something like =F3+G3-Q3. I tried to change all Q's to R's (in a particular column). Sorry, I don't know much... (What's a CSE array?, What's VB mean? What does HTH mean (Hold the House--as opposed to the fort?), (Happy To Help), or (Hope This Helps)? ;^)
:Hi

Posted by Jacob on January 05, 2002 9:39 PM

HTH = Hope this helps
VB = visual basics
CSE = Control Shift Enter (Thats the way you have to enter array formulas

Replacing column letters will work just make sure that there are no other occurances of the letter or you could create errors in your function. For example if there are other Gs and you try to replace G it will replace them all. Just include enough of the formula to make it unique like replace $G$ would probably work fine.

HTH
Jacob Jacob--