Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Solution to capture FIFO system for formula based cell

0

Hi,

Can we able to capture formula based cell through FIFO system or filter or sort.

Is there any kind of formula or any kind of solution, etc. to capture the same.

How to find in the sequence order or how can be notified.

Please find attached sheet for your reference.

Regards,

Nitil

Revision-1

Please refer to attached sheet for your reference.

Please advise for solution.

Regards,

Nitil

Answer
Discuss

Discussion

Nitil

Please clarify your question- I've looked at your spreadsheet but don't understand what you really need. Can you give an example scenario perhaps?

FIFO normally stands for "First In, First Out" (used in the early days of tiny computer registers/ memory devices as I recall fondly!) but do you just want to sort Names by when the Action was done? If so, what differentiates when successive "done" values are made?
John_Ru (rep: 6092) Mar 10, '21 at 4:54 am
Nitil

Thanks for the Revision (and new file). I think I know what you want but in the Time column, do you just want the hh:mm portion? Don't you want the date and time (if added by the macro for example) and have it sorted on that basis?

Also, in "formula based sheet", is each row unique (so only one row for Name "A" for example?
John_Ru (rep: 6092) Mar 11, '21 at 3:20 am
Hi,
I am looking the report on daily basis. Hence, Date is not that important but If I get date also, than good.
Secondly, Yes, each row is unique.
Can you help for the solution.
Thanks,
Nitil
NitilA (rep: 14) Mar 11, '21 at 4:27 am
Thanks.

I'll try to help but it will be later in my day (another 8 hours or more) 
John_Ru (rep: 6092) Mar 11, '21 at 5:39 am
Thanks,
Will look forward for the solution.
Would really appreciate your response.
Regards,
Nitil
NitilA (rep: 14) Mar 14, '21 at 12:44 am
Add to Discussion

Answers

0

Nitil

Please try the attached file (I've lost use of all Office 2016 products for now - due to an update by Microsoft- so using an earlier version where some functions aren't working).

I've made your base data into a table in the sheet Input (so to add a new row, just start typing in the row under the last row). I've set data validation for Strategy 1 to 3 columns to True (or blank) and added the Worksheet_Change event macro below.

If a cell is changed to True within the named table ("Input Table"), it sets cell in the Update Time column to the current time and in Comments, it says which Strategy was adopted at that time (so you could have True in all three columns and know which was the last adopted). It also updates the pivot table values.

Revision 1: The revised code below also gives a "minute-by-minute" report in the sheet "Activity Report". Everytime a Strategy is changed to True in the Input sheet, a new line is added to the report (copying the format from the last one) to show all changes in strategy as time progresses. This is different to your pivot table, which is a snapshot of the current strategies in use at present (but arguably just shows a partial view of the input table).

I note that this report will be daily but leave to rest the Input table (and Activity Report) or write macros to that . Take care to leave at least one data row in the Input Table.

REVISION 2: Code in bold below changed in revised file (since user changed definition of Activity Report)

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("InputTable")) Is Nothing Then Exit Sub

Application.ScreenUpdating = False

Dim LastUsed As Integer

