Excel - IF Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

My spreadsheet has several tabs. A formula on tab 1 is referencing data from tab 2. I want to duplicate my formula and then use the Replace function to have it reference tab 3 instead of tab 2. However, when I attempt the replace Excel opens a dialog box to open a different Excel file. Even if I forego the Replace function and just manually change the path within my formula, Excel opens the dialog box. At the moment I can't seem to reference any data on tab 3 on my spreadsheet. What is going on?
Solved by C. J. in 35 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 24/09/2018 - 09:48
Welcome, Thanks for choosing Got It Pro-Excel. I can help you with that problem.
Excelchat Expert 24/09/2018 - 09:48
Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows.
Excelchat Expert 24/09/2018 - 09:48
Do you have sample data which we can use to illustrate how to solve this problem?
User 24/09/2018 - 09:49
yes
Excelchat Expert 24/09/2018 - 09:49
Kindly share it if you won't mind.
User 24/09/2018 - 09:49
[Uploaded an Excel file]
User 24/09/2018 - 09:50
Are you able to open the file?
Excelchat Expert 24/09/2018 - 09:51
Yeah, I now have it.
Excelchat Expert 24/09/2018 - 09:51
Which formula are you referring to?
User 24/09/2018 - 09:52
I'm selecting cell A26:X50, and I'm trying to replace "CP1-Initiation" with "CP2-Discovery".
User 24/09/2018 - 09:52
So, same formula but referencing a different tab in the spreadsheet
Excelchat Expert 24/09/2018 - 09:56
Okay.
Excelchat Expert 24/09/2018 - 09:57
Thank you for your patience as I was going through it.
Excelchat Expert 24/09/2018 - 09:58
Based on what you’ve shared, you need to replace "CP1-Initiation" in formulas with CP2-Discovery
Excelchat Expert 24/09/2018 - 09:58
Is that correct?
User 24/09/2018 - 09:59
Yes, for rows 25-50
User 24/09/2018 - 09:59
Sorry, 26-50
User 24/09/2018 - 09:59
Rows 1-25 I want to continue to reference the CP1-Initiation tab.
Excelchat Expert 24/09/2018 - 10:00
Okay.
Excelchat Expert 24/09/2018 - 10:00
You must have not been searching and replacing the correct and EXACT phrases.
Excelchat Expert 24/09/2018 - 10:01
Hence an error.
Excelchat Expert 24/09/2018 - 10:01
The sheet names have a space appended to them.
Excelchat Expert 24/09/2018 - 10:02
In the "Find What" field put exactly this phrase:
Excelchat Expert 24/09/2018 - 10:02
'CP1-Initiation '
Excelchat Expert 24/09/2018 - 10:02
With the single quotation marks.
Excelchat Expert 24/09/2018 - 10:02
Note that there is a space appended to it.
Excelchat Expert 24/09/2018 - 10:03
In the "Replace with:" field, put this phrase:
Excelchat Expert 24/09/2018 - 10:03
'CP2-Discovery '
Excelchat Expert 24/09/2018 - 10:03
You can click on the Replace All
User 24/09/2018 - 10:03
I just did and got the same result. Did it work for you?
Excelchat Expert 24/09/2018 - 10:04
It works fine for my end.
Excelchat Expert 24/09/2018 - 10:04
Kindly copy and paste the EXACT phrases I have sent to you.
Excelchat Expert 24/09/2018 - 10:05
Find and Replace:
[Uploaded an Excel file]
Excelchat Expert 24/09/2018 - 10:07
In fact it should work fine.
User 24/09/2018 - 10:07
Weird. It did allow me to replace by putting in two spaces after Discovery as in your screenshot.
User 24/09/2018 - 10:07
It does not, however, appear to be referencing the CP2 tab. No data is pulled in.
User 24/09/2018 - 10:08
and if I try to manually remove the extra space I'm back to the same problem
Excelchat Expert 24/09/2018 - 10:10
Let me upload for you the modified fiel.
Excelchat Expert 24/09/2018 - 10:10
*File
Excelchat Expert 24/09/2018 - 10:10
Formula replaced.
[Uploaded an Excel file]
Excelchat Expert 24/09/2018 - 10:11
The formula in the range:
Excelchat Expert 24/09/2018 - 10:11
A26:X50
Excelchat Expert 24/09/2018 - 10:11
Have been all replaced.
Excelchat Expert 24/09/2018 - 10:11
Just like you had described.
Excelchat Expert 24/09/2018 - 10:12
You can check any, they are now pointing to CP2-Discovery sheet instead of Initiation sheet.
User 24/09/2018 - 10:12
I must be having some sort of software issue on my end. Your spreadsheet works fine. The exact same changes made in mine don't. Bizarre.
Excelchat Expert 24/09/2018 - 10:13
Did you succeed putting the two spaces for the CP2 sheet?
User 24/09/2018 - 10:14
I did, and excel accepts that change. However, it doesn't actually pull in data from the CP2 sheet like your version does. I'm mystified.
Excelchat Expert 24/09/2018 - 10:14
Kindly try this:
Excelchat Expert 24/09/2018 - 10:15
In your original workbook
Excelchat Expert 24/09/2018 - 10:15
Rename both sheets
Excelchat Expert 24/09/2018 - 10:15
When you rename them, just delete the spaces appended to them name so that the names do not contain the spaces.
Excelchat Expert 24/09/2018 - 10:16
Currently Discover sheet is like:
Excelchat Expert 24/09/2018 - 10:16
'CP2-Discovery "
Excelchat Expert 24/09/2018 - 10:16
*
Excelchat Expert 24/09/2018 - 10:16
'CP2-Discovery '
Excelchat Expert 24/09/2018 - 10:16
Delete the two spaces so that it becomes:
Excelchat Expert 24/09/2018 - 10:16
'CP2-Discovery'
Excelchat Expert 24/09/2018 - 10:17
Similarly to Initiation sheet from:
Excelchat Expert 24/09/2018 - 10:17
'CP1-Initiation '
Excelchat Expert 24/09/2018 - 10:17
To
Excelchat Expert 24/09/2018 - 10:17
'CP1-Initiation'
Excelchat Expert 24/09/2018 - 10:17
Are we together?
User 24/09/2018 - 10:18
yep
Excelchat Expert 24/09/2018 - 10:20
Once you have modified the names.
Excelchat Expert 24/09/2018 - 10:20
Find and replace
Excelchat Expert 24/09/2018 - 10:21
In the Find field, find:
Excelchat Expert 24/09/2018 - 10:21
'CP1-Initiation'
Excelchat Expert 24/09/2018 - 10:21
In the replace field put:
Excelchat Expert 24/09/2018 - 10:21
'CP2-Discovery'
Excelchat Expert 24/09/2018 - 10:21
NOTE: This time round there are no spaces appended to the sheet names.
Excelchat Expert 24/09/2018 - 10:21
Does the problem persist?
User 24/09/2018 - 10:22
It looks like that did the trick
Excelchat Expert 24/09/2018 - 10:22
Yaaaay!
Excelchat Expert 24/09/2018 - 10:22
Awesome!
User 24/09/2018 - 10:22
Thank you so much. I've been banging my head on this problem for hours.
Excelchat Expert 24/09/2018 - 10:23
It was my pleasure helping you solve this problem. Any time you have a problem with Excel do not hesitate to come back for help.
Excelchat Expert 24/09/2018 - 10:23
Please remember to rate us at the end of the chat for better and quality services. Thank you!
User 24/09/2018 - 10:23
Will do. Have a good night.

This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.