formatting multiple formulas in one go

[nickedwards]

I have the following formula:

=('grow rev - pages'!C6)
=('grow rev - pages'!C7)
=('Grow Revenue - current'!E3)
etc

I want to go through the s/sheet and change the formula in each cell to:

=indirect("'grow rev - pages'!$C$6")

Is there a way of automating this? I have 800 cells that need changing....

Thanks in advance...

[dps700]

hi,
you can use "find" & "replace" option here. press ctrl + h

[nickedwards]

What would I type in the find and replace field?

Thanks

[FDibbins]

Find/Replace wont do that if the cell ref's keep changing - you would have to do a F/R for every instance looks like you will have to copy/paste

Why do you need to make that change?

[AlKey]

Try this:

=INDIRECT("grow rev - pages"&"!"&"C"&ROW(6:6)) and drag it down

[nickedwards]

Thanks tried that and it didn't work - any other ideaS? cheers for your help all

[FDibbins]

do you have a sample to share with us?

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[nickedwards]

Unfortunately I can't share it as it contains commercial sensitive data about the company I work for - I wish I could

[FDibbins]

Could you put together a clean sample, else it will just be guess-work

[nickedwards]

good plan - example s/sheet attached

[nickedwards]

Any ideas? Cheers guys

[XOR LX]

Hi,

Are you simply wishing to replace all of your formulae with INDIRECT versions of the same formulae in each case?

Regards

[nickedwards]

i wish to replace each formula with this format:

=indirect("'grow rev - pages'!$C$6")

thanks

[XOR LX]

Highlight only those cells for which you wish to make these changes.

Using the Find and Replace tool, perform four Replace Alls as follows and in this order:

Find what: =
Replace with: #INDIRECT

Find what: (
Replace with: ("

Find what: )
Replace with: ")

Find what: #
Replace with: =

Regards

[FDibbins]

=('grow rev - pages'!C6)
=('grow rev - pages'!C7)
=('Grow Revenue - current'!E3)
etc

what do you want these to change to? From what I can make out, it looks like it is working fine (but obviously not, else you wouldnt be asking lol)

What would you want that range of formulas to change to?