Index a datasheet to create an invoice using 1 criterion

[peterjames]

Hi

I am trying to create an Invoice that will pull data for specific months and outputs the corresponding values horizontally and vertically - i have tried using INDEX in an array formula but i am struggling and to be honest i do not even know if this can be done using formulas in MS Excel so thought I would ask for some assistance.

I have attached some test data and will outline the problem.

1. The worksheet is split into 2 tabs (DataSheet and Invoice)

2. If you click on the drop down in Cell N10 on the Invoice Tab and select a month it will populate all of the rows from C16 to C40 and across to Column M with all entries that have a value for that month by pulling from the DataSheet and those cells which don't have a month in the adjacent column to be ignored.

3. The DataSheet has been created so that each value starting from Column E has a Month Invoiced box on the right hand side and I have used colours to identify where the data in pulling from on both the DataSheet and Invoice worksheets.

Any assistance would be greatly appreciated.

Many Thanks

Peter

[XLent]

I would be inclined to do the following (assumes XL2010):

Please Login or Register  to view this content.

for the remaining fields I would just use INDEX/MATCH using the value in Col A,
note: if, per your sample, your data is sorted by Site # you can (for remaining fields) use MATCH(A16,DataSheet!$A$4:$A$14) rather than an exact match (,0) - as will be faster

[peterjames]

Thanks so much for the response - i will try this and will get back to you.

Many Thanks

Peter

[XLent]

Peter, in retrospect, it needs a tweak - apologies...

Revised:

Please Login or Register  to view this content.

and, for completeness, the below would populate the remainder of the matrix:

Please Login or Register  to view this content.

[peterjames]

Hi Xlent

Just tried the formulas and it seems be be doing a count and not pulling the values through, i may be something wrong, just uploaded another attachment called Test_Data-2, if you could have a look it would be greatly appreciated.

Many Thanks

Peter

[peterjames]

No apologies necessary - just tried it and it works, thanks so much Xlent, you have really helped me out Peter

[peterjames]

Sorry to bother you again - just tried it and when i change the month in N10 its putting all the values in for the site number rather than the specific value . ie the uploaded attachment (test data 2) has Birmingham at £150 for October but its putting all the values for the whole site on the Matrix B16:M40.

Do i need to tweak it a bit more so it only pulls those months?

Many Thanks

Peter

[XLent]

Oh dear, another error on my part I'm afraid.

Please Login or Register  to view this content.

edit: or if you prefer a SUMIFS type approach

Please Login or Register  to view this content.

SUMPRODUCT (below) would be a little more succinct (syntax wise) but a little less efficient.

Please Login or Register  to view this content.

[peterjames]

Just tried it - it works perfect, thanks so much for your help Peter

[XLent]

No problem - apologies for the errors.

[peterjames]

No apologies required - you really helped me out

[peterjames]

Apologies for bothering you - just spotted something when i was testing the formulas - i have uploaded test data 3 - and if you look at the Invoice tab i have selected Oct-20 - however Site Number 2 is not showing even though there is an invoiced date in Cell L6. What I also noticed is if i put an entry in Cell I6 and J6 it will then put the info on the invoice for that entry and the missing one as well, are you able to assist me with this please

[peterjames]

Hi Xlent - I have fixed this - thanks so much for your help Peter

[peterjames]

Xlent - Just added the latest attachment - the issue i thought i fixed is still showing - any help would be appreciated. Many Thanks Peter

[XLent]

yet another error I'm afraid... when I added in the N test I should have modified k from ROWS to a constant of 1

Please Login or Register  to view this content.

[peterjames]

Thanks XLent - really appreciate you help with this - will add that line into the sheet

[peterjames]

Everything is now working fine - thanks again for your assistance