If Target.Value = True Then
   Cells(Target.Row, 8) = Now
   Cells(Target.Row, 9) = "To " & Cells(4, Target.Column)
   Sheet2.PivotTables("PivotTable1").RefreshTable

    With Sheet1 'update report
        LastUsed = .Cells(Rows.Count, 2).End(xlUp).Row ' find last row and copy format to
        .Rows(LastUsed).EntireRow.Copy
        .Rows(LastUsed + 1).PasteSpecial Paste:=xlPasteFormats

    Sheet4.Range("B" & Target.Row & ",E" & Target.Row & ":I" & Target.Row).Copy 'copy cells from Input sheet
        .Cells(LastUsed + 1, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                        :=False, Transpose:=False
        Application.CutCopyMode = False
    End With


End If

Application.ScreenUpdating = True

End Sub
Note that your pivot table will stay in the order of the left hand column (Name) so if you want the table sorted by Update Time, move that field to the top of the list of Row Labels (so it will be on the left).
Discuss

Discussion

Hi,
Is it possible to get the result as per sheet "Require Macro result" mention in excel.
Regards,
Nitil
NitilA (rep: 14) Mar 16, '21 at 6:22 am
Nitil

It is possible (with more effort and I'm struggling to help at present) but why do you need both outputs? What's your comment on the Pivot solution?
John_Ru (rep: 6092) Mar 16, '21 at 6:40 am
Hi,
Thanks for your quick response.
Pivot solution is which i am looking for real time result. Thanks John.
I need both the output because for below following reason:
1. Pivot chart will give me real time updates, but the result will change whenever the cell changes i.e. from "True" to "blank".
2. Macro result will help me to record the result in report format which is require for analysis purpose.
Would appreciate your support for the 2nd solution i.e Macro result report.
Further, You can go with the latest version of MSoffice and let me know the version of the same. So, I can upgrade the same. 
Thanks,
Nitil
NitilA (rep: 14) Mar 17, '21 at 1:16 am
Nitil

Please see Revision 1 to my Answer and the modified file. I've now bought a subscription to Microsoft 365 (but you shouldn't need to do the same to use that file/ solution)
John_Ru (rep: 6092) Mar 17, '21 at 7:05 am
Did that work for you, Nitil? 
John_Ru (rep: 6092) Mar 18, '21 at 2:18 am
Yes John it will work for me. Thank you so much for your efforts.
Just small changes required.
Please guide on amendment on coding, as I want to limit the output report format to Name, Strategy1, Strategy2, Strategy3, Update time and Comments. Excluding Age and Occupation.
Secondly, Please also guide on how to give sound alert code Beepnow() whenever criteria matches i.e. "True" appear.
Thank you very much John once again.
NitilA (rep: 14) Mar 18, '21 at 4:20 am
Nitil. 

Good but your sample spreadsheet haad a macro output example which INCLUDED Age and Occupation.

Now you want to exclude those fields, the line Sheet4.Rows(Target.Row).EntireRow.Copy cannot be used. I'll correct that and post a new file (under Revision 2).

Next time, please be sure to show the result you need (so whoever answers your question does not have to re-work the solution).
John_Ru (rep: 6092) Mar 18, '21 at 5:01 am
See Revision 2
John_Ru (rep: 6092) Mar 18, '21 at 5:04 am
Thanks John,
Sorry for inconvenience caused to you.
In future, I will keep in mind, so it not need to rework the solution.
Last request, Since, i wanted sound alert for the same.
Hence, Can I reques you to please help me with the coding on sound alert for the same file.
Regards,
Nitil
NitilA (rep: 14) Mar 18, '21 at 6:15 am
Nitil

Please don't add more to your question (I think I answered your question fully and modified it once so kindly mark as selected). 

You can probably find code for this by doing a Google search like "excel vba play sound file" but will need files stored on your computer or embedded in your file. You could ask a separate question in the Forum if you like
John_Ru (rep: 6092) Mar 18, '21 at 6:30 am
Hi,
I found from the solution that we have to select the "True" tab manually from the drop down. But the "True" is in formula based and derived from another sheet /cell.
Hence, Please suggest how to capture the results from formula based sheet.
Regards,
Nitil 
NitilA (rep: 14) Mar 18, '21 at 7:39 am
Nitil

I did that (selecting True value from a data validation dropdown)  for easier entry. It also triggers the macro. Furthermore the file in your question suggested you needed three sheets (base data, pivot from that and timestamp report). 

If that's not what you wanted, you should have said before I revised it twice. 

You could just remove the data validation in Input and it should work (provided you type True correctly!). 

Please try to understand the code and modify. I have no more time to spare for this.
John_Ru (rep: 6092) Mar 18, '21 at 10:12 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login