I need a variable name to contain the values of several cells

[Rich]

I need a variable name to contain the values of several cells.
For example, If the value of cell A2 = "J10" and the value of cell B1 = "2",
I need a variable named "J10-S2". I will be creating a series of these
variable names from cells A2-A40 and B1-H1.

The value assigned to each of these variables will be the value of the cell
at the intersection of these cells. The value of the variable "J10-S2" will
be the value of cell B2. I can handle this part of the problem...I'm just
drawing a blank on creating and intiializing the variable name.

Can anyone get me started?

 

[ryguy7272]

Ok, look here for some info. on named ranges:
http://www.cpearson.com/excel/named.htm

Chip shows you how to have dynamically expanding ranges too. If you don't
know what that is, you will learn, and soon you'll wonder how you ever lived
without such a convenience.

HTH,
Ryan---

 

[Rich]

Thanks for replying, but I don't think that is addressing my
question....maybe I'm missing something, though.

I don't need variable "a" to refer to a range of cells.

It's the name of the variable itself that I'm trying to assign. If the
value of cell A2 = "J10", value of cell B1 = "2" and the value of cell B2 =
"15", I need a variable named "J10-S2" - in other words, something similar
to: (range("a2").value & "S-" & range("B1").value) . Then, I need to assign
the value of the cell at the intersection of the row/column of A2 and B1
(cell B2) to the variable "J10-S2". So, Variable "J10-S2" = 15.

Here is what I'm trying to do:
I have a series of "part numbers" that occupy range A2:A40 on multiple
worksheets. I have a series of "shipping sequences" that occupy range B1-P1
on multiple worksheets. So, There are 15 pieces of part number "J10" in
sequence #2.

One set of worksheets is the "master set", which contains the complete
quantity for each of the parts in each shipping sequence. I'm creating a
"partial release set" copy of the "master set", of which I may adjust
quantities for any part number in any sequence. Hence, I need to then create
a "balance set" of worksheets that will show the difference between the
"master set" and the "partial release set".

The only way that comes to my mind is to determine the quantity of each part
number in each sequence in the "master set", subtract it from the
corresponding part number and sequence in the "partial release set", then
show that difference in the "balance set".

I want to scan thru the "master set" once, collect the quantity for each
part number in each sequence, then scan thru each "partial release set" once
and subtract those quantities from the quantities I already stored from the
"master set" and then go thru the "balance set" once and fill in the
differences.

So, let's say with "partial release #1", I'm releasing 5 pieces of part
number "J10" in sequence #2. I need the "balance sheet" to show I still need
to release 10 pieces of "J10" for sequence #2. Then I might release 7 more
with "partial release #2", leaving 3 pieces on the balance sheet to release.
etc....