Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
getting value from a different cell...?
If anyone can help me with this, I would be greatful. I created a very
simplified version of my problem below, but an answer to this can solve my real problem. I am at a loss on how to associate the expense next to the name with the dollar value in another column, then maintain a running sum for each individual. I have an expense sheet that has expenses by name, (ie car) and another that assigns a dollar value to that car. Each name can have multiple expenses (ie below fred has car car misc meal). Nbr 1 below. Another section, Nbr 2 below, assigns an expense value to each item. I need a formula that answers nbr 3 Cells B5 through B8. It says: this person had these four items, it checks the value of the each, adds them up and gives me a total expense.... 1) A B C D E 1 fred car car misc meal 2 mike meal car phone misc 3 jim meal phone car misc 4 bill phone misc meal meal 2) A B 5 car 100 6 meal 50 7 phone 10 8 misc 25 3) A B 9 fred 275<------- Need formula for this column 10 mike 185 11 jim 185 12 bill 135 Any help greatfully appreciated! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
getting value from a different cell...?
I would do this.
I'd find x contiguous columns (H:xxx???) and put this in row one of those columns: Car meal Phone Misc xxx yyyy zzzz Then in each row of real data (starts with row 2): In H2: =countif($b2:$e2,h$1)*vlookup(h$1,sheet2!$a:$b,2,f alse) The $ signs are important. When the formula is dragged across and down the range, the addresses will change nicely. it counts the number of Cars in B:E and multiplies that by the value in the table in sheet2. Then I'd use another column that just summed those "detail" amounts. =sub(h2:xxxx2) Maybe even put that sum column to the left of those detail amounts. ==== I like keeping the details (even if I hide those columns for reporting). But then I can do nice things--data|Filter|autofilter or data|subtotals or data|pivottable to get summaries for each category. Samuel wrote: If anyone can help me with this, I would be greatful. I created a very simplified version of my problem below, but an answer to this can solve my real problem. I am at a loss on how to associate the expense next to the name with the dollar value in another column, then maintain a running sum for each individual. I have an expense sheet that has expenses by name, (ie car) and another that assigns a dollar value to that car. Each name can have multiple expenses (ie below fred has car car misc meal). Nbr 1 below. Another section, Nbr 2 below, assigns an expense value to each item. I need a formula that answers nbr 3 Cells B5 through B8. It says: this person had these four items, it checks the value of the each, adds them up and gives me a total expense.... 1) A B C D E 1 fred car car misc meal 2 mike meal car phone misc 3 jim meal phone car misc 4 bill phone misc meal meal 2) A B 5 car 100 6 meal 50 7 phone 10 8 misc 25 3) A B 9 fred 275<------- Need formula for this column 10 mike 185 11 jim 185 12 bill 135 Any help greatfully appreciated! -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
getting value from a different cell...?
This works but may not be suitable for a more complex version of your
requirement: H1:I4 contains your table (section Nbr 2) A10:A13 contains section Nbr 3 Put in B10 and copy down =SUMPRODUCT(($A$1:$A$4=$A10)*(($B$1:$E$4=$H$1)*$I$ 1+($B$1:$E$4=$H$2)*$I$2+($B$1:$E$4=$H$3)*$I$3+($B$ 1:$E$4=$H$4)*$I$4)) I am sure there is a more "sophisticated" way to do this. "Samuel" wrote: If anyone can help me with this, I would be greatful. I created a very simplified version of my problem below, but an answer to this can solve my real problem. I am at a loss on how to associate the expense next to the name with the dollar value in another column, then maintain a running sum for each individual. I have an expense sheet that has expenses by name, (ie car) and another that assigns a dollar value to that car. Each name can have multiple expenses (ie below fred has car car misc meal). Nbr 1 below. Another section, Nbr 2 below, assigns an expense value to each item. I need a formula that answers nbr 3 Cells B5 through B8. It says: this person had these four items, it checks the value of the each, adds them up and gives me a total expense.... 1) A B C D E 1 fred car car misc meal 2 mike meal car phone misc 3 jim meal phone car misc 4 bill phone misc meal meal 2) A B 5 car 100 6 meal 50 7 phone 10 8 misc 25 3) A B 9 fred 275<------- Need formula for this column 10 mike 185 11 jim 185 12 bill 135 Any help greatfully appreciated! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
getting value from a different cell...?
On Feb 28, 9:55 am, Toppers wrote:
This works but may not be suitable for a more complex version of your requirement: H1:I4 contains your table (section Nbr 2) A10:A13 contains section Nbr 3 Put in B10 and copy down =SUMPRODUCT(($A$1:$A$4=$A10)*(($B$1:$E$4=$H$1)*$I$ 1+($B$1:$E$4=$H$2)*$I$2+(*$B$1:$E$4=$H$3)*$I$3+($B $1:$E$4=$H$4)*$I$4)) I am sure there is a more "sophisticated" way to do this. "Samuel" wrote: If anyone can help me with this, I would be greatful. I created a very simplified version of my problem below, but an answer to this can solve my real problem. I am at a loss on how to associate the expense next to the name with the dollar value in another column, then maintain a running sum for each individual. I have an expense sheet that has expenses by name, (ie car) and another that assigns a dollar value to that car. Each name can have multiple expenses (ie below fred has car car misc meal). Nbr 1 below. Another section, Nbr 2 below, assigns an expense value to each item. I need a formula that answers nbr 3 Cells B5 through B8. It says: this person had these four items, it checks the value of the each, adds them up and gives me a total expense.... 1) A B C D E 1 fred car car misc meal 2 mike meal car phone misc 3 jim meal phone car misc 4 bill phone misc meal meal 2) A B 5 car 100 6 meal 50 7 phone 10 8 misc 25 3) A B 9 fred 275<------- Need formula for this column 10 mike 185 11 jim 185 12 bill 135 Any help greatfully appreciated!- Hide quoted text - - Show quoted text - Thank you both. I understand what you are each saying and I think I can work with it. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inputting cell value from source cell based on value in adjacent cell. | Excel Discussion (Misc queries) | |||
cell data not validated if navigating cell to cell with mouse | Excel Worksheet Functions | |||
How to create/run "cell A equals Cell B put Cell C info in Cell D | Excel Discussion (Misc queries) | |||
Cell Formula reference to cell Based On third Cell Content | Excel Discussion (Misc queries) | |||
Cell Formula reference to cell Based On third Cell Content | Excel Discussion (Misc queries) |