I want to show the last entry of a column in Excel in real-time.
I have an excel file, in which a data (numbers) is added to a specific column. I would like to present the current number updated in the last row of the column in a text box every 1 sec. How can I do that? I saw it is written that a text box can be referred only to a specific cell.
Good question. You could import all data in DataPoint and then add automatic row numbers to your data. Then, sort descending on that new row number column and finally show the first value. But your number of values in Excel might grow rapidly and gradually your system would slow down. So that is workable, but not optimal. There is a more elegant way to do this and with great performance too.
Imagine that you have some data like this. An Excel file with one column with multiple values. Here we add new values to the end of the list.
Open your Excel and use a new column. Set a column name like LastValue here.
And in the cell B2 you set the following formula:Â
=LOOKUP(2,1/(A:A<>””),A:A)
You can immediately see that this formula is working. The value returned from this formula is 56. When you would add a new value to the numbers of column A, then that value is updated at cell B2.
Let’s open a PowerPoint presentation and use DataPoint to set up a link to this Excel file to show this information in real-time on a television or computer screen.Â
The range is set to B1:B2 and we check the option The first row contains field names.
Notice that the refresh rate is lowered to every 1 second.
We have a real-time connection to the data file. We can use now a text box and link to this value, or use a needle on a dashboard and show this value on a scale for a dashboard. The value on the running slideshow will be updated every second and the last entered value will be shown on your slide. Problem solved.Â
