Copying Formulas from One Worksheet to Another -- Very Easy Questi

I have a worksheet which has a bunch of formulas in rows (i.e. data entered 
in columns C through F result in formulas results in columns G through 
whatever).

All I want to do is to copy a row and move the formulas into another 
worksheet while keeping the references to the original worksheet intact.  So:

=COUNTIF(G$3:G301,G301)

becomes...

=COUNTIF(Data!G$3:G301,Data!G301), or however you lock the worksheet down.

I'm copying from row 300 in the original to Row 2, to make things a little 
more complicated, but isn't there a way to do this?

Thanks in advance!
0
Rothman (4)
5/6/2008 2:48:01 PM
excel.newusers 15348 articles. 2 followers. Follow

3 Replies
496 Views

Similar Articles

[PageSpeed] 17


"Rothman" wrote:

> I have a worksheet which has a bunch of formulas in rows (i.e. data entered 
> in columns C through F result in formulas results in columns G through 
> whatever).
> 
> All I want to do is to copy a row and move the formulas into another 
> worksheet while keeping the references to the original worksheet intact.  So:
> 
> =COUNTIF(G$3:G301,G301)
> 
> becomes...
> 
> =COUNTIF(Data!G$3:G301,Data!G301), or however you lock the worksheet down.
> 
> I'm copying from row 300 in the original to Row 2, to make things a little 
> more complicated, but isn't there a way to do this?
> 
> Thanks in advance!

Any takers?  This would be a big help!
0
Rothman (4)
5/7/2008 2:09:00 AM
Did you ever sort your problem?
One solution is to copy the formulas in their entirety into a new worksheet 
then use Data > Replace i.e. Find   G$    Replace with   Data!G$   - then a 
second Find   ,G   Replace with   ,Data!G
Hope this helps

"Rothman" wrote:

> 
> 
> "Rothman" wrote:
> 
> > I have a worksheet which has a bunch of formulas in rows (i.e. data entered 
> > in columns C through F result in formulas results in columns G through 
> > whatever).
> > 
> > All I want to do is to copy a row and move the formulas into another 
> > worksheet while keeping the references to the original worksheet intact.  So:
> > 
> > =COUNTIF(G$3:G301,G301)
> > 
> > becomes...
> > 
> > =COUNTIF(Data!G$3:G301,Data!G301), or however you lock the worksheet down.
> > 
> > I'm copying from row 300 in the original to Row 2, to make things a little 
> > more complicated, but isn't there a way to do this?
> > 
> > Thanks in advance!
> 
> Any takers?  This would be a big help!
0
RonBuy (108)
5/13/2008 6:45:00 AM
Yes.  I've found a couple of workarounds for what I need to do.  Because I 
really only need the formula results for the hypothetical data I'm entering 
(to compare it to "real" data as it comes in), I'm just pumping hypothetical 
data into my real data worksheet and then copying the values over to a new 
worksheet (and reverting the real data to...real data).  What makes it easy 
for me is that my formulas won't change, so perhaps someone else who has that 
need will need to solve that mystery.

Unfortunately, although it did cause me to have an "Eureka!" moment, your 
fix would have been somewhat tedious; the example I gave was the simplest 
formula I had.  I would have had to replace a ton of values (G through DA; G$ 
through $DA) for that to have worked, I think. 

Seems to me all solutions to this problem would have been tedious, though, 
if my formulas were to change around in the future.

"Ron@Buy" wrote:

> Did you ever sort your problem?
> One solution is to copy the formulas in their entirety into a new worksheet 
> then use Data > Replace i.e. Find   G$    Replace with   Data!G$   - then a 
> second Find   ,G   Replace with   ,Data!G
> Hope this helps
> 
> "Rothman" wrote:
> 
> > 
> > 
> > "Rothman" wrote:
> > 
> > > I have a worksheet which has a bunch of formulas in rows (i.e. data entered 
> > > in columns C through F result in formulas results in columns G through 
> > > whatever).
> > > 
> > > All I want to do is to copy a row and move the formulas into another 
> > > worksheet while keeping the references to the original worksheet intact.  So:
> > > 
> > > =COUNTIF(G$3:G301,G301)
> > > 
> > > becomes...
> > > 
> > > =COUNTIF(Data!G$3:G301,Data!G301), or however you lock the worksheet down.
> > > 
> > > I'm copying from row 300 in the original to Row 2, to make things a little 
> > > more complicated, but isn't there a way to do this?
> > > 
> > > Thanks in advance!
> > 
> > Any takers?  This would be a big help!


