Excel - Editing Multiple Formula 1

[alexhu]

I have a workbook containing entries for outstanding and closed project issues (one sheet for each)

There are currently 8 of these pairs for various sub projects

I have linked the sheets to the 'front page' by formula in order to give a summary overview of everything

For each sub project there are 36 cells with formulas used
(priority 1- 5, dates, open, closed etc)

When I add a new sub project I copy and paste one of the old ones and edit the 36 cells to change the formula

Any way of doing this a little more efficiently (or quickly)?

Alex

 

[xlbo]

I assume that the bulk of the work is involved with changing the sheet name in each formula. This being the case, you have 2 options.

1: Do a find and replace, ensuring you have the "Lookin" box set to "formulas"

2: Use the INDIRECT function to build the reference part of the formula - this can reference a sep[erate cell that holds the sheet name so you just point all the sheet name references to a seperate cell and then you only have to chnage one thing

Rgds, Geoff

"Errors have been made....Others will be blamed"

Please read FAQ222-2244 before you ask a question

 

[alexhu]

Geoff

Find and replace would be easiest for me - but whats a 'Lookin' box and more important where is it?

Alex

 

[xlbo]

Ctrl+F to bring up the FIND wizard
Click on the "options" button to expand the list of available options

One of the boxes is titled "Lookin". This specifies whether the function should find within cell VALUES or within the FORMULAS.

If you choose "Formulas" then it will allow a find and replace within the formula string so you could

FIND: 'Project A'
REPLACE: 'Project B'

and this would change

='Project A'!$A$27

to

='Project B'!$A$27


Rgds, Geoff

"Errors have been made....Others will be blamed"

Please read FAQ222-2244 before you ask a question

 

[alexhu]

Perhap I should have said this is Excel 97 - there is no options button on the find wizard :-(

Alex

 

[xlbo]

But there is still the option to Lookin: Formulas rather than values. Can't remember exactly where on the Find wizard it is but I certainly remember it being there.
Do you have an "Advanced" option or any other text / dropdown boxes ??

Rgds, Geoff

"Errors have been made....Others will be blamed"

Please read FAQ222-2244 before you ask a question

 

[alexhu]

OK Geoff, found it now - it is on the 'find' box, but not on the 'replace'. Thanks for the help - have a star

Alex

 

[xlbo]

Yup - once you've "Found" in the formulas, the replace works on that level as well

Glad I could help

Rgds, Geoff

"Errors have been made....Others will be blamed"

Please read FAQ222-2244 before you ask a question