named reference: same cell, different worksheets

this is weird and frustrating. I've got 8 sheets in the same workbook. the first 7 sheets starts off looking the same, and each is filled in with different data according to the needs of my office that week. the last sheet acts as a "compilation" sheets, with data fed from the first 7 sheets.

In preparation for creating the 7 identical sheets, I've created one, and I've named individual cells in the worksheet, which I then reference in the compilation. however, I cannot use the same name on two different sheets. I know that I can use the name 'Sheet1!Unit' and 'Sheet2!Unit' but this will cause hours of work to rename the named fields in each worksheet.

so that's what's frustrating: I can reference a specific (non-named) cell on one worksheet and then reference the same cell on another worksheet by using its direct reference (i.e. Sheet1!B34 and Sheet2!B34). so why can't I use Sheet1!Unit and Sheet2!Unit?

thanks.

I know that I can use the name 'Sheet1!Unit' and 'Sheet2!Unit' but ... so why can't I use Sheet1!Unit and Sheet2!Unit?

As you pointed out, you can.

There's a difference in names having workbook scope and names having sheet scope, but I can't parse your question.

I see that the way I asked this was confusing.

I have created one sheet, with many individually named cells. I need to copy this sheet's contents into 6 other sheets in the same workbook. however, this seems to be functionally impossible, since I cannot have 2 cells with the same name on two different sheets in the same book. renaming each named cell, on each worksheet, would take days.

I can reference an un-named cell by using its sheet name and cell reference. hence, Sheet1!B34 and Sheet2!B34 refer to two different cells. but in order to do the same thing using names, I must first name the two cells using the Sheet1!Unit and Sheet2!Unit naming convention, which would be too time consuming.

should I simply give up on the idea of naming cells and simply use the Sheet1!B34 style to reference what I need to compile?

I think I still don't undertand your question.

Make a bunch of names on one sheet, with workbook scope -- no data, just names. Now copy that sheet one or more times. Then all the copied sheets will have the same names, but with sheet scope.

Now add a new worksheet. Any reference on the new worksheet to a named range will refer to the first worksheet, where the name has workbook scope.

Any reference on one of the copied sheets will refer to the local name.

You can refer to a named range on any other sheet by preceeding it with the sheet name.

wow, ok thank you, this worked.

I didn't know that I could create a sheet with names, copy that sheet, and the names would automatically become sheet-based references. I had been testing this all with existing sheets. thanks for the help!!

You're welcome, glad it helped.