For example, I want to change the lookup value (rows) and the columns in which the formula is being applied. I tried to make it dynamic
1) what is the rule behind the "dynamic" way you want to achieve
2) do you already want to have the result or do you want to apply the formula into the cell
3) I assume you did not test the code because it is failing at "Workbooks("Input File").Close"
4) what kind of error do you expect inside the for-loop (you used "on error resume next")
your approach is too easy 
The problem is that you have
1) ignore already ordered items
2) sum all new items and compare the sum to the stock
so for this you now need the extra column. I named it "status" and now your macro (admin accepted) has to sign the order as done (e.g. "ordered").
and recalculate/update the stock
--> attachment Test3 (1).xlsx
I ignore any item with "ignored" in the column "status", sum up the rest nd compare it with the Inventory.
I had a short look but I do not want to find out what all the code does and how the data is connected.
if you tell me what exactly should be copied into which dataset and what is the trigger to do so, then I would be able to provide a short code to add data to the table.
But the better way to learn is if you do it yourself. Have a look into https://www.thespreadsheetguru…o-listobject-excel-tables this is a really good description how you can handle tables via VBA
who will write the VBA code ???
the table is a listobject and has methods to access, change and add data
yes, the best way is that the user forms adds a new row and copy the data in.
So the table is increased and the inventory list updated
I converted both datasets into tables. The advantage is that you do not need to adopt the formula.
If you do not want to use tables then you should use dynamic named ranges so that the calculation will extend always automatically
welcome
I do not know why you are using this SelectionChange event. May be there is a better way to trigger recalculation
of course but this means extra effort (same as a button)
a normal checkbox is an extra shape ans is connect to a cell. If you use the cell without the checkbox --> less effort
If you use a ActiveX element which supports more events and not only code linked to it --> more effort
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lr As Integer, i As Integer
lr = Cells(Rows.Count, "e").End(xlUp).Row
For i = 1 To lr
If Cells(i, 4) <> "DONE" And IsDate(Cells(i, 5)) Then
If DateAdd("m", 6, Date) <= Cells(i, 5) Then
Cells(i, 5).Interior.Color = vbGreen
ElseIf DateAdd("m", -1, Date) <= Cells(i, 5) Then
Cells(i, 5).Interior.Color = vbYellow
Else
Cells(i, 5).Interior.Color = vbRed
End If
Else
With Cells(i, 5).Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If
Next i
End SubIf I provide something without knowing your files this will not work.
I Provide the way and you will find everything on the intrnet
then add a column and mark it as open or done. you donot need to generate an extra button. make the button visible if there is an open order and hide the button when the inventory list is updated
I could not under stand your request on coloring
is it
green: date is 6 month in the future compared to today
yellow: date is between 6 month in the future and one month in past compared to today
re: more than one month in the past?
If Cells(i, 5) < Month(Date) - 6 Then Cells(i, 5).Interior.Color = vbGreen
This will never happen because for today the formula evaluates to if 44012 < 4 -6 then .... 
If Cells(i, 5) = Date Then Cells(i, 5).Interior.Color = vbYellow
this will change the color if the date is today
If Month(CDate(Cells(i, 5))) > Month(Date) + 1 Then Cells(i, 5).Interior.Color = vbRed
will change color only between January and April each year
If Cells(i, 4) = "DONE" Then Cells(i, 4).Interior.Color = vbWhite
will never happen because your loop (i) starts with 2
cheers
For Inventory Excel is not the best but of course can be used (a database program would be better like Access).
if you want for each open request (can there be more than one?) a single button there is mor coding effort. What happens wit the Order in the Order Request sheet? Stays in or will be deleted?
if it stays in the sheet then a much simpler approach would be to add a column "order accepted" with e.g. True/False and a always visible command button which does the deducing and also checking the corresponding "order accepted" cell. Additionally you can protect the cells for unwanted changes afterwards.
why is it not possible to have the command button including code already available?
Via command button 1 you can change then only the visibility of command button 2.
future questions and related- no problem. May be I will not solve it but give you some hints to do it yourself.
books: this depends on your experience in programming in any language. But I do not think they are better or worse. The only differences are in the content but they will not be for free
There is a lot of free stuff on the internet with a a lot of samples for free. For example:
and many more. For specific problems use any search engine. You will also find a lot on Youtube.
i removed the reference for Scripting Runtime. Please use this version
You did not give me much information. Therefore I have some assumptions
1) your files look similar. They all have a column "Date", a column "debit" and a column "credit"
2) you are able to copy the code into the other files
I added code in ThisWorkbook "Workbook_SheetChange( ... )"
Everytime something is updated in any sheet the sheet is checked on the three specific columns mentioned above.
If the columns are found all values will be checked
I also added a class module. This is only used to hold some values.
Therefore if you have other files you have to copy the class (same name!) and also the "Workbook_SheetChange( .. )" to the new file.
cheers