I need to change the formula in the page in every link to a different page
in my work book. How can I do that with out having to fo it one by one in
each cell?
I need to change the formula in the page in every link to a different page
in my work book. How can I do that with out having to fo it one by one in
each cell?
If I understand correctly, you have something that you need to change in a bunch of different cells to a different formula? If so, this is a little quicker but it won't do them all at once (I'm not sure how to do that). But if you change the formula in one cell, then highlight that cell, use format painter (the little paintbrush on your toolbar) and highlight the new cell you wish to put the formula in. The only thing is that it will completely change any formats you have in the other cell if they are different, so hopefully they are the same. I'm not sure if this will work either, but I know in word, if you highlight something and press either shift or control, you can highlight multiple items. Try to use that to format paint all at once. Again, not sure if it will work, but I hope so!
You can use Edit | Replace (or CTRL-H). Highlight the cells you want
this to happen to, then CTRL-H, and in the find box enter:
worksheet_name!
and in the replace with box enter:
other_worksheet_name!
then click the Replace All button.
This assumes that you want to link to the same cells on the two
worksheets, just the worksheet name needs to change.
Pete
You can change your Options to display all formulas in your sheet and then use the Find Replace functionality. So say all of your formulas in the sheet you want to change refer to a sheet with the name Sheet1! to Sheet2!. Go to Tools/Options/View Tab. Under Window Options, select the check box next to formulas. Click OK. When you go back to the sheet, you will now see all of your formulas displayed. Go to Edit/Replace. In the Find What? box, type the name of the old sheet reference (Sheet1). In the Replace With type in the new sheet reference (Sheet2). Select "Replace All". This will change all of your formulas that contain a reference to Sheet1 to Sheet2. Go back into the Tools/Options/View tab and deselect the checkbox next to Formulas to hide your formulas from view in the worksheet.
HTH
Cheers,
Steve
Thanks to all that worked great, I figured it out while waiting for a
response, Thanks
"Pete" wrote:
> You can use Edit | Replace (or CTRL-H). Highlight the cells you want
> this to happen to, then CTRL-H, and in the find box enter:
> worksheet_name!
> and in the replace with box enter:
> other_worksheet_name!
> then click the Replace All button.
>
> This assumes that you want to link to the same cells on the two
> worksheets, just the worksheet name needs to change.
>
> Pete
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks