Hi,

What I need to create is a pair of two sheets, that as they are copied and re-named the second sheet would be renamed by the first sheet's name with a prefix added.

So if there was a pair of sheets "sheet1" and "Psheet1", and they are copied at the same time and automatically renamed by excel to "sheet1 (2)" and "Psheet2 (2)" or the index of sheet(3) and sheet(4). The user could just rename the first “A” and the second would be renamed by the CONCATENATE function, adding P to the name of the first with resultant sheet name “PA”.

To complicate things, the users currently change the sheet name on the tab it-self, that name is inserted on a cell in the sheet by using the formula.

=MID(CELL("FileName",A1),FIND("]",(CELL("FileName",A1)))+1,999)

So there is actually no change on the work sheet to trigger the input. This is not a requirement but it would be nice that the only input would occur on the tab it self.


thanks,