Forum Discussion
Enter date when cell updated
I understand the limitation you are facing with Excel for the web and SharePoint regarding circular references and dynamic updates without VBA. Unfortunately, Excel formulas alone cannot dynamically update the date without introducing a circular reference or using VBA. The circular reference issue is a limitation of Excel for web and SharePoint, preventing formulas from referring back to themselves. While VBA can provide a solution, it requires scripting expertise and is not user-friendly for shared spreadsheets. Given these constraints, let us explore an alternative approach that involves using a combination of data validation and helper columns:
1. Helper Column for Initial Entry Date:
- Create a new column (let's say column D) and use the following formula in cell D28:
=IF(B28<>"", IF(D28="", TODAY(), D28), "")
- This formula checks if cell B28 is not blank and if cell D28 is also blank. If both conditions are met, it sets the date to the current date using TODAY(). If either condition is not met, it leaves the existing date in place.
2. Data Validation to Lock Date:
- Select cell B28.
- Go to the "Data" tab.
- Click "Data Validation."
- Under the "Settings" tab:
- Allow: Choose "Date."
- Criteria: Choose "Greater than or equal to."
- Start Date: Choose a reasonable date range that covers your expected entry period.
- Under the "Error Alert" tab, you can set an error message to instruct users that the date is now locked.
- Apply data validation to cell B28 to prevent further changes once a date is entered:
This way, users can enter a date in cell B28 only once, and the corresponding date in column D will be set at that time. The data validation in cell B28 will prevent further changes.
Please test this approach in your specific setup to ensure it meets your requirements. Keep in mind that users can potentially bypass data validation rules by copying and pasting values, so it's essential to communicate the intended usage guidelines to the users of the spreadsheet.
Alternative, to achieve dynamic date updating without circular references or VBA, you need to consider external applications like 3rt party AddIns or services that can communicate with Excel and update the date accordingly.
Hi NikolinoDE, thanks for the reply. I'm confused by your first answer though. Haven't you just moved what I've done from column C to column D? And therefore cell D28 now contains the circular reference?
If you're meaning to reference a separate cell, won't that separate cell always be blank and therefore the cell with the formula will, if B28 is not blank, always evaluate "TODAY()" and therefore update every time I open the sheet, rather than only when B28 is first filled in?
Sorry if I'm missing something here, but this is the problem I've been trying to solve.
I understand the limitation you are facing with Excel for the web and SharePoint regarding circular references and dynamic updates without VBA. Unfortunately, Excel formulas alone cannot dynamically update the date without introducing a circular reference or using VBA. The circular reference issue is a limitation of Excel for web and SharePoint, preventing formulas from referring back to themselves. While VBA can provide a solution, it requires scripting expertise and is not user-friendly for shared spreadsheets. Given these constraints, let us explore an alternative approach that involves using a combination of data validation and helper columns:
1. Helper Column for Initial Entry Date:
- Create a new column (let's say column D) and use the following formula in cell D28:
=IF(B28<>"", IF(D28="", TODAY(), D28), "")
- This formula checks if cell B28 is not blank and if cell D28 is also blank. If both conditions are met, it sets the date to the current date using TODAY(). If either condition is not met, it leaves the existing date in place.
2. Data Validation to Lock Date:
- Select cell B28.
- Go to the "Data" tab.
- Click "Data Validation."
- Under the "Settings" tab:
- Allow: Choose "Date."
- Criteria: Choose "Greater than or equal to."
- Start Date: Choose a reasonable date range that covers your expected entry period.
- Under the "Error Alert" tab, you can set an error message to instruct users that the date is now locked.
- Apply data validation to cell B28 to prevent further changes once a date is entered:
This way, users can enter a date in cell B28 only once, and the corresponding date in column D will be set at that time. The data validation in cell B28 will prevent further changes.
Please test this approach in your specific setup to ensure it meets your requirements. Keep in mind that users can potentially bypass data validation rules by copying and pasting values, so it's essential to communicate the intended usage guidelines to the users of the spreadsheet.
Alternative, to achieve dynamic date updating without circular references or VBA, you need to consider external applications like 3rt party AddIns or services that can communicate with Excel and update the date accordingly.