Copy and paste does not work if I change a single sheet name within the formula

[Roband41]

I have a workbook with 2 separate sheets for each month of the year.

Some formulas are long (ex: =IF('SAL FEB 2019'!H12>'SAL FEB 2019'!E7,'SAL FEB 2019'!E7+'SAL FEB 2019'!H12+'SAL FEB 2019'!B7+'SAL FEB 2019'!G12-'SAL FEB 2019'!G3,IF('SAL FEB 2019 '!G12+'SAL FEB 2019'!B7>=0,'SAL FEB 2019'!G12))+'SAL FEB 2019'!I12)

In the above, 'SAL FEB 2019'! refers to Sales February 2019. I need this same formula for the month of March ('SAL MAR 2019 '!)

I thought that using Word to replace FEB by MAR and MAR by APR with “Search and Replace” I could easily create all my formulae’s for the twelve months. But when I copy (from Word) and paste (into Excel), the formula does not work. (Error: Cannot find 'SAL MAR 2019 '!)

I then tried to copy (from Excel) the formula that works in one month (as the complete formula above), paste it in another month and change each month to the following month (EX: changed FEB to MAR). The formula did not work either.

The only way I can get it to work, is to physically link the sheets by selecting the proper cell in the following month’s worksheet, which is not only very tedious but error prone.

I think I could use the INDIRECT function, but it seems too complicated for my level.

Any suggestion as to why:
1- what I am doing (copy and paste from Word to Excel) does not work
2- why copy and paste (from Excel to Excel or Word to Excel) adds a space after each sheet’s name (ex: IF('SAL FEB 2019'! becomes IF('SAL FEB 2019 '!)
3- what could easily solve my problem.

Thanks in advance.

 

[Special-K99]

DON'T use Word to search and Replace, use Search and Replace from inside Excel.

Can you post the file with a full explanation of what you want to do?

You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

Or upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.

 

[Roband41]

Hello Special K99
Thank you for your response.
I have taken the long road and achieved what I wanted, but I am certain that there are easier ways.
Because the file is sensitive, I could not upload it, but I will prepare a shorter workbook that will be simpler but still reflect the problem I had.
I plan to do this in a couple of weeks and hope that you can give me pointers to make my life easier next time.
Thank you.