basically... if date value present, add corresponding dollar value.

[chestersneakers7]

I've attached a sample workbook. Basically what I need done is finding "expenses incurred (po amounts) - not received (no invoice yet)" values (this is on sheet2 m1). In workbook, on sheet1 row3, there are five po's listed but only three have invoices (known by whether having a date or invoice number). So, total sub cost is $5 (total po cost) minus the po's that have corresponding invoices gives us $2. On sheet3 row44, is the total sub cost. I really do hope this makes sense, I've got the worksheet filled out but with no formulas. Thanks!

[scott.s.fower]

i would use two helper columns (possibly DD and DE) where you use a count formula

DD=COUNT(BA3,BG3,BM3,BS3,BY3,CE3,CK3,CQ3,CW3,DC3) <--your Invoice Date columns = 3
DE=COUNT(AW3,BC3,BI3,BO3,BU3,CA3,CG3,CM3,CS3,CY3) <--your Amount columns = 5
then sheet2!M3 = Sheet1!DE3-Sheet1!DD3 = 2

or

sheet2!M3=COUNT(Sheet1!AW3,Sheet1!BC3,Sheet1!BI3,Sheet1!BO3,Sheet1!BU3,Sheet1!CA3,Sheet1!CG3,Sheet1!CM3,Sheet1!CS3,Sheet1!CY3)-COUNT(Sheet1!BA3,Sheet1!BG3,Sheet1!BM3,Sheet1!BS3,Sheet1!BY3,Sheet1!CE3,Sheet1!CK3,Sheet1!CQ3,Sheet1!CW3,Sheet1!DC3)=2

or

sheet2!M3=Sheet3!B44-COUNT(Sheet1!BA3,Sheet1!BG3,Sheet1!BM3,Sheet1!BS3,Sheet1!BY3,Sheet1!CE3,Sheet1!CK3,Sheet1!CQ3,Sheet1!CW3,Sheet1!DC3)=2

[chestersneakers7]

Thanks for helping me. The second formula seems to have only found how many po's did not have invoices. I really need to have the total of what invoices I do have to be taken out of the total po amount so I can see what is outstanding. The third formula gave me a date... I did not try the first as I'm trying to work with an existing layout... Not trying to be difficult or anything though. Thanks again for these ideas!

[aydeegee]

Hi Chestersneakers7

I would love to help, but am totally confused. I would normally give up at this stage, but here goes.

(I have re-read this and it could be read as being aggressive. This is not the case - I am trying to find out if I can help)

You say (in effect) that you are looking for the uninvoiced POs. Sheet 3 seems to have the total POs.

On Sheet1 AV1 says "PO forSub 1" and then lists 3 names against it a,d and b in AV3,AV4 and AV5

On Sheet1 columns B:AU are blank. Will these remain fixed, or might you delete some of them?

Is there any scope for changing the layout of Sheet3 ? I realise thAt there may be a good reason for it, but it does make it very difficult to analyse information. (As Scott clearly demonstrates). For analysis purposes it would be easiest to have:

Sub # | ? Sub Name ? | Amount | PO # | PO Date | Invoice # | Invoice Date |

I also note that there is no way of dealing with the situation where the invoice is different from the PO.

You say that receipt of an invoice is indicated by an invoice number OR a date. Making one test will simplify matters.

I look forward to hearing from you

Regards
Alastair

[chestersneakers7]

Hey! No, no, nothing aggressive about that post at all. Sheet1 has Site ID's in column a, I had the panes froze but maybe that was undone. If so, yeah, you'd never know there was values all the way at a when we're working down on column av. So for Site ID 1 (sheet1 row3), it had five total subs, thus five po's, three po's have corresponding invoices, I need the dollar total of the ones that don't have invoices to be subtracted from the total sub cost (total po - sheet3 b44) so I can fill in sheet2 m3. There is a value in sheet2 m3 now because I manually typed it in there for demonstration purposes. I would like it to be updated automatically as I fill in the blanks on sheet1 with new po and invoice information. I hope this makes a little more sense. If not, I am more than willing to try again. Thanks so much!

[aydeegee]

Hi Chestersneakers7

As I said it is easier if the layout is different.

So I have written a macro that puts the data into some sensible order and then produced that totals that you want. Just for good measure I have also gicen you the breakdown of Orders raised and Invoiced

You need to click on the blue rectangle if you alter the data on sheet 1. There is a restriction of 20,000 rows. No particular reason, but there has to be some limit!

Let me know if it works for you.

Regards
Alastair

[chestersneakers7]

I see what you did and it seems to work but I really need the layout I had to remain. I've attached an updated book to maybe provide more of an insight on why. This book is slam packed with info and formulas that are intertwined and auto updated from other sheets. So I put some of that which is relevant in this working example. But I really need column M on sheet2 to be auto figured using what information I have available and in current layout. So if a Site ID on sheet1 does not have a corresponding Invoice Number next to a listed PO Amount, I need the total of the missing invoice values to be placed into sheet2 column M. Thanks guys and gals!

[aydeegee]

Hi Chestersneakers7

Sorry if I did make iot clear, but I have not altered your layout. I have created a new sheet "Workings", but the answer feeds in to your Sheet3. The 3 totals in that may be either copied or linked to your sheet 2, column M (I had left the easy bit for you to complete)

I think that this achieves what you requested - please let me know if this is not the case.

Regards
Alastair

[chestersneakers7]

Your sheet3 has headers labeled as "PO's Not Invoiced, Invoiced and PO's Raised." I can not do that in this application... On my sheet3, you'll notice there is no headers, just Site ID's going across row2. On my real spreadsheet there are almost 1000 different Site ID's going across. They all use the OFFSET formula to fill in data. It would be too messy to edit. I can't think of a way to auto-fill the "Workings" page either (that would be the only way this could work due to the large volume of Site IDs). Each Site ID on sheet1 can have up to 10 PO's and corresponding Invoices (but not all do), sheet1 column av to column dc. And there are, again, around 1000 Site IDs. It seems like a bigger "to do" that I'm looking for... There's got to be a way to stream line the process to find sheet2 m3:m1003 (give or take)... I do appreciate your input and any other you might have. Thanks guys!

[aydeegee]

Hi Chestersneakers7

The 1000 sites certainly make a difference! On my sheet3 I was trying to be clever (helpful?) by showing "Invoiced" and "PO raised" which is unneccessary - so delete columns F - M.

Your sheet2 can remain with 1000 sites, picking up the figures from sheet3

The macro will autofill the "Workings" but will only accommodate 1,000,000 or so sites . (Note that the "Sub" column gets its information from Sheet1 AV1,BB1 et seq.)

Please get back to me if this does not work

Regards
Alastair

[chestersneakers7]

i've been trying to toy with this more now that i'm back from the field. on sheet3 where it lists sub1-sub10 (a5-a14), i need every sub cost to be carried over (invoiced or not), as it totals the sub costs at the bottom at a43. as your example sits now, it would only total the po's not invoiced. sheet3 also has the sub cost listed out per sub. what i need is sheet2 m3-m1000 to total all the subs that don't have invoices. i don't need to see on sheet3 one by one who has not been invoiced as that information is on sheet1. just need the totals of what hasn't yet been invoiced and put into sheet2 m3-m1000. thanks again!