Master formula in one sheet used by another

[tbihn]

I have a predefined parameter spreadsheet setup to read in a test sequence. It has two tabs. One tab is labeled "Bay1" and the other tab is labeled "Bay2". The test software that reads this is locked down, so I am limited to my ability to change the structure of the spreadsheet. They have this setup for flexibility so that you can have different tests in different cells, but it creates a problem...There is no way to guard against an update a sequence in the "Bay1", but don't update "Bay2". The ONLY difference between these two sheets is that "Bay1" references "Zebra1" and "Bay2" references "Zebra2". The way this spreadsheet is structured, you can add delays and other test sequence steps by moving rows around. I'd like to setup a formula in one sheet that references the sheet name (to extract the 1 or 2) and in the other sheet, use that formula to get the sheet name.

Is this actually possible without a macro? I want to set this up so that "Bay2" is locked and if someone wants to make a change, it only occurs on the first sheet.

 

[SkipVought]

hi,

I'd like to setup a formula in one sheet that references the sheet name (to extract the 1 or 2) and in the other sheet, use that formula to get the sheet name.

That statement is not clear to me.

Please post some sample data that illustrates the issue. Then explain exactly what information you expect to get from the expression.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

I get it - you want Bay 2 to reflect changes made in Bay 1 to formulae etc but with any sheet etc references in the formulae to be modified to suit the sheet name - 1 changed to 2 etc

Sorry but imho this is a code or nothing job

code could be relatively simple but that depends on the requirements exactly and what you are attempting to trap / change - if it's just formulae then fine but if you want to trap row / column insertion / deletion / sorting etc you've got a pretty big job on your hands

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Rofeu]

Well, if it's just about getting the sheet name you can subtract that from (with some help from RIGHT LEFT and LEN functions):

=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))


To create a formula with a variable sheet reference, you could take a look at the INDIRECT worksheetfunction.

However from your description it is not clear to me if this alone will get you there.

Cheers,

Rofeu