Excel Formula Clarification for Dynamic Worksheet

0
votes

I have a dynamic Spreadsheet with three columns. Every time a record is added onto the website, it updates the worksheet. I need to calculate the AGE of a ticket from the Created Date.

I have used cell formula =Today() - G2 in AGE column, Where G2 is the cell with the created date.

The column header names are:

Ticket Status|  Created Date | AGE

However I want this formula to keep adding down the rows dynamically whenever there is a new record in the list. How can I do this?

1
excelexcel-formula
Either copy the formula down beyond your current used area, or select your data -> Insert -> Table in order to convert it to a table which will auto-fill the formula as new rows are added – tigeravatar

1 Answers

0
votes

Without getting in-depth about how the website is dynamically adding data to you're sheet:

Try:

Modify the formula to =IF(ISBLANK(G2),"",TODAY()-G2) (which just keeps the cell blank if the date is empty) and then select the column and press CTRL+D to fill the formula down the entire column

Other options:

As @tigeravatar mentioned, maybe a cleaner solution would be to convert the sheet into a table. But there are some caviates that may or may not affect you, like the sheet looking significantly different.

The cleanest solution would be to add it the the code that adds rows triggered from the website, but that may be significantly more complex.