Referencing cells

[JAF]

Is ther a way in Excel 2003 to use labels or words to refernce cells.

For example instead of a formula being A1+C1, is there a way to call
column A something like Jan_Sales and column B something like
Feb_Sales and then the formula would be Jan_Sales1 + Feb_Sales1?

Thanks for your help

JF
http:\\www.discountdrivingschool.com


Best regards,

JAF
http://www.discountdrivingschool.com

 

[Stan Brown]

Is ther a way in Excel 2003 to use labels or words to refernce cells.

For example instead of a formula being A1+C1, is there a way to call
column A something like Jan_Sales and column B something like
Feb_Sales and then the formula would be Jan_Sales1 + Feb_Sales1?

Yes. Insert | Name | Define.

 

[JAF]

Yes. Insert | Name | Define.

I don't think this is what I am looking for. The "Name" you mention
refers to the whole colum, row or range of cells.

I need a cell reference, not a column or row reference, or am I
amissing something in your post?

Again what I would like is something like this

row Jan Feb Mar
1 5 5 5
2 3 3 3
3 4 4 4

And if I had a formula like Jan2 + Feb1 + Mar3, my formula would pull
in Jan2 which is 3, Feb1 which is 5, and Mar3 which is 4.

Your insert name lets me reference the whole column.

thanks for the post anyway.

Best regards,

JAF
http://www.discountdrivingschool.com

 

[suro]

u can define the each cell such as jan1 feb1 mar1
while u also can define the column jan feb mar;define the row:
r1,r2,r3
then the formular can be written like that:
=jan r1+feb r1+mar r1

Hope this can be helpful.

Regards,

Angine

 

[JAF]

u can define the each cell such as jan1 feb1 mar1
while u also can define the column jan feb mar;define the row:
r1,r2,r3
then the formular can be written like that:
=jan r1+feb r1+mar r1

Hope this can be helpful.

Regards,

Angine

Thanks for your post, but if I understand you correctly, I would have
to define each and every cell. We have 60,000 rows with 35 to 50
columns of data, so doing this on each cell would not work.

Here is my problem: we receive spreadsheets from various offices
around the country. We are expected to analyze these and make
accounting entries based on our analysis.

But the spreadsheets don;t come to us with the columns in the same
order. From month to month, the offices seem to reorder the colums.

Thus we could get a report with columns like this:

region district location qty cost

and the next month the columns could be in this order

location qty cost region district

This is a simple example. With 50 columns in our actual spreadsheets,
the column order can vary much more.

so we spend at least half an hour per analysis simply reordering
columns. One important point to note is that the column headers are
always named the same, they are just in different orders.

Since we have 48 analysis to do, that translates into about 24 hours a
month we lose in simply reformatting columns, and if a spreadsheet
arrives late and we have to work overtime to produce results, it can
be costly.

So since the columns are always named the same, I was jhoping to be
able to reference cells by the column names + row number, in order to
negate the need for reording the columns.

All suggestions I have seen on this newsgroup so far, don't seem to
present an answwer. (I do appreciate everyone taking the time to post
though.)

Does anyone know of a way to help? Is there some way to reorder
columns automatically, or is there some way to use the column headers
in the cell references?


Best regards,

JAF
http://www.discountdrivingschool.com

 

[Stan Brown]

I don't think this is what I am looking for. The "Name" you mention
refers to the whole colum, row or range of cells.

?? I don't know where you got that. You can make names for cells or
ranges.

I need a cell reference, not a column or row reference, or am I
amissing something in your post?

I don't believe you need a cell reference; I believe you need a range
reference.

Define JAN as one column and FEB as another. Then you add them up by
highlighting the target column, typing =JAN+FEB in the formula bar,
and hitting Ctrl-Shift-Enter. (Make the obvious adjustment if you
have more than two columns.)

However, ...

I don't see why you want to go through all the hassles of creating
names in the first place. Create the first sum, then click and drag
to propagate it to the additional rows.

 

[Ragdyer]

This is one approach you might like to play with:

Name your *entire* column as you please.
For example ... for Column A,
Select the column header ( A ),
Click in the Name Box (left of the formula bar),
Type in "JAN" (no quotes),
Hit <Enter>

Do the same for Columns B & C, using "FEB" and "MAR".

NOW, a formula to reference the cells within each column would look like
this, if you wanted A4:
=Index(JAN,4)

Therefore, a calculation of the sum of A4 plus B12 being divided by C9 would
look like this:

=(Index(JAN,4)+Index(FEB,12))/Index(MAR,9)

If this formula returned something you might label "First Quarter", you
could then name the formula itself, say "Qtr1".

Select the *entire* formula in the *formula bar*, right click and choose
"Copy",
Then hit <Enter>
NOW, from the menu bar:
<Insert> <Name> <Define>

