Formula and Data Lookup/Import Help Please!

[Guest]

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]

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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Guest]

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?

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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

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]

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.

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?

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 here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

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!