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.
--
Earl Kiosterud
www.smokeylake.com
"LoganStallworth"
<LoganStallworth.1vzcqa_1127761504.2398@excelforum-nospam.com> wrote in
message news:LoganStallworth.1vzcqa_1127761504.2398@excelforum-nospam.com...
>
> 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.
>
>
> --
> LoganStallworth
> ------------------------------------------------------------------------
> LoganStallworth's Profile:
> http://www.excelforum.com/member.php...o&userid=27561
> View this thread: http://www.excelforum.com/showthread...hreadid=470856
>
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.
--
Earl Kiosterud
www.smokeylake.com
"LoganStallworth"
<LoganStallworth.1w0fma_1127811906.1284@excelforum-nospam.com> wrote in
message news:LoganStallworth.1w0fma_1127811906.1284@excelforum-nospam.com...
>
> 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.
>
>
> --
> LoganStallworth
> ------------------------------------------------------------------------
> LoganStallworth's Profile:
> http://www.excelforum.com/member.php...o&userid=27561
> View this thread: http://www.excelforum.com/showthread...hreadid=470856
>
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.
--
Earl Kisoterud
www.smokeylake.com
"Earl Kiosterud" <someone@nowhere.com> wrote in message
news:eax5WU4wFHA.3400@TK2MSFTNGP14.phx.gbl...
> 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.
> --
> Earl Kiosterud
> www.smokeylake.com
>
> "LoganStallworth"
> <LoganStallworth.1w0fma_1127811906.1284@excelforum-nospam.com> wrote in
> message
> news:LoganStallworth.1w0fma_1127811906.1284@excelforum-nospam.com...
>>
>> 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.
>>
>>
>> --
>> LoganStallworth
>> ------------------------------------------------------------------------
>> LoganStallworth's Profile:
>> http://www.excelforum.com/member.php...o&userid=27561
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=470856
>>
>
>
I appreciate your help and I do have a lot of products. About for hundred of them. This is going to be tedious.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks