CODEWITHSUNDEEP
excelvba
JSM
JSM

Reputation: 225

Excel VBA - Calling individual cells in a defined name

I have defined a column by the column after it (i.e. Data1 -> Label1, Data2 -> Label2, etc.) I found I need to add a data field to my form, and it will also be dependent on the currently named column (i.e. Extra1 -> Label 1, etc.).

I can create all new labels, but I would rather have both of the cells be dependent on the same label. I have played around on a scrap sheet, and cannot make a cell equal to a defined name's cells when there are more than one. If I only have one, I have no problem:

A         B
Data1     Label1
=Label1

Will give me Data1 in row two.

A         B         C
Data1     Data2     Label1
=Label1

Will give me a #VALUE error.

How do I grab an individual cell from a defined name like this? Is it a range? Trying to grab it in VBA as Label1.Cells(x, x) give me an error as well. Am I going to have to make a separate label for the second data column (not ideal).

Thanks,

Upvotes: 0

Views: 525

Answers (1)

L42
L42

Reputation: 19727

You can use INDEX Function to get Data1 in row two:

=INDEX(Label1,1,1)

To return Data2 in your example

=INDEX(Label1,1,2)

Edit1: This is VBA equivalent

Range("Label1")(1) '~~> return Data1
Range("Label1")(2) '~~> return Data2

Or you can be explicit

Range("Label1").Item(1, 1) '~~> return Data1

Here's VBA using WorsheetFunction

WorksheetFunction.Index(Range("Label1"), 1, 1) '~~> return Data1

Upvotes: 1

Related Questions