Payment priority invoice list

MrTall

New Member
Joined
Jul 17, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Hi there,

We are trying to improve the way we are prioritising payment of invoices and giving department managers the ability to "sort" which invoices should get paid first based on needs to the organisation. With COVID things are tight at the moment with cash, therefore we want to ensure there is no impact on significant services if we are not able to pay all invoices on time. Since there is no built in function to do this in our ERP we have resorted to Excel to assist.

Basically the financial controller at the start of the week will advise how much can be paid this week as a total figure. I have tried then using a "Running total" formula M3=SUM($H$2:H3)), which is then dragged down and leads to N3 =IF(M3>'Stripped Example'!$M$1,"Pay limit exceeded. Invoice will not be paid this payrun","Invoice will be paid in this payrun"), also dragged down, to make it clear to the user what invoices will be paid based on the sorting.

The issue comes up on how I can make it as easy to sort as possible for the user and for the formulas to remain consistent. I have tried using "Cut/Paste" rows which leads to the SUM formula in column M to be out of order. Do you have any suggested alternatives? I have tried searching online for a template to do this also with no luck, but by all means if you have a cleaner version you recommend we should use please suggest away :)

Cheers,
MrT
 

Attachments

  • Invoice Payment Priority by Sites example.png
    Invoice Payment Priority by Sites example.png
    128.1 KB · Views: 8

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
884
Office Version
  1. 365
Platform
  1. Windows
So you can add a helper column with priority of payments so for eg
consider

New Microsoft Excel Worksheet.xlsx
CDEFGHI
2Budget10000
3
4Company NameAmountHelperRunning Total
5v100011000
6s200013000
7s3000FALSE
8s200015000
9s100016000
10g1999FALSE
11g2000FALSE
12f100017000
13f2999FALSE
14ea117001
15d400FALSE
16d4000FALSE
17c10017101
18a200019101
19a1999FALSE
20
21
22
23
24
Sheet1
Cell Formulas
RangeFormula
G5:G19G5=IF(F5=1,IF(SUMIF($F$5:F5,1,$E$5:E5)>$F$2,"Payment will not be made",SUMIF($F$5:F5,1,$E$5:E5)))


I> have initiated Helper Column. You have to enter 1 in case you want to make the payment and in running total you will find 3 values 1/- False which means it is not scheduled for payment
2. Value i.e it is scheduled for payment
3. Payment will not be made" for showing budget value increased.

Even if you short the data it will not affest the payment status
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,384
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Firstly remove the sheet name from your formula in col M.
=IF(M3>$M$1,"Pay limit exceeded. Invoice will not be paid this payrun","Invoice will be paid in this payrun")
You should never us the name of the sheet that contains the formula.

You can then sort the data without damaging the formula.
The users could also add a priority number in col L & sort on that.
 

MrTall

New Member
Joined
Jul 17, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
Hey @CA_Punit & @Fluff,

Thanks for your responses. I have removed the sheet name from col M (thanks Fluff).

I appreciate the suggestion to use a helper column, though the reason I was going to "move rows around" was in case the amount of readily available funds was reduced further after the sheet was already submitted. That was if the assumption was the list was already sorted in priority order than it would simply be invoices at the bottom of the list that would not be paid be default rather than having to go back to the submitter.

With that additional detail, do you have any further suggestions?

Cheers
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,384
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I'm afraid I don't understand what you are asking.
If the data is sorted on the priority column, then surely the most urgent invoices would be paid first.
 

MrTall

New Member
Joined
Jul 17, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
I'm afraid I don't understand what you are asking.
If the data is sorted on the priority column, then surely the most urgent invoices would be paid first.
Sorry - I think I confused yours and @CA_Punit's responses. In the first response I can see it is either 1 or blank in the helper column F, that is more what I was replying to. I will give your solution a go
 

MrTall

New Member
Joined
Jul 17, 2020
Messages
5
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

In summary - is there no way I can move/cut and paste the rows that wont creating issues with column M's coding? For example, currently cell M9 =SUM($H$2:H9). If I was to cut and paste it to the bottom of the list (row 27) it now shows as =SUM($H$2:H27) which is perfect. If I undo this and move it to row 4 (2nd from the top) it shows =SUM($H$2:H9) in error. It's like Excel is allowing me to move it lower in the list but not move it up the list.
 

Attachments

  • Moving up example.png
    Moving up example.png
    49.4 KB · Views: 3
  • Moving down example.png
    Moving down example.png
    103.3 KB · Views: 3

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,384
Office Version
  1. 365
Platform
  1. Windows
If you are going to cut/paste rows with formulae, the best thing is to reset all the formulae afterwards.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,384
Office Version
  1. 365
Platform
  1. Windows
You're welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,494
Messages
5,602,004
Members
414,490
Latest member
Rip181

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top