• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Getting Invoice Item ID to show up in TimeSheet Line Item after invoicing

ifoundthetao

New Member
Hey all,
Sorry about the crappy title. Sometimes solving a problem has more about your ability to understand what the problem is, than actually "solving" it.

And unfortunately for me, my Excel vocabulary is a bit limited.

I'm building a Project Management spreadsheet, which I have attached (with dumbed down and sanitized data), that I'm having a bit of difficulty with.

I have three sheets in my workbook: TimeSheet, Invoices, and Project Table

TimeSheet is where the hours worked at logged. Each activity has a start stamp, and and end stamp, with some other information in there. There's also a portion to reconcile whether or not this Log ID has been invoiced. If it has, then there's a "Y" that appears in a column.

What I want, is for the Invoice Item ID to appear there, instead.

I've tried all sorts of VLOOKUPs, HLOOKUPs, INDEX, Array Formulas, creating of new columns to combine data to make it easier to get a hook in there, but I just can't quite get it out.

So, I'm leaning on your support, to help me through this. :)

Also, I posted this on Reddit.com's Excel board (http://www.reddit.com/r/excel/comments/1nzzxv/help_getting_a_specific_value_for_multiple_fields/), but I don't think that is very active over there.

Thanks again!
 

Attachments

  • sample.xlsx
    19 KB · Views: 8
If i've understood you correctly then all you need to do is replace the formula in column H with the below, it warns of a circular reference but it doesn't actually exist, it's just because part of the vlookup range would create a circular ref if it was used but it's not, it's just a column in the range

IF(D2="","",IF(SUMIF(TimeSheet!C$1:C2,C2,G$1:G2)-SUMIF(Invoices!H:H,C2,Invoices!I:I)>0,"N",VLOOKUP(C2,Invoices!H2:J5,3,FALSE)))
 
Back
Top