Formula Question...

[andyinla]

My General Ledger is an excel file generated by Finance and has a column with invoice numbers. My Cost Report is a separate excel file that also has an invoice column with invoice numbers

I want to do a formula that will place the month in GL, actual month "02/2009", if the invoice numbers both appear in both reports, General Ledger and Cost Report, so I don't have to go through the Generl Ledger line by line and check did invoice number 66758 apear on my GL for Feb and is that number in Cost Report as well..

How do I go about doing that formula?

Thank you so much

Andy

 

[jasonb75]

Hi Andy.

This could be done with lookup and an error check, something like

=IF(ISERROR(VLOOKUP($A2,'COST REPORT'!$A:$A,1,FALSE)),"","02/2009")

$A2 should be the cell with the invoice number in the current row.
$A:$A should be the column in the cost report that contains the invoice numbers.
'COST REPORT' should be the file and sheet name where the column above is found (if you leave it like it is, as long as there is not a sheet called COST REPORT in the GL workbook, excel should open a window asking you to select the file and sheet that it should be looking at).

Note this formula will always enter "02/2009" as the month, if the month should be entered as found elsewhere then it can be referenced by changing the formula.

 

[andyinla]

Jason

Thank you for your help, I think I am almost there...
here is what I have....

=IF(ISERROR(VLOOKUP($K423,'Cost Report'!$A:$A,1,FALSE)),'[Copy of Andrew Haspel (4).xls]Job Cost Detail Report 3'!$G:$G"","02/2009")

[Copy of Andrew Haspel (4).xls]Job Cost Detail Report 3'!$G:$G
this is reference to my General Ledger file and referencing the column with the invoice numbers

What do you think?

 

[jasonb75]

Andy

Try

=IF(ISERROR(VLOOKUP($K423,'[Copy of Andrew Haspel (4).xls]Job Cost Detail Report 3'!$G:$G,1,FALSE)),"","02/2009")

the filename and sheet name '[Copy of Andrew Haspel (4).xls]Job Cost Detail Report 3' should be the opposite file to the one in which you enter the formula.