Variable

  • Thread starter Thread starter LoganStallworth
  • Start date Start date
L

LoganStallworth

I want to be able to define variables. Is that possible.

What i'm aiming for here is if I know that object "a" costs me $6.00 I
want it to display the name of the object in a cell but hold the value
of 6.

Then if I totaled a column of objects I could have the total be
displayed as just the total of the numerical values disregarding the
names of the objects.

It seems like there should be an easy way to define variables but I
have no found one.
 
It isn't clear to me what you want. Any cell can be a variable. It can
have a name if defined in Insert - Name - Define. Then you use the name in
your formula. Or you can assign a name to a value, and not have it in a
cell (Insert - Name - Define, Name: a, Refers to: 5.

But the more typical thing is to put them in a table with the names in a
column, and the associated value in another. Then VLOOKUP can find the
record you want.
 
Ok.

Let me explain what I am trying to do.

I have a set of data that contains a group of Produced Items, and the
materials needed to produce those items.

I want to have a spreadsheet that has each produced item and the
materials that it takes to produce them. I want those materials to
appear as text in the cells, but I also want those cells to hold a
value. So for instance:

If it takes 4 2x4's to create a table, and I have assigned the value of
$5.00 to a two by four then want the spreadsheet to show that:

A1 = Table; B1 = 4 two by fours; C1 = $20.00

My aim here is to have sheet1 hold the table of produced items, and
sheet2 to hold the materials required. This is so I can change the
cost of a material such as two by four and it will change the value in
all the produced items that use two by fours respectfully. I also want
sheet1 to only show a produced item, the materials required to make that
item, and the value of all the materials together.

This could be done a number of round-about ways, but I figured if I
could make a variable twobyfour or two_by_four and assign it a value in
one sheet, and then have the first sheet display two_by_four in the cell
but also hold the same numerical value. That is my goal.
 
Logan,

Normally, a "variable" isn't used for data in this context. Instead, you'd
put your parts and their costs into a table:

Sheet A might look like this:

A B
1 Part Cost
2 2x4 6
3 2x2 4
4 4x8 ply 18
etc.

Then make a table of your product parts:

A B C
1 Product Part Cost
2 Table 2x2 =VLOOKUP(B2,'A'!$A$2:$B$200, 2, FALSE)
3 Table 2x4 =VLOOKUP(B3,'A'!$A$2:$B$200, 2, FALSE)
4 Table 4x8 ply =VLOOKUP(B4,'A'!$A$2:$B$200, 2, FALSE)
5 Chair 2x2 =VLOOKUP(B5,'A'!$A$2:$B$200, 2, FALSE)
etc.

If this stuff isn't lined up, you need to set your newsreader to use a
non-proportional font like Courier. After keying the first VLOOKUP, just
copy down with the Fill Handle.

Now you can change the prices in the table in sheet A, and get new results.
You could then use Data - Subtotals, or a Pivot table to summarize costs for
each product.
 
Logan,

Actually, you could make a single table for each product, rather than all
the products as I suggested in sheet B. This would be practical where you
don't have a lot of different products. You could then eliminate the first
column. Then you could just sum the costs with an ordinary formula.
 
I appreciate your help and I do have a lot of products. About fo
hundred of them. This is going to be tedious
 
Back
Top