changing the name of a worksheet

[amgo_gnome]

I've created a workbook with several worksheets. One of the worksheets, called 15, references another one, called Dump15, and works ok. However, when I rename Dump15, the cells in 15 give a #REF error, even though the formulas in the cells have been updated with the new name.

Can anybody help please?

 

[plettieri]

Hi and welcome to the board:

How is the reference made? Can you post up of the formula used in the referencing...

plettieri

 

[amgo_gnome]

Thanks for replying. It's quite a compicated formula:-

IF(ISERROR(MATCH($M7,Dump15!$A$1:$A$10000,0)),0,INDEX(Dump15!$A:$F,(MATCH($K$3,INDIRECT("'Dump15'!A"&MATCH($M7,Dump15!$A$1:$A$10000,0)&":A10000"),0)+(MATCH($M7,Dump15!$A$1:$A$10000,0))-1),3))

If I rename Dump15, it incorporates the new name into the formula but gives #REF

 

[Richard Schollar]

You are using 'Dump15'! within an INDIRECT statement - this won't automatically update with the name of the renamed spreadsheet.

Regards

Richard

 

[amgo_gnome]

Richard

The formulas on 15 which reference Dump15 ARE being updated to reflect the name change but still the cell is returning #REF.

 

[Richard Schollar]

=IF(ISERROR(MATCH($M7,Dump15!$A$1:$A$10000,0)),0,INDEX(Dump15!$A:$F,(MATCH($K$3,INDIRECT("'Dump15'!A"&MATCH($M7,Dump15!$A$1:$A$10000,0)&":A10000"),0)+(MATCH($M7,Dump15!$A$1:$A$10000,0))-1),3))

The Dump15 in bold is part of a string and not part of a cell reference - I would be very surprised if this was being updated - can you recheck it?

Richard

 

[amgo_gnome]

Richard

My apologies! There are five 'Dump's in that formula and I obviously didn't check closely enough. Four of them are being updated, but as you quite rightly say, the one in the string is not being updated. What a muppet I am!

That's been a great help.

Thank you very much.

John