Edit – Replace

Topic

New Reply

Good afternoon

I have a worksheet in which 15,232 cells have Transport! and then a cell reference for example Transport!D6. I thought that I could use replace to change Transport! to =Transport! but I just get a dialog box saying that there is a forumala error.

There is a way that I can change 372 cells manually and then drag them across to make the changes but I am intrigued to know why it will not replace?

Reply | Quote

Replies

Do you have a sheet named Transport in your workbook? It should work otherwise – at least it did for me.

Reply | Quote

Thanks for the quick response, yes I have a sheet named Transport and that is where all of the cell references are supposed to be pointing once the = sign is added.

Back off to try again

Ta

Reply | Quote

Are you sure there isn’t a leading or trailing space in your sheet name?

Reply | Quote

Are you sure there isn’t a leading or trailing space in your sheet name?

Hi Rory

Thanks for the input, the reference did work initially in my trial sheet with only 372 references to cells in the Transport sheet, but now that I have had to make it work for the 15k cells it does not if I click into any cell and manually add an = before Transport! it works, it just does not seem to want to know when it comes to bulk replacing.

I think I will just bite the bullet and manually change the 372 and drag them over

Thanks

Reply | Quote

I have tried again but this is what I get?

Reply | Quote

Does it work if you select a small subset of cells and then do the Find/Replace on just those cells?

Reply | Quote

I’m not sure it is a bulk issue, I just did a replace with 196,299 cells with out a problem.

I’m guessing you have a cell with the =Transport! in it already and when you are replacing the Transport! with =Transport!, you end up with a cell with ==Transport!

Do a replace in with =Transport! to Transport! then try it again.

Reply | Quote

=Transport! is not a valid cell reference, it fails a cell address.

I’d expect to see
=Transport!A1 or similar.

Reply | Quote

I wasn’t giving a complete cell content description. I was pointing out that there may be a cell that already contains the =.

Reply | Quote

I wasn’t giving a complete cell content description. I was pointing out that may be a cell that already contains the =.

Morning

Sorry for the Tawdry response but my broadband went down at 16:00 and has just come up now.

Spot on, 1 stupid = sign was all that was doing it.

Thanks to all for your input

Reply | Quote