formulas

[Guest]

I am using this formula sum=('Oct 1:Oct 31'!C42). I need to enter this
formula 112 times but have it advance each time to reference a different
columm, example sum=('Oct 1:Oct 31'!D42), sum=('Oct 1:Oct 31'!E42)etc, etc. I
have not found an easy way to do this other than manually drag the formula
down to the next cell and than change the letter in the formula and the
number to 42 because excel want to change the number to 43 each time. Any
ideas?

 

[Steve H.]

Use this........sum=('Oct 1:Oct 31'!$C$42).

$= ABSOLUTE REFERENCE.

Steve H.

 

[Myrna Larson]

I don't understand your formula; the syntax isn't correct. If you intend a 3D
sum, it should be

=SUM('Oct 1:Oct 31'!C42)

not SUM=(.....

If the formula above is what you want, then try this.

1. Insert a new row 1

2. In A1, enter the *syntactically correct* formula that refers to cell D42.

2. Edit/Goto and type A1Q1 in the address box (that's 121 cells). Then press
CTRL+R to fill the formulas to the right. Excel will increment the column
number, but leave the row as 42.

3. Trick Excel into thinking the cells contain text rather than a formula: use
Edit/Replace to either remove the equal sign or replace it with another
character. I'll do the former: In the Find What box, type an equal sign. Leave
the Replace With box blank, and Replace All. The first cell should now look
like

SUM('Oct 1:Oct 31'!C42)

4. Edit/Copy these cells.

5. Select the cell where the first formula belongs and Edit/Paste Special
selecting the Transpose option in the Paste Special dialog box. Since Excel
considers the contents to be text, it won't be changed in any way.

6. Put back the equal sign: Edit/Replace and replace SUM with =SUM

7. Delete the row you inserted in step 1.

Actually in step 3, instead of deleting the =, you can replace it with any
character that doesn't appear in the formula (including the new formulas where
the column letters have changed). A symbol like # would work. (But don't use
@, as Excel will think you're a Lotus user and change it to =.)