0
Rothman (4)
5/13/2008 5:57:00 PM
Reply:

Similar Artilces:

More than one profile in Outlook. #3
Hi I have a e-mail user that needs to open more than one e- mail account at a time, using Outlook 2002. If I click, File, Open, Others User's Folder, it works and he can get the mail for that e-mail account. When I try to put all the mailboxes in the folder list by clicking Tools, Email Accounts, View or change existing e-mail account, next, Change, More Settings, Advanced and then Add it gives me this message: The name could not be resolved. The action could not be completed. I use Exchange 5.5 Can anyone help? ...

Need one help
Dear Sir/Madam, I need some help that I am working in cousulting firm and we providing solution to clinet as web base and mail base. We are microsoft ceritfied partner and we using win2k3 ent. server on this server exchange2k3 and other is win2k3 with IMail. Till upto now we are proving clients mail solution on Imail base but now we are planing to charge more for mail solution and those are interested to pay then we give them exchange mail solution. Now I having one question in my mind is that in IMail I create diffrent diffrent domain for different web site according to clients. Now o...

Excel Financial Statements Copied into Word
I insert Excel Worksheets into Word. Some of the underlines and bouble underlines show on the screen but do not print. -- MAB Rather than inserting the Excel worksheets into your Word document, I would suggest copying the data that you need from the worksheet as a picture and then pasting that picture into the Word document. To do so, highlight the data in the spreadsheet that you want to place into the Word Document. Hold down the "shift" key and then go to Edit -> Copy Picture -> As Shown When Printed. Then go into your Word document and Edit -> Paste. -- ...

Copying and pasting an Auto Shape from PowerPoint 2003 to Word 200
When copying a circular Auto Shape from a PowerPoint flow chart into a Word document using Office 2003 the paste into Word 2003 elongates that small Auto Shape into an oval roughly 5-6 inches in length. Is there anyway to retain the shape's original size when pasting into Word? It doesn't happen here. Send me your PowerPoint file at dkr[atsymbol]mvps[dot]org and I will have a look at the issue. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted ...

Excel 2000 Shared Workbook Missing Worksheets
I am experiencing a problem in an Excel 2000 shared workbook. Worksheets are continously missing from the shared workbook. Users are connecting to the shared workbook using a Citrix connection. They enter their data into the worksheet and then save the workbook. The shared workbook is password protected so that users can not get exclusive rights and delete or move sheets. I've also tried tracing the changes and looking at the history to find the problem. Any help or advice would be greatly appreciated. --- Message posted from http://www.ExcelForum.com/ ...

Issue writing IF OR Error Formula Types
Having trouble putting a formala together. I have the following formula which works: Senario : $d$d = either weight or unit or unknown (of an ingredient) e8= is a quantity number ( either in grams or units, o an ingredient. eg 100 or 1) Raw data is in nutrition!$b$6:$g$70 The following formula works so far: f8 ( which is the no. o calories)=if($d$d="weight",e8/100,e8)*vlookup(c8,nutrition!$b$6:$g$70,3,false) But I also want to add to it that If e8(quantity)=0 or blank or invalid then "qty?" should appear in th f8 cell And not sure what to do to avoid an error in f8...

Help with array formula
I need help with an array formula, I have to columns, I need to find the min, the max and average form the two columns added, but I cant create a new column. E.g.; name s1 s2 John 1 1 Peter 3 5 Frank 6 3 min= 2 ( John) Max= 9 (Frank) average= 6.33 Thanks One way (array entered): =MIN(B2:B4+C2:C4) =MAX(B2:B4+C2:C4) =AVERAGE(B2:B4+C2:C4) In article <1164729405.003619.272780@j72g2000cwa.googlegroups.com>, GastonFranzini@gmail.com wrote: > I need help with an array formula, > I have to columns, I need to find ...

