thbutterflycollector_
New Member
- Joined
- Oct 29, 2015
- Messages
- 42
- Office Version
- 365
- Platform
- Windows
Hello,
I have some data in Table 1 which is a rolling transactional list from accounts, it gives me every payment from every supplier as a cumulative and I would like a formula to return the most recent payment. I’ve deduced I need a max formula to look this up from the table. However, I require this to link back to each supplier code in another table. So far I have:
=MAX(IF(Table1[Supplier Ref]='Supplier Liability'!C7,Table1[Total Value]))
Table 1 being on Tab 1
“Supplier Liability” being a separate tab.
Cell Values in Column C in supplier liability tab is a unique code for the supplier i.e. C9999/5050/BC (Contract Number / Supplier Number / User).
Supplier Ref in Table 1 may contain some or all of this string e.g. C9999/5050/BC, C9999/5050, or say 5050/BC but will in some shape or form contain a four digit number usually beginning with 5.
At the moment the formula is only returning the max value contained within all of Table 1 rather than yielding the max values for each code in Column C.
I’m not sure where I am going wrong? Ideally, I’d also like the formula to capture the number with the 5XXX prefix to account for inputting errors.
Thank you in advance!
I have some data in Table 1 which is a rolling transactional list from accounts, it gives me every payment from every supplier as a cumulative and I would like a formula to return the most recent payment. I’ve deduced I need a max formula to look this up from the table. However, I require this to link back to each supplier code in another table. So far I have:
=MAX(IF(Table1[Supplier Ref]='Supplier Liability'!C7,Table1[Total Value]))
Table 1 being on Tab 1
“Supplier Liability” being a separate tab.
Cell Values in Column C in supplier liability tab is a unique code for the supplier i.e. C9999/5050/BC (Contract Number / Supplier Number / User).
Supplier Ref in Table 1 may contain some or all of this string e.g. C9999/5050/BC, C9999/5050, or say 5050/BC but will in some shape or form contain a four digit number usually beginning with 5.
At the moment the formula is only returning the max value contained within all of Table 1 rather than yielding the max values for each code in Column C.
I’m not sure where I am going wrong? Ideally, I’d also like the formula to capture the number with the 5XXX prefix to account for inputting errors.
Thank you in advance!
