Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Spreadsheet,Not sure what to use? Vlookup works for first
I have a excel problem with a formula for a spreadsheet, basically I am
trying to combine two accounting spreadsheets. Lets call them spreadsheet A & B. All my values are in spreadsheet A and this contains four columns. see below Code Description Division Amount 1 COGS sales 10 1 COGS manufacturing 15 2 expenses sales 12 2 expenses manufacturing 17 Now I need the amount in spreadsheet B which is organized like this SALES Manufacturing COGS EXPENSES Now for sales i have put in=vlookup(A2,spreadsheet B!A2:D5,4,False) and this works but it doesnt work for manufacturing as it brings up the first thing it find (for example it brings $10 and $12) I gues my question is How do I create a formula that takes columns one and three into account from another spreadsheet in order to get column four in a different spreadsheet? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Spreadsheet,Not sure what to use? Vlookup works for first
I'm not sure if I'm reading your columns correctly... it looks like your
source data has the division in column C and description in column B (though your vlookup formula suggests that the description is in column A). Based on what it looks like: =sumproduct(--('spreadsheet B'!$C$2:$C$5=B$1),--('spreadsheet B'!$B$2:$B$5=$A2),'spreadsheet B'!$D$2:$D$5)) The first two arguments of the sumproduct are arrays of 1s and 0s; 1 where there's a match, 0 where there's not. The third argument is the corresponding amount. Sumproduct multiplies corresponding elements of each array and adds them up. The multiplication results in 0 whenever either match fails, but results in the 'amount' when both matches succeed. Then all the amonts from the successful matches get added. If you're open to a non-formula approach, also try a pivot table, with the Description in the row field, Department in the column field and Amount in the Data field. "Getz" wrote: I have a excel problem with a formula for a spreadsheet, basically I am trying to combine two accounting spreadsheets. Lets call them spreadsheet A & B. All my values are in spreadsheet A and this contains four columns. see below Code Description Division Amount 1 COGS sales 10 1 COGS manufacturing 15 2 expenses sales 12 2 expenses manufacturing 17 Now I need the amount in spreadsheet B which is organized like this SALES Manufacturing COGS EXPENSES Now for sales i have put in=vlookup(A2,spreadsheet B!A2:D5,4,False) and this works but it doesnt work for manufacturing as it brings up the first thing it find (for example it brings $10 and $12) I gues my question is How do I create a formula that takes columns one and three into account from another spreadsheet in order to get column four in a different spreadsheet? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Spreadsheet,Not sure what to use? Vlookup works for f
Thanks for the quick reply and sorry for the confusion. I guess really column
1 and column 2 are the same and explain the account name, column 3 is the accounting department and column 4 is the $amount. In the new spreadsheet I only want to display the $amount. Now to understand this sum product equation, the first two parts of the formula define whether it is true, then if they are both true the third is displayed? Is this how this reads. So for my example the amount in cell D2 is $10, to get this i need a formula that says if A is 1 and C is sales, then D is amount shown? "bpeltzer" wrote: I'm not sure if I'm reading your columns correctly... it looks like your source data has the division in column C and description in column B (though your vlookup formula suggests that the description is in column A). Based on what it looks like: =sumproduct(--('spreadsheet B'!$C$2:$C$5=B$1),--('spreadsheet B'!$B$2:$B$5=$A2),'spreadsheet B'!$D$2:$D$5)) The first two arguments of the sumproduct are arrays of 1s and 0s; 1 where there's a match, 0 where there's not. The third argument is the corresponding amount. Sumproduct multiplies corresponding elements of each array and adds them up. The multiplication results in 0 whenever either match fails, but results in the 'amount' when both matches succeed. Then all the amonts from the successful matches get added. If you're open to a non-formula approach, also try a pivot table, with the Description in the row field, Department in the column field and Amount in the Data field. "Getz" wrote: I have a excel problem with a formula for a spreadsheet, basically I am trying to combine two accounting spreadsheets. Lets call them spreadsheet A & B. All my values are in spreadsheet A and this contains four columns. see below Code Description Division Amount 1 COGS sales 10 1 COGS manufacturing 15 2 expenses sales 12 2 expenses manufacturing 17 Now I need the amount in spreadsheet B which is organized like this SALES Manufacturing COGS EXPENSES Now for sales i have put in=vlookup(A2,spreadsheet B!A2:D5,4,False) and this works but it doesnt work for manufacturing as it brings up the first thing it find (for example it brings $10 and $12) I gues my question is How do I create a formula that takes columns one and three into account from another spreadsheet in order to get column four in a different spreadsheet? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Spreadsheet,Not sure what to use? Vlookup works for f
You can build the values you're looking for directly into the formula; I was
trying to give you a formula that pulled the target values from the header row and column in your output table. But for your specific example of sales COGS: =sumproduct(--('spreadsheet B'!$C$2:$C$5="sales"),--('spreadsheet B'!$a$2:$a$5=1),'spreadsheet B'!$D$2:$D$5)) (I'd encourage you to try the prior formula; the same formula should work for each cell in your output table!) "Getz" wrote: Thanks for the quick reply and sorry for the confusion. I guess really column 1 and column 2 are the same and explain the account name, column 3 is the accounting department and column 4 is the $amount. In the new spreadsheet I only want to display the $amount. Now to understand this sum product equation, the first two parts of the formula define whether it is true, then if they are both true the third is displayed? Is this how this reads. So for my example the amount in cell D2 is $10, to get this i need a formula that says if A is 1 and C is sales, then D is amount shown? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Spreadsheet,Not sure what to use? Vlookup works for f
Thanks a ton, i am going to try to hammer this out. I am not and excel guru
though but at some point ill get this. I appreciate your time |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Spreadsheet,Not sure what to use? Vlookup works for first
Hi,
You could also try to use the following array formula 9Ctrl+Shift+Enter) =sum(if((range1="COGS")*(range2="Sales"),sum_range )) Please replace "COGS" and "Sales" with the references in your table -- Regards, Ashsih Mathur Microsoft Excel MVP www.ashishmathur.com "Getz" wrote in message ... I have a excel problem with a formula for a spreadsheet, basically I am trying to combine two accounting spreadsheets. Lets call them spreadsheet A & B. All my values are in spreadsheet A and this contains four columns. see below Code Description Division Amount 1 COGS sales 10 1 COGS manufacturing 15 2 expenses sales 12 2 expenses manufacturing 17 Now I need the amount in spreadsheet B which is organized like this SALES Manufacturing COGS EXPENSES Now for sales i have put in=vlookup(A2,spreadsheet B!A2:D5,4,False) and this works but it doesnt work for manufacturing as it brings up the first thing it find (for example it brings $10 and $12) I gues my question is How do I create a formula that takes columns one and three into account from another spreadsheet in order to get column four in a different spreadsheet? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert ms works spreadsheet to excel spreadsheet on pda | Excel Discussion (Misc queries) | |||
MS Works Spreadsheet 2000 v5.0 VLOOKUP | Excel Worksheet Functions | |||
ms works spreadsheet | Excel Discussion (Misc queries) | |||
conversion of MS Works Spreadsheet to Excel 2002 Spreadsheet | Excel Discussion (Misc queries) | |||
How do I convert exel spreadsheet to works spreadsheet? | Excel Discussion (Misc queries) |