rename worksheet tabs
Hello, Maybe you guys can help me. Is there a way rename the worksheet tabs in an Excel file that I've already created? Example... I've already created a fancy Excel report using SSRS. The problem is...SSRS has no way to rename the 10 worksheet tabs to more meaningful names. Any ideas? Thanks! Double-click on the tab. That will let you rename the sheet. Alternatively, right-click on the tab, and choose "Rename" from the menu. -- David Biddulph "fix me" <brickpack@gmail.com> wrote in message news:85c00dd8-0a8f-4c32-8d12-f71c5ebcda...

worksheet_calculate event changes worksheets, but shouldn't
Hi, The code below is in the Sheet1 (Budget) Module: The user is entering data into the "Budget" worksheet. When this fires, it should hide some rows on the Categories worksheet. It does work ok, however, everytime a user enters a new figure on the Budget worksheet, it fires and momentarily flashes over to the Categories worksheet. It is quite distractive, especially when one has hundreds of figures to enter. I set a trap to stop it on the top line of this code - the Private Sub Worksheet_Calculate() line. Then, when I enter a new number on the Budget page, it fi...

Two Copies of Personal Folders Please Help!
I just transfered all of my email from my old computer to my new one and I just noticed that I have two identical copies of my personal folders in Outlook 2003. If I do something to one of them, it happens to both of them. How can I get rid of one of the copies of my personal folders? Also, I don't like the favorites folder. Can I hide or remove it? Thanks. Shannon you can edit the registry, but it'll be easier to make a new profile. http://www.outlook-tips.net/howto/ghosts.htm No, you can't hide Favorite folders. Try Ctrl+6 to use the old folder list view. -- Diane...

Routing the emails for another ISP (Exchange 2000)
Hello, I am analyzing a situation for a client: I has a LAN that has 4 PCs, assigned to 4 local users, each one has an email account (Exchange 2000). But emails should not be routed for the ISP that is being used, they must be routed for another ISP set. What options do I have for this. I hope you understand my question. Thanks. ...

Turn off formulas
I don't want any formulas to show in a document..I just want the values to show. How can I do this? Thank you. If you're saying that you see in a cell =A1+B1 rather than the result of the formula choose Tools / Options / View and untick formulas. "HT" <asfafd@microsoft.com> wrote in message news:utFVJO%23oEHA.3324@TK2MSFTNGP15.phx.gbl... > I don't want any formulas to show in a document..I just want the values to > show. > > How can I do this? > > Thank you. > > glad to help & thanks for the feedback "HT" <asfafd@...

how to increment cell formula by 5 rather than 1
Good morning group. Formula in Cell F1 =SLOPE(D10:D14,B10:B14) Formula in Cell F2 =SLOPE(D20:D24,B20:B24) Formula in Cell F3 =SLOPE(D30:D34,B30:B34) Formula in Cell F88=.... Here's the problem. A simple copy formula down operation will not work for filling in cells F4-F88. What I end up with is this: Formula in cell F4=SLOPE(D31:D35,B31:B35) It is incrementing by 1, rather than the 10 that I need it to (SLOPE(D40:D44,B40:B44). I will need to do this several more times (grab various numbers of years (10, 15) to generate slope estimates. Thus if there is something that requires a bit o...

Network printing (another sufferer)
I added a comment to an earlier thread from September 2004, because I have been experiencing problems printing over a network from Publisher (2000) I noted that Brian Kvalheim had hinted that something might be afoot, but after that the thread goes cold. So... Brian, if you are out there, (or anyone else who can help) is there anything in the pipeline to address this problem, or a work-round? Many thanks in advance ...

can 2 copies of mail be saved in outlook
I would like to be able to have 2 copies of mail saved in Outlook, so mail can be simultaniously be opened on 2 computers with the same email address. Is this possible? Outlook saves data on the local PC If you want to access the same recieved mails on another PC, you set up your mail account in Outlook on the other PC, and on both Outlooks you select the option to keep a copy on the server. (The sent mail will only be seen on the PC it was sent from) If you use an imap account the mail will be in sync on both PC's "dd" <dd@discussions.microsoft.com> wrote ...

Disabling formulas and turning file into text only
I have a workbook in which there are formulas that pull through from other workbooks. I want to make the workbook so that it doesnt ask me to update the formulas everytime and make it so that only the values show up(formulas are no longer needed once computed) Is there a way to do that besides copying and pasting as special? Not 100% sure, but I think you can do this by going edit/links/break links. >-----Original Message----- >I have a workbook in which there are formulas that pull through from other >workbooks. I want to make the workbook so that it doesnt ask me to updat...

