What I am trying to do: Create cell labels that are a composite of the
contents of other cells.

Example: An investment club contributions sheet for all months and all
members

A1 = Name
A2 = John Smith
B1 = January
B2 = $500

I want to create a label for cell B2 such that it takes from $A2 and
$B$1 so that the label is something like "January:John Smith" .... or
something similar....

Why?

Because I will create a seperate sheet for each month where I can
reference this master sheet and show the current month (and perhaps the
previous 3 months) contributions for each member.

I can currently do this by just referencing by reference (position)...
but I want to make this a little smarter so that when members are added
or deleted from the master, all monthly sheets will always be correct
because they are referencing a smartly labeled cell.

So... how to create the labels dynamically - and how to reference them
dynamically in the monthly report sheets.

Thanks,

John

Try something like this:

=B1&":"&A2
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Johnny Google" wrote in message
ups.com...
What I am trying to do: Create cell labels that are a composite of the
contents of other cells.

Example: An investment club contributions sheet for all months and all
members

A1 = Name
A2 = John Smith
B1 = January
B2 = $500

I want to create a label for cell B2 such that it takes from $A2 and
$B$1 so that the label is something like "January:John Smith" .... or
something similar....

Why?

Because I will create a seperate sheet for each month where I can
reference this master sheet and show the current month (and perhaps the
previous 3 months) contributions for each member.

I can currently do this by just referencing by reference (position)...
but I want to make this a little smarter so that when members are added
or deleted from the master, all monthly sheets will always be correct
because they are referencing a smartly labeled cell.

So... how to create the labels dynamically - and how to reference them
dynamically in the monthly report sheets.

Thanks,

John

RD,

Thanks, but perhaps I wasn't clear with my use of the word 'Label'. I
mean to say I want to create a named cell (i.e. John Smith:January)
such that it can referenced later if the name and month are known. Not
to produce that content in the cell. The content of the cell will be
$500, for example.

Does this make sense? I can try to re-explain if not.

On the January sheet, there will be another column of names and I want
to reference the values for contributions for each member for that
month accurately. So, my hunch is that if I can have the cell
label/name created by reference, and be able to call it by reference, I
can avoid getting incorrect data - down the road.

Thanks,

John


RagDyer wrote:
Try something like this:

=B1&":"&A2
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Johnny Google" wrote in message
ups.com...
What I am trying to do: Create cell labels that are a composite of the
contents of other cells.

Example: An investment club contributions sheet for all months and all
members

A1 = Name
A2 = John Smith
B1 = January
B2 = $500

I want to create a label for cell B2 such that it takes from $A2 and
$B$1 so that the label is something like "January:John Smith" .... or
something similar....

Why?

Because I will create a seperate sheet for each month where I can
reference this master sheet and show the current month (and perhaps the
previous 3 months) contributions for each member.

I can currently do this by just referencing by reference (position)...
but I want to make this a little smarter so that when members are added
or deleted from the master, all monthly sheets will always be correct
because they are referencing a smartly labeled cell.

So... how to create the labels dynamically - and how to reference them
dynamically in the monthly report sheets.

Thanks,

John

AFAIK, this can't be done ... perhaps with code.

BUT, I don't really see a need for it.
You can reference the contributions by name and time frame relatively
easily.

Say you have each of the names going down Column A, with the monthly
contributions going across the columns, say from "Jan" in Column B to "Dec"
in Column M.

With names in say A2 to A100, and months in *text* form, from B1 to M1,
Enter a name to find in say N1, and in N2 to N4 (you did say 3 months) you
could enter 1 *or* 2 *or* a 3rd month to total.

You could then try a formula something like this:

=SUMPRODUCT((A2:A100=N1)*((B1:M1=N2)+(B1:M1=N3)+(B 1:M1=N4))*B2:M100)

Does this sound like something you could use?

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Johnny Google" wrote in message
ups.com...
RD,

Thanks, but perhaps I wasn't clear with my use of the word 'Label'. I
mean to say I want to create a named cell (i.e. John Smith:January)
such that it can referenced later if the name and month are known. Not
to produce that content in the cell. The content of the cell will be
$500, for example.

Does this make sense? I can try to re-explain if not.

On the January sheet, there will be another column of names and I want
to reference the values for contributions for each member for that
month accurately. So, my hunch is that if I can have the cell
label/name created by reference, and be able to call it by reference, I
can avoid getting incorrect data - down the road.

Thanks,

John


RagDyer wrote:
Try something like this:

=B1&":"&A2
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"Johnny Google" wrote in message
ups.com...
What I am trying to do: Create cell labels that are a composite of the
contents of other cells.

Example: An investment club contributions sheet for all months and all
members

A1 = Name
A2 = John Smith
B1 = January
B2 = $500

I want to create a label for cell B2 such that it takes from $A2 and
$B$1 so that the label is something like "January:John Smith" .... or
something similar....

Why?

