megera716
Board Regular
 Joined
 Jan 3, 2013
 Messages
 110
 Platform

 Windows
I think that what my boss is after is impossible, but if anybody can solve it, it's this group!
I have a table of customers, the sales location (there are 4) and the amount sold to the customer (onetime sale). Some customers may pay in full all at once, others in two payments, others in 3. So I also have columns for Payment 1 (Amount)0, Payment 1 Date, Payment 2, Payment 2 Date, Payment 3, Payment 3 Date. If we ever had a customer who paid in more than 3 chunks, I'd have to add Payment 4 and Payment 4 Date and on and on.
I also have a column of total payments received to sum those 3 columns. But Boss wants to see total cash collected on a given day and so I must track each payment's date.
But then when I try to Pivot this, it doesn't work to have payment dates 1, 2 and 3 stacked on top of each other, so I have 3 pivots  one for each payment date, with location in rows, dates in columns and values are the payment amounts. Boss wants to know the total collected [yesterday] in a single cell that updates daily, so I have HLOOKUP(today()1,array,row,reference)+HLOOKUP(today()1,2nd array,row,reference)+HLOOKUP(today()1,3rd array,row,reference)
Tell me there's a better way!
Visual representation of raw data. I think I can figure out a way to get the total collected as of today,. Can I get total collected by day when it needs to check and grab from 3 different columns?
I have a table of customers, the sales location (there are 4) and the amount sold to the customer (onetime sale). Some customers may pay in full all at once, others in two payments, others in 3. So I also have columns for Payment 1 (Amount)0, Payment 1 Date, Payment 2, Payment 2 Date, Payment 3, Payment 3 Date. If we ever had a customer who paid in more than 3 chunks, I'd have to add Payment 4 and Payment 4 Date and on and on.
I also have a column of total payments received to sum those 3 columns. But Boss wants to see total cash collected on a given day and so I must track each payment's date.
But then when I try to Pivot this, it doesn't work to have payment dates 1, 2 and 3 stacked on top of each other, so I have 3 pivots  one for each payment date, with location in rows, dates in columns and values are the payment amounts. Boss wants to know the total collected [yesterday] in a single cell that updates daily, so I have HLOOKUP(today()1,array,row,reference)+HLOOKUP(today()1,2nd array,row,reference)+HLOOKUP(today()1,3rd array,row,reference)
Tell me there's a better way!
Visual representation of raw data. I think I can figure out a way to get the total collected as of today,. Can I get total collected by day when it needs to check and grab from 3 different columns?
Location  Customer  Sale  Pmt 1  Pmt 1 Date  Pmt 2  Pmt 2 Date  Pmt 3  Pmt 3 date 
Nowhere  Bob  40,000  40,000  4/15/2021  
Anywhere  John  40,000  5,000  4/17/2021  25,000  4/18/21  10,000  4/19/2021 
Somewhere  Susan  40,000  10,000  4/18/2021  10,000  4/19/21  20,000  4/20/21 
Everywhere  Jane  40,000  20,000  4/19/2021  20,000  4/20/21  