upgraded Excel 97 to 2003, one of our older workbooks wont print .
We have a large number of Excel 2000 worksheets (many generated by macros) we have had no problems except for one sheet in one book that will not print. We print preview and see in this case 3 pages. Printer receives nothing. Our printer is a Xerox Phaser 3115. Only way we can get it to print is Move/Copy to new workbook which then prints OK Any ideas would be appreciated ...

how can i copy a document to a CD without making it read only?
HOW CAN I COPY A DOCUMENT TO A CD WITHOUT MAKING IN READ ONLY? You can't. It is not the file, but the media, that is read only. Even CD-RW media does not allow editing a file on the CD. Copy te file from CD to HD, mak edits and if a CD-RW you should be able to burn the edited file back to the CD. hth "DON" wrote: > HOW CAN I COPY A DOCUMENT TO A CD WITHOUT MAKING IN READ ONLY? ...

How do I advance the range by one row Excell VBA automatically #3
I am using Sheets(Array("Breakfast", "Lunch", "Supper", "Bedtime")).Select Range("B94:D98").Select but when I run this more than once it overwrites what was put in the first time. ...

check box checked
Hello, I would appreciate any help concerning "check boxes". This concerns a trucking company. The company has multiple "runs" in different locations/states. Each "run" pays a different amount. I would like to use the "check box" for the person to check off the run he/she did at the end of the day. Then in turn, I need that $ amount to read out into an "other cell". For instance, a "run" to the state of Virginia is $37.50, so when that "Virginia box" is checked (b7) - how can I get the $37.50 dollar amount out into...

formulas in excel #2
I'm having trouble dislaying the results of my formulas. I've typed the formulas in and that's what is displaying instead of the results. I know I'm overlooking something but I just can't seem to figure it out. Can someone please point me in the right direction. Thanks in advance. Tom Hi Tom your cell is porbably formated as 'Text'. Goto 'Format - cells' and change the format to 'General'. After that reenter your formula HTH Frank Tom wrote: > I'm having trouble dislaying the results of my formulas. I've typed > the formulas in...

Unable to set the formula property of the series class
Hi Guys, I am struggling with one problem. I am trying to change formulae o series in a chart using VBA code given below. But I am getting 'Runtime Error 1004, Unable to set the formula property of the serie class' Code ------------------- ActiveChart.SeriesCollection(1).Formula = _ Replace(ActiveChart.SeriesCollection(1).Formula, "Sheet1", "test") ------------------- Can anybody Please suggest some solution? Thanks, MH -- Malha ----------------------------------------------------------------------- Malhar's Profile: http://www....

How do you print pages 1 AND 3 in a worksheet? #2
I have tried all three methods you guys gave - but I get the sam message with each... Runtime error 9. Script Out of Range What is this telling me. How do I modify those commands to suit my spreadsheet -- SirLoi ----------------------------------------------------------------------- SirLoin's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1593 View this thread: http://www.excelforum.com/showthread.php?threadid=27413 That means that you do not have a worksheet with the requested name. Perhaps you should change the name of the request (sheet1) in the macro or ...

UserForm Print Extra Copies
Hi On a user form I have a command button "Print1" which on clicking uses the following macro to print the sheet Sub Macro4() Sheets("Print1").Select ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True End Sub What would I need to add so that on clicking the command button it would ask how many copies required. Any help much appreciated Cheers Mully Try this: Dim x As Integer x = InputBox("Number of copies?", "Print") If x > 0 Then ActiveWindow.SelectedSheets.PrintOut Copies:=x, Collate:=True Else End If ...

Help Re-formatting Cells (not as easy as it sounds)
I have a column of values that are all numbers, however, Excel doesn' recognize them as numbers. (These were dumped into Excel by some othe program). I need to scan through these columns with VLOOKUP to match, but sinc they are treated as text (it appears) I am getting "#N/A" for th entries. Now, all of these data entries appear to be regular numbers, but whe you click on the cell you see an apostrophe in front of each number. Examples looks like: 112358 but the cell has '112358 as the input. When I try to change format, nothing happens (i.e. currency doesnt ad dollar si...