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.
