Change Cell base on Sheet Name

[KIM Chanthan]

Hi,

Do you have anyway to change one cell base on the sheet name?
Please view attached file for more detail question.

Thanks for your help.
C

 

[PCosta87]

Hi,

With VBA it is reasonably easy...
Please refer to attachment.

There may be a way to do it without VBA though, but this solution is the first that comes to mind... hope it helps!

 

[PCosta87]

Without VBA:

In each "Mr..." sheet, place the following formula in a cell (let's say A1):
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

This will reflect the sheet name... just refer to this cell from sheet4

EDIT: or even better, use in sheet4:
=MID(CELL("filename",'Mr. A'!A1),FIND("]",CELL("filename",A1))+1,255)

'Mr. A'!A1 - select a random cell from the desired sheet.

 

[KIM Chanthan]

Thanks alot. It works now

Without VBA:

In each "Mr..." sheet, place the following formula in a cell (let's say A1):
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

This will reflect the sheet name... just refer to this cell from sheet4

EDIT: or even better, use in sheet4:
=MID(CELL("filename",'Mr. A'!A1),FIND("]",CELL("filename",A1))+1,255)

'Mr. A'!A1 - select a random cell from the desired sheet.

 

[PCosta87]

Thanks alot. It works now

Don't mention it