I have done hours of research and cannot figure out how to do this. Please help.
I have an Excel for Web worksheet with many columns of data. Each column is an individual entry in the data series with each row corresponding to different data points of the entry. Each entry has about 15 rows of data but that is not relevant.
I have multiple users of the worksheet at any given time. I want a user to be able to enter their name in for example cell H2, and when they do, use a macro or API to automatically populate the date, accurate to the minute, in H1. This would apply for I2 to I1, J2 to J1, K2 to K1, and so on down the columns. Currently we have data up to column CBQ and it grows every day. The worksheet is reset every quarter, so the data ends up going to approx. column NZZ. I then want to use the date values for each column and make mathematical comparisons to count a certain data point (row) in each column. This count would output in a separate cell with the number of columns including the data point.
My first thought was to use the now() function. I needed to make it static for each column, which I found is easily done with a macro such as:
Function NowStatic()
NowStatic = Now
End Function
Then I could use this formula in each column of row 1: =IF(ISBLANK(H2)," ",nowstatic()).
It works perfectly in Excel desktop app. I could then easily perform any comparisons or formulas using the decimal serial number Excel uses to store date and time. Problem is, macros don't function in Excel for Web Then I checked out Office Scripts and Power Automate, but that cannot automatically run the script when a cell in a certain range (row) is edited.
I started researching how to use the Script Lab add-in, as this is available in Excel for Web. It seems like I would be able to do this using the onChanged event handler function. I want to use that function to detect when someone enters their name in example H2, then output a JavaScript date to H1 and I can figure out how to convert to Excel time serial number format from there for math and comparisons.
I am not really familiar with how JavaScript and Excel interact, and after many attempts, I cannot get it to function the way I want. It outputs wildly erratic values or just errors and does nothing. Screenshot of an example of my data attached:
I expect to be able to make my Excel for Web worksheet function like the Excel desktop app where I can create a function similar to the macro I shared above
I have not been able to find a function as simple as the macro that works in Excel for Web.
