How to replace or substitute function formulas but left the cell reference intact?
Hi,
I have a large amount of cells containing 2 formulas applied as cell reference for different ranges.
I'm using the formulas IFERROR and INDIRECT (with bracket and ampersand)
i don't really know how to do it because excel won't let me due to (brackets, ampersand, comma)
so the question is : it is a way to replace it with out doing one at the time?
replace with
Please Login or Register to view this content.
Please Login or Register to view this content.
thank you !!
Try find and replace.
1st find =IFERROR(INDIRECT(" & replace it with =! (Note: Input a space at the start before the equals sign)
Then find "),".") and again replace it with nothing.
Then find =! (Note: Input a space at the start before the equals sign) and replace with =
Last edited by NeedForExcel; 05-01-2016 at 11:41 PM.
Cheers!
Deep Dave
Hi NeedForExcel,
I did as you instructed but didn't work.
On Box Find entered
A-.=IFERROR(INDIRECT("
On replace Box
B-. 1 space here =!
Then pop a message box :
Microsoft excel cannot find any data to replace, check if your search formatting criteria are defined correctly.
If you are sure if matching data exist in this workbook it may be on protected sheet..etc.etc..
If weird because i ever protected this sheet, matter of fact i can replace any other formulas with out problem
for example early today i changed a large ranges of formulas :
FindReplacePlease Login or Register to view this content.ThanksPlease Login or Register to view this content.
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (or use the paperclip icon).
Hi NeedForExcel,
Unfortunately i can't send the sample workbook due that is huge (at this moment is more than 20mb) and the data is dependable, mean 1 sheet depend from other sheet and so on.. (12 worksheet), the other day i tryed but the owner (my boss) didn't let me..
thanks for your kindness
Can you atleast make a sample or something?
Coz I'v tried it.. It works for me, the only thing is it gets relative reference (PU5) instead of absolute ($PU$5)
Not sure I understand
Do you want t replace teh whole formula =IFERROR(INDIRECT("PU5"),".") with $PU$5?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Hi FDibbins,
No, I want to get rid of
IFERROR(INDIRECT("
and
"),".")
The ideal is to left the cell reference Intact ( later i can add the $ )
Thanks
OK then do it in stages....
=IFERROR(INDIRECT("PU5"),".")
1. F/R = with @=
2. F/R IFERROR(INDIRECT(" with $
3. F/R "),".") with $
4. F/R @ with nothing
Hi NeedForExcel,
Yes, it let me do the first Step,
Find
A-.=IFERROR(INDIRECT("
replace
B-. 1 space here =!
I Went to Options >> and for some reason the Match Case was tik
@Franky alta - It will let you do it all.. I tried it once again and it works just fine.
Also make sure the Look In drop down has formulas selected.
Hi NeedForExcel,
Yes it worked, i select a range but it was replacing the whole sheet, i undo and I tried the first step a few more time and bingo, it worked!
Thank you my friend!!
Thank you FDibbins,
Appreciated your kindness
No Problem..
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks