Referencing Dancing Columns from another worksheet

[Anderson]

I am using MS Excel 2003 SP2
I saw the Excel help example "About labels and names in formulas" which
explained how to use Column headers in a formula. I want to use this
functionality because the column headers remain constant but headers may end
up in different colums (ie "Product 1" may be in B1 or in B3).

Division Product 1 Product 2 Product 3
East 70 30 16
West 20 80 14
Total 90 110 30

the example shows the formula "=Product 3 East" with a result of 14 which
is what I would expect and this to works in my test spreadsheet. How do I
reference these cells from another worksheet or workbook. I get an error
when I try =Sheet!Product 3 East
Thank you
Anderson

 

[Roger Govier]

Hi Anderson

You will get a #NAME error when trying to do what you tried.
That method only works within a sheet.

I also think you will need to use underscore within your Product names -
Product_1, otherwise Excel will be confused with the extra space

If you go mark your whole Table and use Insert>Name>Create> tick labels in
Top row and Left column

Then on another sheet type
=Product_3 East
You will get the result

 

[excelent]

Try with underscore

=Sheet!Product_3 East

"Anderson" skrev:

 

[Anderson]

The space characters do actually work. However the "underscore" character
doesn't work properly. Probably a bug in MS Excel.

If I use the "Name" functionality, I believe it maps the Name to a
particular cell reference. Then if the worksheet column headers move to a
differenct cell, the formula utilizing the "Name" would now be picking up the
incorrect information.
Thank you
Anderson

 

[Anderson]

Roger,
I used the "name" approach you suggested and it works even when the columns
are moved around.

Is there a way to reference the column header - name with the row so when I
copy paste a formula it will behave like regular cell references?

For example =Product1 id2 + Product2 id3

When I copy the above formula to another cell it copies as is. I would
like to be able to copy the formula to work as such

=Product1 id2 + Product2 id2 (where id2 equals the row number)

=Product1 id3 + Product2 3 (where 3 equals the row)

Currently, I have to manually key in the row reference I would like to be
able to copy and paste and have the row automatically incremented. Any way
to do this?

 

[Roger Govier]

Hi Anderson

Firstly, you should uses longer names than ID1, id2 etc, as they could be
interpreted as cells.
Using Pid1, Pid2 could not be confused with a cell Identity (unless you are
using XL2007)

You can't use a variable using the Intersect method.
However, you could use
=INDEX(Product2,Row(A3))+INDEX(Product3,Row(A3))

Row() returns the row number. It doesn't matter what column letter you use,
so Row(A3) will return 3, and when copied down it will become Row(A4),
Row(A5) etc.
You only need named ranges for the columns with this method, but the row
names won't cause any harm.

Will that suit your needs?

 

[Anderson]

Roger,
the index command worked great embedded in an if statement. Have you ever
seen a problem where the column generated by this command will not sort as
expected.
=IF(INDEX(PAT_Owner, ROW($B11)) ="Mfg", INDEX(Brickchart_Name,ROW($B11)), "")


Engineering (Header)
3DCS Analyst
3DCSi

Acrobat 3D
ADVISE
AutoSMP


BSML

CATIAV5
CATIAV5

When i try to sort on the above column, it does not sort out the empty
cells. It is as if it is sorting the column from which this data was
generated.
Any thoughts on why this would be happening?
Anderson