Dynamic Name Range for each Worksheet

[cgrablew]

I have a spreadsheet with several worksheets and I want to do a data validation that is based on the content of that particular worksheet. When I create a named range using =offset(Sheet1!C3,-2,-1,4,0) for Sheet 1, I would like the sheet name to change based on the sheet I'm on. So when I'm in Sheet2, I would like the name range to be dynamic and change the sheet name to Sheet2. I tried just putting in the cell reference when creating the name range, but Excel always puts the sheet name in the offset function depending on the sheet I'm on. Anyone know of a way to make the sheet name dynamic in the offset function when using a name range?

 

[pgc01]

Hi
Welcome to the board

=offset(Sheet1!C3,-2,-1,4,0)

Is the 0 it a typo?

Anyway, you can use, for ex.:

=offset(indirect("C3"),-2,-1,4,1)

or, refer to the local sheet

=offset(!$C$3,-2,-1,4,1)

Remark: I seem to remember having problems with refreshing with this last syntax but that is many years ago with a very old excel version (maybe 97?).

 

[cgrablew]

Thanks for your response - What I forgot to mention is that the C3 must be a relative reference so it changes with each row. So the indirect won't work. Its probably easier if I describe what I'm doing. I have a NCAA basketball bracket in Excel and want to do a data validation on the second and subsequent rounds so that that only choices are what the guy picked. For Example:

Mich
___________ Underline should have a data validation with Mich & Ohio as valid
Ohio

Texas
____________ Underline should have a data validation with Texas or Butler as valid
Butler

So If the underlines are in Column C, the offset is creating a dynamic name range for that column and the other worksheets which are the other people picks, should be able to access the same name range that is able to change based on the worksheet.

Hope this clear,

Chuck