Hi All, hoping for some help, many thanks in advance.
So I have a 13 sheet petty cash sheet. 1 Sheet per month, and 1 summary page.
On the summary page I have a Query function that gives me a weekly list of purchases made (read from manual entries on the monthly pages), this is still shown by purchase. Ie, today I bought 2 loaves of bread, yesterday was 3 loaves of bread and a lemon.
Also on this page I also have a product spotlight summary which reads the week and countifs it, so rather than entry by entry I can quickly see this last week I bought 7 loaves of bread total. This is down with a =unique and sumif functions.
However, I've just seen that last week, we bought 7 loaves of bread, but in the weekly product spotlight it says i bought "Bread x 7 £7.00" AND "bread x 7 £7.00" - so the unique function is case sensitive but the sumifs are not, so the 7 loaves are counted twice as some entries are made with a capital and some aren't.
I've tried adding a proper function, =unique(proper(range)) gave me a blank and =proper(unique(range)) gave me one correct answer but didn't autofill downwards with everything else I bought, just whatever the first item was.
Any help would be great, thanks!
G
So I have a 13 sheet petty cash sheet. 1 Sheet per month, and 1 summary page.
On the summary page I have a Query function that gives me a weekly list of purchases made (read from manual entries on the monthly pages), this is still shown by purchase. Ie, today I bought 2 loaves of bread, yesterday was 3 loaves of bread and a lemon.
Also on this page I also have a product spotlight summary which reads the week and countifs it, so rather than entry by entry I can quickly see this last week I bought 7 loaves of bread total. This is down with a =unique and sumif functions.
However, I've just seen that last week, we bought 7 loaves of bread, but in the weekly product spotlight it says i bought "Bread x 7 £7.00" AND "bread x 7 £7.00" - so the unique function is case sensitive but the sumifs are not, so the 7 loaves are counted twice as some entries are made with a capital and some aren't.
I've tried adding a proper function, =unique(proper(range)) gave me a blank and =proper(unique(range)) gave me one correct answer but didn't autofill downwards with everything else I bought, just whatever the first item was.
Any help would be great, thanks!
G