# Copy Excel Formula Microsoft Office professional edition 20...
March 12, 2010 at 08:12:53
Specs: Windows 2000
 I have 500+sheet, summary sheet, and total sheet in excel. I need to know how to copy a formula that state (=sheet1'!\$B\$1) in summary sheet in cell A2 to A3 in the same sheet so that it can read (=sheet2'!\$B\$1). Is the possible. See More: Copy Excel Formula

#1 March 12, 2010 at 08:37:43
 I'm missing something here.You want to copy a formula on your summary sheetfrom cell A2 to cell A3You want the formula to change from =sheet1'\$b\$1 to =sheet2'\$b\$1Correct?MIKE

Report •

#2 March 12, 2010 at 09:11:32
 Look up the INDIRECT() and ROW() functions in Excel Help.Combining them should get you what you are asking for.Put this formula in A2 and drag it down:=INDIRECT("Sheet"&ROW()-1&"!\$B\$1")In A2, ROW() - 1 will evaluate to 1 so you'll have:=INDIRECT("Sheet1"&"!\$B\$1") which evaluates to:=Sheet1!\$B\$1As the ROW() function picks up the number of the Row it is in and subtracts 1, it will increment from 1 to 2 to 3 etc.P.S. I left out the single quote in my formula...put it back in if you really need it.

Report •

#3 March 12, 2010 at 09:30:33
 Hi,To create a large number of formulas which cannot be extended by dragging - as in your case where you need the change the sheet number do this:1. Starting at cell 2 in any empty column (column G for this example) enter 1 in G2, then 2 in G3.2. Select the two cells and drag them down the 500 or so lines required, so that the last cell has the number of the last worksheet.3. In the column H also on row 2 enter this:`="XXXSheet" & TEXT(G2,"0") & "!\$b\$1"`4. Drag this formula down alongside the numbers in column G5. H2 will now display this ```XXXSheet1!\$b\$1 ```6. Select all the cells from column H and Copy7. Goto cell A2 and Paste Special - Paste Values8. Select column A and do a Find and Replace, find XXX and replace with =You now have a series of formulas referencing cell B1 in all your worksheets.A bit tedious, but it sure beats the heck out of changing 500 formulas manually.Regards

Report •

Related Solutions

#4
March 12, 2010 at 13:42:02
 Humar,Thanks for the great advice. So I can do IF THEN statement too. ex: =IF(sheet1'!\$B\$1=1,sheet1'!\$B\$2,sheet1'!\$B\$3). Thanks alot

Report • 