Software

Ask something
Question Locked

A formula to return a formula in Excel???

0 helpful
7 replies

by bubba_barnett

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a situation in which several (hundreds) of sheets in the same workbook require the same formula to be enterred. Is there a way (short of building a function) that I can enter the formula once in a 'Legend' sheet, and then use a reference to that cell in each of the other sheets? for example:

Sheet1: Cell A11: =sum($A$1:$A$10)
or
Sheet1: Cell A11: sum($A$1:$A$10)
?
Then on Sheet 2: Cell A11: (a formula which results in the use of the formula stated on Sheet1!A11)

In other words, on sheet 2 I will place the sum of Sheet2!A1:A10 into Sheet2!A11....

How??? HELP!!!

7 replies

by bschaettle

Sheet1: Cell A1: =sum($A$1:$A$10)

This formula references itself (Cell A1). Excel won't let you do this. Did you mean that this will be the formula for cell A11 perhaps?

by bubba_barnett

Yeah, it was supposed to be sheet1!A11, also I had some text enclosed in "<" marks, which the site saw as metatags and didn't display, so I changed them to "(" instead.

The original post has been corrected.

by bschaettle

OK, since you want to enter the same formula into the same cell on all the worksheets, this is going to be pretty easy.

I discovered this technique by accident a few years ago, and so I don't know the technical term for it. You can select multiple worksheets simultaneously and anything you type into a cell on one of them is entered into the same cell on all the other selected sheets. I call it "carbon paper" which I realize dates me a little.

To select multiple worksheets, first click one worksheet tab at the bottom of the Excel interface, then hold down Shift and click on another worksheet -- all worksheets between them will be selected. Whatever you type into cell A11 will be entered into cell A11 of all the selected sheets, just like they had carbon paper between them.

by sgt_shultz

did you try Naming the cell range and then pasting the name. from the excel text menu, Insert Name (or Insert Name Paste). i found this by searching excel help for 'reference worksheet in formula' in case you decide to give up being helpless (i'm teasing!!)

by manu.kirag

you can put a single macro. every time u don hav to put the formula just select the particular cell and press the required key

below is the macro for that

Sub m()
ActiveCell.FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)"
End Sub

i hope ths helps buddy

This discussion is closed to new replies.