Lookup(?) problem -

[Rob8222]

Hello gents,
I'm trying to finish one file for my restaurant, it is composed by 12 sheets, one for each month of the year, inside any sheet i have:
Column A - date the goods arrive at the restaurant
Column B - the supplier
Column C - amount to be paid
Column D - type of payment (wire, cash, check etc)
Column E - date the payment needs to be done latest

Some supplier are paid net30-60 so for some cells we insert "to be paid"

To have an eye on payments to be done, i created another sheet called Tobepaid, inside this sheet i would have each rows in which there is "to be paid" of all months.
So i need to excel do a search inside the 12 sheets with months, on column D and if there is a "to be paid", add the relative rows in the sheet Tobepaid

The results should be something like that:

01/01/2015 Carrefour 105eur to be paid 10/12/2015
03/05/2015 Esselunga 214eur to be paid 13/11/2015
05/08/2014 Mediaworl 300 eur to be paid 22/10/2015

Problem is that i cannot use any macro because i need to upload this on google sheets and VBA wont work.

Best Regards,
Roberto

[bebo021999]

Not seeing your file, but I suggest putting 12 months into one sheet
Anyway, to give a correct advice, we need a sample.
Could you pls post a dummy worksheet?

[Rob8222]

I cannot put everything in one sheet, i simplified the situation to make it clear.
Enclosed an example of what i meant.

[bebo021999]

To picking data from 12 sheets is a real paint. but VBA can do it, i think. But I cant help with VBA.

[Rob8222]

Vba is not an option because i need to upload it ok google sheets where vba doesnt work.

[Glenn Kennedy]

It's still a bit complicated!!

This array formula in the result sheet delivers what you need.

Please Login or Register  to view this content.

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

[bebo021999]

It's still a bit complicated!!

Sure it is! I have though of your solution before, but real data should be 12 months this year also few month next year!!!
i think try to combine all 12 months to one is best solution.

[Glenn Kennedy]

Yes, I TOTALLY agree. You could extend this to cover a year.... but it'd be horrible. TBH, I didn't notice the 12 sheets requirement whenI started to look at this.

[Rob8222]

Problem is that i have 3 sheets per month and each sheet are connected so it is not an option to have the whole year on a single sheet (( ps it is just one per year file

[Rob8222]

Hello Glenn,
It seems that your formula do the job, i tried to adapt it myself and i did a big mess.
I enclosed again the file using real name i have in my file putting in the real column the data, any chance you will help me?
I m of course ready to do what you tell me, if it is boring for you to complete the formula for 12 months.

Thank you very much, i see the end of the tunnel.
Best,
Roberto

[Glenn Kennedy]

well.... you did ask (by PM) me to look at this again. I still think that you'd be MUCH better off assembling the data into one sheet (even if that sheet is hidden). However, it's done. if you extend the formula bar you will be able to see the "modular" approach that this formula uses.

I have set the formula ONLY to look down as far as row 20. If it needs to be longer, you'll need to adjust the ranges. if it needs to be HUGELY longer, the formula will crash your PC. Guaranteed.....

Have fun!! Any problems, please let me know. Finally, it'd be nice if you were to click the "Add Reputation" button at the foot of ALL of those who have helped produce a solution, whether it was adopted by you, or otherwise.

[newdoverman]

I looked at your latest upload but don't understand the tab names. However, I suggest this way of doing things.
Enter all data into a Data Table on one worksheet with the proper dates then create a Pivot Table to do the calculations.

I changed the dates for each month so that the months would be obvious instead of having all the months with exactly the same data.