Is this an array constant application?

[JenniferMurphy]

I have a list of products, each with a set of properties (Name, Catalog Number, Price, Weight, etc.). I would like to create an array in one sheet with all of this data and be able to access it from any other sheet in the workbook by name, rather than by cell numbers.

Suppose my product table is in Sheet1 and looks like this:

C/R	A	B	C	D
1	Name	CatNo	Price	Weight
2	ProdA	K2932	$24.99	5.5
3	ProdB	G16	$14.99	3.1
4	ProdC	MX915	$9.99	2.3

<tbody>
</tbody>

Now in Sheet2, I would like to be able to access any property of any product using labels rather than cell numbers.

For example, assuming I have a cell named "Quan", I would like to calculate the total cost of ordering Quan units of ProdB. I could use the formula:

=Quan*Sheet1!C3

Is there any way I can use a formula like one of the following?

=Quan*Sheet1!Price!ProdB
=Quan*ProdB!Price
=Quan*ProdB.Price
=Quan*ProdB(Price)

Is there a way to do that?

Thanks

 

[Rick Rothstein]

Try this...

1) Select Column C and name it Price
2) Select Row 3 and name it ProdB

Then you can use this formula (note the space between ProdB and Price... it is important as it is the intersection operator)...

=Quan*(ProdB Price)

You can name your weight column if you need to get to it individually... and, of course, name your other product rows so you can reference them as well.

 

[JenniferMurphy]

Try this...

1) Select Column C and name it Price
2) Select Row 3 and name it ProdB

Then you can use this formula (note the space between ProdB and Price... it is important as it is the intersection operator)...

=Quan*(ProdB Price)

You can name your weight column if you need to get to it individually... and, of course, name your other product rows so you can reference them as well.

Very nice. Thank you very much.

One thing I forgot to mention. The product names in the calling table will be in their own column. Will that formula work if one or both or the names in a cell, rather than typed explicitly?

Here's my calling table. In B2, [=ProdB Price] works, but [=A2 Price] does not.

C/R	A	B	C	D
1	Product	Unit Price	Quantity	Total
2	ProdB	??	10	??
3	ProdC	??	75	??

<tbody>
</tbody>

Is there a way to use your intersection formula with one or both of the axes in a cell?

 

[Rick Rothstein]

One thing I forgot to mention. The product names in the calling table will be in their own column. Will that formula work if one or both or the names in a cell, rather than typed explicitly?

Here's my calling table. In B2, [=ProdB Price] works, but [=A2 Price] does not.

Is there a way to use your intersection formula with one or both of the axes in a cell?

The intersection is of a row and a column. That is why I suggested naming each row for the product so that you could use the name directly. However, if you want to do it with an address instead, you would not use A2 because that is a cell (which does not intersect the Price column), rather, you need to use the row that the product is on...

=Quan*(2:2 Price)

 

[JenniferMurphy]

I didn't explain myself very well. I do want to name the rows and columns just as you suggested and I do want to use those names in the formulas. I just want one of the names to be in a cell, rather than typed explicitly in the formula.

Suppose my product table is in Sheet1 and looks like this:

C/R	A	B	C	D
1	Name	CatNo	Price	Weight
2	ProdA	K2932	$24.99	5.5
3	ProdB	G16	$14.99	3.1
4	ProdC	MX915	$9.99	2.3

<tbody>
</tbody>

Col C is named Price and Row 3 is named ProdB.


In Sheet2, I have this table:

C/R	A	B	C	D
1	Product	Unit Price	Quantity	Total
2	ProdB	??	10	??
3	ProdC	??	75	??

<tbody>
</tbody>

In B2, I want the unit price for ProdB and I want to get it from the table in Sheet1.

If I put either [=ProdB Price] or [=3:3 Price], I get $14.99, which is correct. But that doesn't make use of the value in A2 ("ProdB"). If I change A2 to "ProdA", I want the value in B2 to change to $24.99.

Is there a way to do that?

 

[Rick Rothstein]

If I put either [=ProdB Price] or [=3:3 Price], I get $14.99, which is correct. But that doesn't make use of the value in A2 ("ProdB"). If I change A2 to "ProdA", I want the value in B2 to change to $24.99.

Is there a way to do that?

Use the INDIRECT function to form the row reference via the defined name as text...

=Quan*(INDIRECT(A2) Price)

I am not sure of your layout, but you will have to preface the A2 reference with a reference to the sheet it is on if that sheet is not the one the formula is on.

 

[JenniferMurphy]

Use the INDIRECT function to form the row reference via the defined name as text...

=Quan*(INDIRECT(A2) Price)

I am not sure of your layout, but you will have to preface the A2 reference with a reference to the sheet it is on if that sheet is not the one the formula is on.

Perfect. Thank you so much.

I defined the names in the lookup table as global names, so I didn't need the explicit sheet reference.

Here's a sample workbook.

https://www.dropbox.com/s/d6ivqh9hr5e0e7v/Intersect Operator.xlsx?dl=0

Maybe it will be helpful to others...

 

[Rick Rothstein]

Perfect. Thank you so much.

I defined the names in the lookup table as global names, so I didn't need the explicit sheet reference.

Here's a sample workbook.

https://www.dropbox.com/s/d6ivqh9hr5e0e7v/Intersect Operator.xlsx?dl=0

Maybe it will be helpful to others...

I was referring to the A2 reference itself... if your A2 cell was on a different sheet, the you would need to preface the A2 with the sheet name it was on, otherwise the formula would use the A2 cell on the sheet where the formula is located. For example, if the formula was on Sheet1 and the A2 cell you were using was on Sheet2, then you would need to use this formula instead...

=Quan*(INDIRECT(Sheet2!A2) Price)

I am guessing since everything is working for you that your A2 cell is on the same sheet as the formula itself.

 

[JenniferMurphy]

I was referring to the A2 reference itself... if your A2 cell was on a different sheet, the you would need to preface the A2 with the sheet name it was on, otherwise the formula would use the A2 cell on the sheet where the formula is located. For example, if the formula was on Sheet1 and the A2 cell you were using was on Sheet2, then you would need to use this formula instead...

=Quan*(INDIRECT(Sheet2!A2) Price)

I am guessing since everything is working for you that your A2 cell is on the same sheet as the formula itself.

No, it is not on the same sheet. It works (I think) because the row and column names are global to the workbook.

I've uploaded a better workbook to illustrate this here:

https://www.dropbox.com/sh/u4f64gptxikup84/AAAzeFlwWCz4H2PEBDv6o848a?dl=0

This folder contains the original workbook and a new one that expands on the previous one in that it shows the formulas needed to make it work. That required a UDF I wrote some time ago with help from people here, so it's a macro-enabled workbook.