Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula and Data Lookup/Import Help Please!
Hi,
Ive recently been given a new task at work to create a report of all overdue invoices on excel 2007. Each record has basically a due date, and the amount of the invoice, so i've used the formula (for example): =SUMIF(A2:A30, "<=19/10/2007", B2:B30) to sum the value of each invoice that is overdue (i.e has a due date less than today). The first problem is that the "TODAY()" tool doesn't work in the formula, nor does "NOW()", so i manually have to enter the days date in the formula. The second problem is how to extract the value this formula produces, and how to put it into a new spreadsheet (i.e. the report im required to produce), as there are many different spreadsheets this has to be done for. Any help would be greatly appreciated and will save me a fair amount of time each week! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula and Data Lookup/Import Help Please!
Thank you for the first bit about using the TODAY tool, a bit confused about
the second part though, all I need to do is copy the value that is given by this formula and put it into a new spreadsheet, because as i stand now i have to go into each spreadsheet and manually copying the values for each set of overdue invoices given by the formula i mentioned. Does what you've put do that? "Dave Peterson" wrote: Try: =SUMIF(A2:A30, "<="&today(), B2:B30) or if you have to specify a date: =SUMIF(A2:A30, "<="&date(2007,10,19), B2:B30) Maybe you could just include a similar formula in the "receiving" workbook: =SUMPRODUCT(--([book1.xls]Sheet1!$A$2:$A$30<=&today(), [book1.xls]Sheet1!$B$2:$B$30) (=sumif() won't work if the sending workbook is closed.) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html wrote: Hi, Ive recently been given a new task at work to create a report of all overdue invoices on excel 2007. Each record has basically a due date, and the amount of the invoice, so i've used the formula (for example): =SUMIF(A2:A30, "<=19/10/2007", B2:B30) to sum the value of each invoice that is overdue (i.e has a due date less than today). The first problem is that the "TODAY()" tool doesn't work in the formula, nor does "NOW()", so i manually have to enter the days date in the formula. The second problem is how to extract the value this formula produces, and how to put it into a new spreadsheet (i.e. the report im required to produce), as there are many different spreadsheets this has to be done for. Any help would be greatly appreciated and will save me a fair amount of time each week! -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula and Data Lookup/Import Help Please!
If you copy|paste special|values, then if the data changes, your other worksheet
won't reflect the change. You could use a formula in the second workbook/worksheet that points at the cell with the formula in the first worksheet. But if you alter the layout with the receiving book closed, you may find that you're not pointing at the correct cell anymore. (You could use a named range to avoid that problem, though.) But the second formula just mimics the first =sumif(). They should return the same value. wrote: Thank you for the first bit about using the TODAY tool, a bit confused about the second part though, all I need to do is copy the value that is given by this formula and put it into a new spreadsheet, because as i stand now i have to go into each spreadsheet and manually copying the values for each set of overdue invoices given by the formula i mentioned. Does what you've put do that? "Dave Peterson" wrote: Try: =SUMIF(A2:A30, "<="&today(), B2:B30) or if you have to specify a date: =SUMIF(A2:A30, "<="&date(2007,10,19), B2:B30) Maybe you could just include a similar formula in the "receiving" workbook: =SUMPRODUCT(--([book1.xls]Sheet1!$A$2:$A$30<=&today(), [book1.xls]Sheet1!$B$2:$B$30) (=sumif() won't work if the sending workbook is closed.) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html wrote: Hi, Ive recently been given a new task at work to create a report of all overdue invoices on excel 2007. Each record has basically a due date, and the amount of the invoice, so i've used the formula (for example): =SUMIF(A2:A30, "<=19/10/2007", B2:B30) to sum the value of each invoice that is overdue (i.e has a due date less than today). The first problem is that the "TODAY()" tool doesn't work in the formula, nor does "NOW()", so i manually have to enter the days date in the formula. The second problem is how to extract the value this formula produces, and how to put it into a new spreadsheet (i.e. the report im required to produce), as there are many different spreadsheets this has to be done for. Any help would be greatly appreciated and will save me a fair amount of time each week! -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need formula to lookup data in row | Excel Worksheet Functions | |||
Formula to lookup data and enter it in another WB | Excel Worksheet Functions | |||
Formula to lookup matching data | Excel Discussion (Misc queries) | |||
how to Import data in excel office2003 with data more 65000 rows | Excel Worksheet Functions | |||
ENTER A FORMULA IN A CELL TO UTILIZE THE IMPORT DATA FUNCTIONS? | Excel Worksheet Functions |