In the "Names In Workbook" box, type
Qtr1
Then, select the *entire* "Refers To" box, and do a :
<Ctrl> < V >
Which will paste the formula in there,
Then <OK>

Now, if you type,
=Qtr1
in a cell, you get the results of that formula.

You could now use that to say, guess at a yearly number, by doing this:
=Qtr1*4

You might be able to *pre-calculate* all your sheets this way, and then,
when the *NEW* data is imported,
You'll have to go *again* into <Insert> <Name> <Define>
Click on say "JAN" in the Big window,
Then click in the "Refers To" box,
Hit <F2> to enter the Edit Mode,
Then revise the column reference to whatever column represents JAN in this
import version.

You will *not* have to revise the "Qtr1" named formulas, since they will
*automatically* pick up your *new, revised* column references.

 

[anon]

Thanks for your post, but if I understand you correctly, I would have
to define each and every cell. We have 60,000 rows with 35 to 50
columns of data, so doing this on each cell would not work.

Here is my problem: we receive spreadsheets from various offices
around the country. We are expected to analyze these and make
accounting entries based on our analysis.

But the spreadsheets don;t come to us with the columns in the same
order. From month to month, the offices seem to reorder the colums.

Thus we could get a report with columns like this:

region district location qty cost

and the next month the columns could be in this order

location qty cost region district

This is a simple example. With 50 columns in our actual spreadsheets,
the column order can vary much more.

so we spend at least half an hour per analysis simply reordering
columns. One important point to note is that the column headers are
always named the same, they are just in different orders.

Since we have 48 analysis to do, that translates into about 24 hours a
month we lose in simply reformatting columns, and if a spreadsheet
arrives late and we have to work overtime to produce results, it can
be costly.

So since the columns are always named the same, I was jhoping to be
able to reference cells by the column names + row number, in order to
negate the need for reording the columns.

All suggestions I have seen on this newsgroup so far, don't seem to
present an answwer. (I do appreciate everyone taking the time to post
though.)

Does anyone know of a way to help? Is there some way to reorder
columns automatically, or is there some way to use the column headers
in the cell references?

Absolutely. Let's define a few assumptions with respect to the
workbook you get from your offices:

0) There is precisely one worksheet and it is named "Sheet1"
1) There are a variable number of columns
2) To the extent a column is included, it always has the same value in
the header row (row 1)
3) Columns can appear in any order

What I think you want is to create a new worksheet. Let's create a
workbook with a dummy Sheet1 (or you can use any example that you have
lying around, so you aren't working with a dummy Sheet1, but a real
example Sheet1). You then create a Sheet2 in that workbook.

Let's assume that you want the order of the columns in Sheet2 to be:

region district location qty cost

but they appear in Sheet1 as

location qty cost region district

This is set up for 5 columns, but the theory will work for 35 or 50.

So, first, put those headers into row 1 of Sheet2 ("location" into A1,
"qty" into B1, etc.)

Then, in Cell A2 put:

A2:=HLOOKUP(A$1,Sheet1!$A$1:$E$60000,ROW(),FALSE)

Copy A2 down as far as you need it

Then copy A2 through Axxxxx (where xxx is "as far as you need it" from
above) to each additional column (B through E in case of 5 columns,
more in case of 35 columns)

Your Sheet2 will now have all of the values from Sheet1 in the column
order you want.

You can than turn everything in Sheet2 into values by:

1) ctl-A
2) ctl-C
3) alt-e-s
4) v
and click OK

Then copy sheet2 to a new workbook and save (rename Sheet2 to whatever
you want in the new workbook).

Once you have the workbook with Sheet2 in it, it should be a 20 second
operation to get you your new worksheet in the order you want it to
be.

Jim

 

[anon]

Absolutely. Let's define a few assumptions with respect to the
workbook you get from your offices:

0) There is precisely one worksheet and it is named "Sheet1"
1) There are a variable number of columns
2) To the extent a column is included, it always has the same value in
the header row (row 1)
3) Columns can appear in any order

What I think you want is to create a new worksheet. Let's create a
workbook with a dummy Sheet1 (or you can use any example that you have
lying around, so you aren't working with a dummy Sheet1, but a real
example Sheet1). You then create a Sheet2 in that workbook.

Let's assume that you want the order of the columns in Sheet2 to be:

region district location qty cost

but they appear in Sheet1 as

location qty cost region district

This is set up for 5 columns, but the theory will work for 35 or 50.

So, first, put those headers into row 1 of Sheet2 ("location" into A1,
"qty" into B1, etc.)

Oops. I meant:

So, first, put those headers into row 1 of Sheet2 ("region" into A1,
"district" into B1, etc.)