Using Tab value in formula

[ridgetown_rick]

I must not be using the right keywords because I haven't been able to find anything on how to insert the value on the tab into a cell by using a formula. Can it be done, or do I need to write a macro to do it?

 

[VoG]

The file must first be saved for this to work

<code>=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))</code>

 

[ridgetown_rick]

Thanks Peter, but unless I'm reading it wrong, that isn't what I need. I guess I was not clear. What I want to do is insert whatever is on the tab (e.g. sheet1) into a specific cell by using a formula. Ideally it would be something as simple as =tab but obviously that doesn't work, so what might?

 

[VoG]

Do you mean like

=Sheet4!E23

 

[ridgetown_rick]

Yes, LIKE that, but not exactly.

What I am creating is a workbook with about 30 worksheets, each with the workload calculations for a different person. I am creating this by using ASAP utilities, a list of the employees on one sheet, and a template of the workload calculations on another.

Using this tool the name of the employee is placed on the tab of each sheet, but I also want it in cell A3 (where I now manually type it). I thought the easiest way would have been to just insert a formula in A3 that would grab the value on the tab, then as each worksheet was created the names of the employees would be automatically inserted.

Is this any clearer?

 

[VoG]

I think so. In A3 of each tab enter the first formula that I posted. So if the tab name is Dave A3 should then display Dave.

 

[Jonmo1]

Yes, LIKE that, but not exactly.

What I am creating is a workbook with about 30 worksheets, each with the workload calculations for a different person. I am creating this by using ASAP utilities, a list of the employees on one sheet, and a template of the workload calculations on another.

Using this tool the name of the employee is placed on the tab of each sheet, but I also want it in cell A3 (where I now manually type it). I thought the easiest way would have been to just insert a formula in A3 that would grab the value on the tab, then as each worksheet was created the names of the employees would be automatically inserted.

Is this any clearer?

If by that bolded statement you mean
"Put the NAME of the Sheet into A3"

Then put Vog's formula in A3
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

IMPORTANT
AS Vog Noted, the File MUST be saved for this formula to work.
It will not work on a NEW book until it is saved.

 

[ridgetown_rick]

Thanks. I got it to work. I was getting #VALUE in the cell till I double clicked to adjust the cell width, then it worked.

Sorry for the confusion. I'm a happy camper again.