Find value in cell to left of named range

[Jephph]

I'm designing a form and each datapoint is a named range. The cell directly to the left of the named range is the label for the datapoint. I want to create a list of all the labels in the form on another sheet and feel I need a formula that takes the named range and gives me the value of the cell to the left without having to name the label cell as well. The label name can change while the named range won't so I want my list of labels to update automatically. Google isn't helping me with this. Not sure it is even possible.

PS. In a perfect world, I'd have a list of the named ranges in column B and their Labels in column A and the value of the named range in column C, with the values in A and C based on what's written in B. Hope that makes sense. Couldn't find anything about using the contents of a cell as a named range reference in a formula.

PPS. Trying to do all of this without macros, but if that's the only way it has to be foolproof.

 

[AlphaFrog]

Name of Named Range is in B1

One column to the left of the name range

=OFFSET(INDIRECT(B1),0,-1)

Value of Named Range

=INDIRECT(B1)

 

[Jephph]

Thank you for replying so quickly! However, when I replace B1 with the name of a range, I get a #REF error.

For example...
=OFFSET(INDIRECT(SS_UnitType),0,-1)

 

[AlphaFrog]

If you use the Name within the formula, surround the name with quotes

=OFFSET(INDIRECT("SS_UnitType"),0,-1)

Or just use the Named Range without the INDIRECT Function...

=OFFSET(SS_UnitType,0,-1)
=SS_UnitType

 

[Jephph]

OK, we're getting there! Thank you!

However, I get a #REF error when I try this...
B1 = "SS_UnitType"
=OFFSET(INDIRECT(B1),0,-1)

 

[Toadstool]

Hi Jephph,

I'm not sure I'm following you. How can a single cell contain the value of a Named range?

Here's something along the lines of what you ask.

Jephph.xlsx
	C	D	E	F	G	H
1	Month		Month	1	2	3
2	Animal		Animal	Cat	Dog	Mole
Sheet1

Cell Formulas
Range	Formula
C1	C1	=INDEX($A$1:$W$97,ROW(MRange),COLUMN(MRange)-1)
C2	C2	=INDEX($A$1:$W$97,ROW(ARange),COLUMN(ARange)-1)

Named Ranges
Name	Refers To	Cells
ARange	=Sheet1!$F$2:$H$2	C1:C2
MRange	=Sheet1!$F$1:$H$1	C1:C2

 

[AlphaFrog]

OK, we're getting there! Thank you!

However, I get a #REF error when I try this...
B1 = "SS_UnitType"
=OFFSET(INDIRECT(B1),0,-1)

B1 should just be the name without quotes.

 

[Jephph]

B1 should just be the name without quotes.

My bad, it worked, I just had a typo and it was B10 rather than B1. Sorry. Thank you, thank you, thank you. Works beautifully.

 

[Jephph]

Hi Jephph,

I'm not sure I'm following you. How can a single cell contain the value of a Named range?

Here's something along the lines of what you ask.

Jephph.xlsx
	C	D	E	F	G	H
1	Month		Month	1	2	3
2	Animal		Animal	Cat	Dog	Mole
Sheet1

Cell Formulas
Range	Formula
C1	C1	=INDEX($A$1:$W$97,ROW(MRange),COLUMN(MRange)-1)
C2	C2	=INDEX($A$1:$W$97,ROW(ARange),COLUMN(ARange)-1)

Named Ranges
Name	Refers To	Cells
ARange	=Sheet1!$F$2:$H$2	C1:C2
MRange	=Sheet1!$F$1:$H$1	C1:C2

My named ranges contain a single value. For example, SS_UnitType references H10 on the Support Schedule sheet.

 

[Toadstool]

So if this is the Support Schedule

Jephph.xlsx
	E	F	G	H	I
4
5
6				Porky	Pig
7	Rover	Dog
8
9
10			Moley	Mole
11
Support Schedule

Then this would be the desired Results

Jephph.xlsx
	A	B	C
1	Label	Name	Address
2	Rover	Canine	'[Jephph.xlsx]Support Schedule'!$F$7
3	Porky	Porcine	'[Jephph.xlsx]Support Schedule'!$I$6
4	Moley	Cute	'[Jephph.xlsx]Support Schedule'!$H$10
Results

Cell Formulas
Range	Formula
A2:A4	A2	=INDEX('Support Schedule'!$A$1:$Z$9999,ROW(INDIRECT(B2)),COLUMN(INDIRECT(B2))-1)
C2:C4	C2	=CELL("address",INDIRECT(B2))