Can formulas look up sheet numbers instead of sheet names?

[country101]

I have a spread sheet that I need to adapt for several differant crews. Each spreadsheet has a sheet for each supervisor. My formulas in my master sheet all draw off of the supervisor sheets. Since the supervisors' names will change on the sheets when I do a differant crew, I cannot re use the formulas without manually changing them. If they were referancing the sheet number instead of name, however it would work, I think. Is this possible?

 

[Robert Mika]

You can use Name Box Name Manager to create Name Cell in each sheet and then use your formula to link o this cell.
Even if the Sheet name will change this will not affect your formula.
Is this what you after?
Coudl you post your formula?

 

[T. Valko]

I have a spread sheet that I need to adapt for several differant crews. Each spreadsheet has a sheet for each supervisor. My formulas in my master sheet all draw off of the supervisor sheets. Since the supervisors' names will change on the sheets when I do a differant crew, I cannot re use the formulas without manually changing them. If they were referancing the sheet number instead of name, however it would work, I think. Is this possible?

It's possible but it may be more trouble than it's worth.

What kind of formulas are you using that need to refer to sheet numbers rather than the sheet name?

 

[country101]

Its not the formula that is the problem, I am just trying to save work when I try to adapt these files to other crews, or presumably if supervisors leave or switch crews.

I have a master sheet, data sheet, and a sheet for each supervisor(7). I have an if statement on the master that checks to see if there is a name in a cell on each of the supervisors page. This works fine. The problem will be that I have to make this spreadsheet for other crews. When I do, and I change all the supervisors names to make it fit the other crew, my if statement will no longer work and I will have to retype all the formulas(40 of them with 7 arguements each) before I can autofill the rest. If I can attach the formula to the sheet rather than the sheet name, when I change the name my statement will still work. I will have to retype the original, but it will save me 2/3 of the work.

 

[T. Valko]

Its not the formula that is the problem, I am just trying to save work when I try to adapt these files to other crews, or presumably if supervisors leave or switch crews.

I have a master sheet, data sheet, and a sheet for each supervisor(7). I have an if statement on the master that checks to see if there is a name in a cell on each of the supervisors page. This works fine. The problem will be that I have to make this spreadsheet for other crews. When I do, and I change all the supervisors names to make it fit the other crew, my if statement will no longer work and I will have to retype all the formulas(40 of them with 7 arguements each) before I can autofill the rest. If I can attach the formula to the sheet rather than the sheet name, when I change the name my statement will still work. I will have to retype the original, but it will save me 2/3 of the work.

Still not sure why you would need to use sheet numbers rather than sheet names. However, after reading your explanation again I don't think my idea is what you had in mind.

I can give you a formula that returns the sheet names and then you could choose which sheet to use by using an index number for the sheet name.

Can you post an example of one of your formulas and explain how you would use a sheet number rather than the actual sheet name?

 

[country101]

=IF(Data!C51,Dumas!E5,IF(Data!C52,Fox!E5,IF(Data!C53,Freeman!E5,IF(Data!C54,Karnes!E5,IF(Data!C55,McGee!E5,IF(Data!C56,Morris!E5,IF(Data!C57,Spanke!E5,0)))))))

The names are the supervisors sheets. When I do a differant crew, the names will change and I will have to edit the formula accordingly 40x per crew times the 4 additional crews making it about 1k changes that I would like to get down significantly. If it was referancing a more constant source such as the (name) in the properties form when you open VBA, I could do the formulas once. But, my problem is, I dont know how to do that.

 

[T. Valko]

=IF(Data!C51,Dumas!E5,IF(Data!C52,Fox!E5,IF(Data!C53,Freeman!E5,IF(Data!C54,Karnes!E5,IF(Data!C55,McGee!E5,IF(Data!C56,Morris!E5,IF(Data!C57,Spanke!E5,0)))))))

The names are the supervisors sheets. When I do a differant crew, the names will change and I will have to edit the formula accordingly 40x per crew times the 4 additional crews making it about 1k changes that I would like to get down significantly. If it was referancing a more constant source such as the (name) in the properties form when you open VBA, I could do the formulas once. But, my problem is, I dont know how to do that.

Ok, how would using sheet numbers improve that formula?

 

[country101]

If it uses the sheet name under the properties box in VBA instead of the name I give it, it will referance the sheet by it's location instead of my name. As long as the sheets are in order, all my formulas will still work and I wont have to make 5000 changes.

 

[T. Valko]

If it uses the sheet name under the properties box in VBA instead of the name I give it, it will referance the sheet by it's location instead of my name. As long as the sheets are in order, all my formulas will still work and I wont have to make 5000 changes.

Here's how you can reference a sheet by a number. I think you'll agree that it may not be worth the trouble if you have very long complicated formulas.

Create this defined named formula:

Name: SheetNames
Refers to: =GET.WORKBOOK(1)&T(NOW())

Then, to link to cell A1 on sheet #3:

=INDIRECT("'"&INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),3)&"'!A1")

The red highlighted number is the sheet number. It refers to the physical location of the sheet.

If these are the sheets:

\_Master_/\_Jones_/\_Smith_/\_King_/\_Jackson_/\_Morris_/

Then the above formula is referencing sheet Smith.

 

[T. Valko]

Here's how you can reference a sheet by a number. I think you'll agree that it may not be worth the trouble if you have very long complicated formulas.

Create this defined named formula:

Name: SheetNames
Refers to: =GET.WORKBOOK(1)&T(NOW())

Then, to link to cell A1 on sheet #3:

=INDIRECT("'"&INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),3)&"'!A1")

The red highlighted number is the sheet number. It refers to the physical location of the sheet.

If these are the sheets:

\_Master_/\_Jones_/\_Smith_/\_King_/\_Jackson_/\_Morris_/

Then the above formula is referencing sheet Smith.

Also, if you use this method and you're using Excel 2007 or later the file must be saved in the macro enabled file format of *.xlsm.