Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP, Sorting, Calculate
I have a project to create a "running" report that calculates various
formulas for each particular jobID, but the job ID's aren't entered yet ( to be entered in the future in another worksheet within the same workbook), and when they are, their are multiple jobID's to be summed up to create one jobID with one value. This is my scenario- Expenses! (worksheet) has the following data A1 = Date B1 = jobID C1 = totalexpense note: one jobID may appear several times in B column depending on the number of purchases made for that jobID Profits! (worksheet within same workbook as above) has the following data A1 = jobID B1 = totalexpense C1 = formulas I will enter later How can I have Profits!A1 automatically sort through Expenses!A1:B100 to add up the totals expenses for each respective jobID AS THE JOBID's ARE BEING ENTERED IN THE FUTURE? For example, jobID100 apears 8 times with different values each time. I need jobID100 to appear in Profits!A1 and the cumulative total of the 8 times an expense was entered for jobID100 entered in Profits!B1. Profits!B1 needs to be able to change in the event jobID100 appears again in the future. Thanks in advance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP, Sorting, Calculate
ASSUMING expenses can be sorted by JOBID then in Profit:
A2: =Expenses!B2 A3: =OFFSET(Expenses!$B$2,COUNTIF(Expenses!$B$2:$B$500 0,"<="& Profit!A2),0) and copy down until 0 value as JobID B2: =SUMPRODUCT(--(Expenses!$B$2:$B$5000=Profit!$A2)*(Expenses!$C$2: $C$5000)) and copy down Change ranges of B & C to suit. HTH "rldjda" wrote: I have a project to create a "running" report that calculates various formulas for each particular jobID, but the job ID's aren't entered yet ( to be entered in the future in another worksheet within the same workbook), and when they are, their are multiple jobID's to be summed up to create one jobID with one value. This is my scenario- Expenses! (worksheet) has the following data A1 = Date B1 = jobID C1 = totalexpense note: one jobID may appear several times in B column depending on the number of purchases made for that jobID Profits! (worksheet within same workbook as above) has the following data A1 = jobID B1 = totalexpense C1 = formulas I will enter later How can I have Profits!A1 automatically sort through Expenses!A1:B100 to add up the totals expenses for each respective jobID AS THE JOBID's ARE BEING ENTERED IN THE FUTURE? For example, jobID100 apears 8 times with different values each time. I need jobID100 to appear in Profits!A1 and the cumulative total of the 8 times an expense was entered for jobID100 entered in Profits!B1. Profits!B1 needs to be able to change in the event jobID100 appears again in the future. Thanks in advance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP, Sorting, Calculate
Alternatively, look at using a pivot table.
"Toppers" wrote: ASSUMING expenses can be sorted by JOBID then in Profit: A2: =Expenses!B2 A3: =OFFSET(Expenses!$B$2,COUNTIF(Expenses!$B$2:$B$500 0,"<="& Profit!A2),0) and copy down until 0 value as JobID B2: =SUMPRODUCT(--(Expenses!$B$2:$B$5000=Profit!$A2)*(Expenses!$C$2: $C$5000)) and copy down Change ranges of B & C to suit. HTH "rldjda" wrote: I have a project to create a "running" report that calculates various formulas for each particular jobID, but the job ID's aren't entered yet ( to be entered in the future in another worksheet within the same workbook), and when they are, their are multiple jobID's to be summed up to create one jobID with one value. This is my scenario- Expenses! (worksheet) has the following data A1 = Date B1 = jobID C1 = totalexpense note: one jobID may appear several times in B column depending on the number of purchases made for that jobID Profits! (worksheet within same workbook as above) has the following data A1 = jobID B1 = totalexpense C1 = formulas I will enter later How can I have Profits!A1 automatically sort through Expenses!A1:B100 to add up the totals expenses for each respective jobID AS THE JOBID's ARE BEING ENTERED IN THE FUTURE? For example, jobID100 apears 8 times with different values each time. I need jobID100 to appear in Profits!A1 and the cumulative total of the 8 times an expense was entered for jobID100 entered in Profits!B1. Profits!B1 needs to be able to change in the event jobID100 appears again in the future. Thanks in advance. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP, Sorting, Calculate
hi rldjda
you have an interesting post for me... 1. Is the plan to create a JOBID will be in series (eg. 100,101,102) ? 2. Which will be in series for FUTURE entry on Expense! ? The DATE or the JOBID.... OR BOTH? 3. In the Profit! "since you will prepare a formula in column C " which maybe the PROFIT per JOBID", are you planning to sum the planned expense or the Actual Expense which are entered on Expense! by the Dates<=Today() or Today()....? the word future entry for me seems you need the ACTUAL in reference to TODAY()... regards ill the entry of JOBID and Expenses in Expense! will be in series -- ***** birds of the same feather flock together.. "rldjda" wrote: I have a project to create a "running" report that calculates various formulas for each particular jobID, but the job ID's aren't entered yet ( to be entered in the future in another worksheet within the same workbook), and when they are, their are multiple jobID's to be summed up to create one jobID with one value. This is my scenario- Expenses! (worksheet) has the following data A1 = Date B1 = jobID C1 = totalexpense note: one jobID may appear several times in B column depending on the number of purchases made for that jobID Profits! (worksheet within same workbook as above) has the following data A1 = jobID B1 = totalexpense C1 = formulas I will enter later How can I have Profits!A1 automatically sort through Expenses!A1:B100 to add up the totals expenses for each respective jobID AS THE JOBID's ARE BEING ENTERED IN THE FUTURE? For example, jobID100 apears 8 times with different values each time. I need jobID100 to appear in Profits!A1 and the cumulative total of the 8 times an expense was entered for jobID100 entered in Profits!B1. Profits!B1 needs to be able to change in the event jobID100 appears again in the future. Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup isn't working correctly? | Excel Discussion (Misc queries) | |||
formula to lookup value and return value from cell at left of target | Excel Worksheet Functions | |||
VLOOKUP and its properties | Excel Worksheet Functions | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions |