@hollies

I am glad if you can use it.

Be aware that the formulas in column E and F are so-called array formulas. Array formulas are special in that they require to be entered a certain way. Where you normally just press Enter when you finish a formula, an array formula must be entered by holding down Ctrl and Shift before pressing Enter. If you do it correctly Excel will automatically put braces {} around the formula. Do not try to enter these braces manually. Excel will then think it is a text string and not a formula. When you have entered one array formula correctly you can copy it to other cells as you normally do.

I do not understand what may have happened to column B in Sheet1 (Day). I surely use Scandinavian settings but I wouldn't have expected that should influence one with US settings.



I attach a file PivotTest_3 where I have converted Sheet1 back to a Table and adjusted the formulas accordingly.

Please write back should you get problems in adapting the formulas to your actual file.

Attached Files

PivotTest_3.xlsx (23.3 KB, 10 views)

Many thanks XOR

I have successfully transferred the formulas to the proper table and it has worked a treat, almost!

The information now shows the last date of every visit we have made in the last 20 years.

How do I now filter that information so that I can select just one Country, or the UK (4) or Australia for example.

I also want to sift out only those entries in Note2 that match certain criteria, ie Yes, Poss, Good etc. I don't want the Overseas and No entries to show in this particular list, but I might next month.

I found this worked well in the pivot Table.

Is it a case of using a pivot table for some columns and then bringing in the formulas in other columns to get them right.

Thanks

Rob

Maybe you can use the attached.

Attached Files

PivotTest_4.xlsx (28.8 KB, 11 views)

xor

Brilliant, you have been exceptionally helpful and I appreciate it.

I am learning so much about Excel and it's hidden capabilities (to most people anyway).

Having used it for many years, I have never got passed the basic table stage and although I knew there was much more to it, because I have not really needed it until now, I have not bothered to learn the more advanced features.

Thank you once again.

Rob