File name won't change when macro is enabled

[debbiesh]

Hello. Just when I think I have everything all figured out...

My JOB spreadsheet has contents in cells such as:

Cell U2 1 - Number changes when macro is enabled

Cell U3 =LEFT(CELL("filename"),FIND("[",CELL("filename"))-2) - The folder location for this spreadsheet (G:\Test Folder for Excel\Job Folder Test)

Cell U5 ='Project Contact Info'!$C$11&" EST"&'Project Contact Info'!$U2 - Combines the job name/file name (in C11) with the number in U2 and adds EST (30198 Alabama Ave Dimensional test EST1) which becomes the name of the 'Quick Estimate' workbook.

The problem is, when I enable the macro the first time, everything is fine. But when I click to enable the macro again, the number in U2 becomes '2' but it doesn't change in U5. The cell still shows EST1, no many how many times the number in U2 changes.

How's my code --

Please Login or Register  to view this content.

Can anyone tell me why the name in cell U5 isn't changing? For that matter, if I change the file name in cell C11, it doesn't change in U5 either.

I hope I'm making sense...and I appreciate your help as always.

[mrice]

Might be worth trying

Please Login or Register  to view this content.

At the start and end of your code.

[debbiesh]

That didn't seem to help, Martin.

[mrice]

Can you post a copy of the workbook so that I can see if there is anything unusual about it.

[debbiesh]

I can only attach the file with the macro, not the file it calls upon -- but I don't think you'll need it anyway.

Thanks.

[mrice]

The file that you send just has a value in U5 and not a formula. It seems to work OK if I paste your formula back into this cell.

[debbiesh]

The file that you send just has a value in U5 and not a formula. It seems to work OK if I paste your formula back into this cell.

Figures that it would work for you. lol

But that's what happens though. When I create this Excel workbook (from ACT!), the file name is automatically added as the job name (C11) and the formula in U5 works ONE time. The formula disappears and just the value appears. So when U2 changes to '2', U5 remains EST1.

I don't get it. I should even be able to change the file name and have it appear in U5.

---------- Post added at 04:08 PM ---------- Previous post was at 04:04 PM ----------

Is the anything that you can add to a forumla in a cell that would prevent it from changing to a value?

[mrice]

Hmmmm....

Can you post a copy of the workbook where the macro has never been run - i.e. one with a formula still in U5?

[debbiesh]

I will tomorrow morning Martin. I may have to send the whole workbook the way I use it. Thanks so much for your patience.