cancel
Showing results for 
Search instead for 
Did you mean: 

How to detect change of data in a cell

former_member195433
Participant
0 Kudos

Hello experts ,

How to detect the change in a data  in a cell ,  lets say i have a cell  A1   in spreadsheet and i want to flag a cell B1 when there is a change in data in cell A1 .

Can some one throw me some ideas .. Do we have any excel function to detect the change of data in a cell ..

Thanks&Regards ,

Pramod

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Pramond

In the “Other” components look for the “History” control.

Point the control’s data field to A1.  Point the destination to A2:A3. 
The current value of A1 will be written to A3.  The previous value will be written to A2.

former_member195433
Participant
0 Kudos

Thanks mike

Answers (2)

Answers (2)

former_member192430
Active Participant
0 Kudos

Hey Pramod,

If you want to detect this particular change against a reference value then a simple "=if(A1<>B1,0,1)" statement will do.

Another option, which might very much depend upon the scenario you are facing, is where you might need to capture the current dataset as initial image of data in one place and refer that to detect the change in the new data set or the data set where the change needs to be detected.

Regards,

Abhishek

former_member195433
Participant
0 Kudos

Abhishek ,

yes it works fine when i capture initial data and compare it to a cell having data changed constantly . But  I need to detect the change in a cell by comparing with its previous data value in it . 

I tried to store the initial value but that has to be changed constantly and it didnt work for me ..

I dont even think there is a way to have memory ...like storing the previous value and comparing with present value and overwriting the previous value with present value

Anyway thank you abhishek and vinaykumar for throwing some ideas .

former_member192430
Active Participant
0 Kudos

Pramod, detecting a change in a cell might have many alternatives but then you are right the second one is more automated and legitimate when it comes to checking the change every time the data changes.

It is feasible, though a little complicated, but can be achieved using a refresh flag. You can leave it up to the system and the component to do the copying part and assessing the data change.

Let me know if you want to find out how to do this.

Regards,

Abhishek

former_member195433
Participant
0 Kudos

Abhishek ,

I would like to know the logic ..

former_member192430
Active Participant
0 Kudos

Pramod,

The logic is as follows:

Maintain two tabs one where the data gets updated using any kind of connection and the other tab where we maintain the copy/image of the updated data.

Every time you use the refresh button it will first copy the the updated data from update tab to the image tab and update a refresh flag which will control the update of the data in the update tab.

This way whenever you update the data into the update tab your system will be ready to compare the data with previous image.

How to accomplish this:

You will need a Push Button and the Refresh Query Button for refreshing the data.

Push Button is used to copy the the image.

Refresh Button is used to refresh the dataset.

Maintain 3 tabs as shown for update, image and refresh.

Just maintain the refresh flag value as '=IF(Update!C6=Image!C6,1,0)'.

The value in Update C6 is the timestamp value which will get updated whenever you refresh the values in the Update Sheet. This can be configured in the Excel sheet itself or you can get it from the source you are refreshing from.

In case you are using the Excel then use this formula to get it '=Timevalue(TEXT(NOW(),"HH:MM:SS")))', just keep in mind that we need to cover this cell in the push button source.

When you are ready with this you are good to go for checking on the values and compare them.

When you press refresh the data gets updated in the Update sheet and the time value differs from the image value and you can now apply your formula here to identify the difference in the fields you want to check. [I used dynamic visibility to prevent show how is this done].

I hid the refresh button when there is difference in the timestamps.

So, now you can do the comparison.

But after comparison you need to refresh your values again for that you need to press copy which equates the time stamp values and you are good to go for refresh as the refresh button pops up which I had hid using visibility controller.

I do hope I have conveyed the general idea of accomplishing this thing.

Its up to you how you intend to compare the values or further simplify this thing to detect the change of the value.

I'd like to invite the experts to experiment on this conundrum so as to reduce this particular detection method down to one button.

Happy Diwali and Happy Dashboarding to all,

Abhishek

former_member195433
Participant
0 Kudos

Thanks a lot abhishek ,

I will try to implement using your logic ..My requirement needs to minimize mouse clicks and i  need to check the status of the cell , when it changes i need to flag something else or do some other interactivity . without interacting any selectors .

But thats really the logic i am looking and thank you for putting a lot of effort  with  screen shots

-Pramod

vinay_mhaske
Contributor
0 Kudos

Hi Pramod,

You can write VBA code to delect value change in excel cell; however its not supported in Xcelsius.

Thank you.

Regards,

Vinay Mhaske