Because I will create a seperate sheet for each month where I can
reference this master sheet and show the current month (and perhaps the
previous 3 months) contributions for each member.

I can currently do this by just referencing by reference (position)...
but I want to make this a little smarter so that when members are added
or deleted from the master, all monthly sheets will always be correct
because they are referencing a smartly labeled cell.

So... how to create the labels dynamically - and how to reference them
dynamically in the monthly report sheets.

Thanks,

John

Let's take a simpler example:

Here is an example of data on one sheet for listing stock prices:

YHOO GOOG IBM
Jan 25.00 300.00 80.00
Feb 26.00 310.00 81.00
Mar 27.00 320.00 82.00

Since I must reference this sheet for each month's sheet and it will be
different for months when we own different stocks etc, I would like to
just reference a named cell.

For example, if I named each cell manually, it might look like this:

YHOO_JAN GOOG_JAN IBM_JAN
YHOO_FEB GOOG_FEB IBM_FEB
YHOO_MAR GOOG_MAR IBM_MAR

I could then use these named cells on the sheets for each month and
insure the values would always be correct whereas using just the actual
letter/number combinations would require I always check that it is
correct when some months we may not own the same stocks.

What I would like to do, instead of manually labeling each cell AND
instead of entering each reference to that named cell manually - I
would like to see if either one or both of the following is possible:

1) Have the label / name created dynamically from the other cells
contents

2) Be able to reference the named cell by dynamically using the month
and stock name instead of something like B3. Because B3 may not always
be correct in a different month's report.

If 1 is not possible, I can create the names manually, but 2 would be
at least half way there. Is 2 possible?

Thanks,

John

Hi John

Firstly you can do what you want within a single sheet without creating
any named ranges.
Turn on ToolsOptionsCalculationAccept labels in formula
Then if you type
= Feb Goog it will return a value of 310.00
Note the space between Feb and Goog.

It will not work however, if you type =Sheet2!Feb Sheet2!Goog

You can have Excel create a whole series of named ranges for you.
With your sample data in A1:D4, mark the block A1:D4 and choose
InsertNameCreate tick Top row and Left Column
Excel will create a series of named ranges for you.
Repeat the procedure on other sheets, and it will create a further
series of named ranges for you, each set will be sheet specific.

Now you can type =Sheet3!Feb Sheet3!Goog (again note the space between
labels) and it will find the relevant data from that sheet.
This method can get very unwieldy however, and is not one that I would
choose to use.

If you set out your data with Months in B1:M1
and your Names in A2:Ann where nn can be up to 65536 rows this should be
sufficient to hold all of your data for the year.
To pick off individual values you could then use Index and Match
functions.

Suppose all of the data is on Sheet1.
InsertNameDefineName Mydata Refers to mark the whole block of
data
InsertNameDefineName Months Refers to Sheet1!$A$1:$M$1
InsertNameDefineName Name Refers to Sheet1!$A:$A

On any other sheet, enter Month required in B1, Name in A2 and in B2
enter
=INDEX(Mydata,MATCH($A2,Name,0),MATCH(B$1,Months,0 ))
and that will return the appropriate value.
If there is no value for that stock for that month, then it will return
0

If your data on the Master sheet is likely to grow in length during the
year (with more names being added) then I would make Mydata a dynamic
range. In the Refers to dialogue box enter
=OFFSET($A$1,0,0,COUNTA($A:$A),13)
Now you can add more data to the end of your sheet and it will get added
into the range automatically.
You can mark the whole block of data and sort Ascending by column A (be
sure to tick that the range has Headers) and this will keep your data
alphabetically sorted, and will not affect the return of the correct
values to other sheets using the Index, Match formulae.

--
Regards

Roger Govier


"Johnny Google" wrote in message
ups.com...
Let's take a simpler example:

Here is an example of data on one sheet for listing stock prices:

YHOO GOOG IBM
Jan 25.00 300.00 80.00
Feb 26.00 310.00 81.00
Mar 27.00 320.00 82.00

Since I must reference this sheet for each month's sheet and it will
be
different for months when we own different stocks etc, I would like to
just reference a named cell.

For example, if I named each cell manually, it might look like this:

YHOO_JAN GOOG_JAN IBM_JAN
YHOO_FEB GOOG_FEB IBM_FEB
YHOO_MAR GOOG_MAR IBM_MAR

I could then use these named cells on the sheets for each month and
insure the values would always be correct whereas using just the
actual
letter/number combinations would require I always check that it is
correct when some months we may not own the same stocks.

What I would like to do, instead of manually labeling each cell AND
instead of entering each reference to that named cell manually - I
would like to see if either one or both of the following is possible:

1) Have the label / name created dynamically from the other cells
contents

2) Be able to reference the named cell by dynamically using the month
and stock name instead of something like B3. Because B3 may not always
be correct in a different month's report.

If 1 is not possible, I can create the names manually, but 2 would be
at least half way there. Is 2 possible?

Thanks,

John