Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 05-22-2024, 06:40 AM
SnakeDoctor's Avatar
SnakeDoctor SnakeDoctor is offline When a value is selected for B2, auto-populate C2 with date that selection was made Windows 11 When a value is selected for B2, auto-populate C2 with date that selection was made Office 2021
Advanced Beginner
When a value is selected for B2, auto-populate C2 with date that selection was made
 
Join Date: Jul 2023
Location: Ft. Worth, TX
Posts: 38
SnakeDoctor is on a distinguished road
Default When a value is selected for B2, auto-populate C2 with date that selection was made

I am trying to do something I thought was simple, I need to have the date populate a cell when a selection from a dropdown is made in another cell.

Details:
Column B cells are set as a dropdown list of employees.
Column C cells represent the date the Column B selection was made.
Date cannot change every time the spreadsheet is opened.

Attempts and Failures:
(with Column C cell Format set to Number/Custom: mm/dd/yyyy)
=IF(B2<>"",TODAY(),"") - WORKS but date changes every time sheet is opened

=IF(B2<>"",DATE(),"") - produces "Not trying to type a formula?" error

=IF(B2<>"",DATE(mm/dd/yyyy),"") - produces "Entered too few arguments" error

=IF(B2<>"",DATE(TODAY(),"") - produces 'Entered too few arguments" error

among others too numerous to count, only one works - and it changes every time I


open the sheet.

This is driving me mad, it should not be this hard. It is just a simple 'IF/THEN' scenario! And every search I've done to find the various formula 'date values' produces nothing usable.

PLEASE HELP!
Reply With Quote
  #2  
Old 05-26-2024, 01:16 PM
Alansidman's Avatar
Alansidman Alansidman is offline When a value is selected for B2, auto-populate C2 with date that selection was made Windows 11 When a value is selected for B2, auto-populate C2 with date that selection was made Office 2021
עַם יִשְׂרָאֵל חַי
 
Join Date: Apr 2019
Location: Steamboat Springs
Posts: 112
Alansidman has a spectacular aura aboutAlansidman has a spectacular aura aboutAlansidman has a spectacular aura about
Default

Deleted. My solution did not work.
__________________
Alan עַם יִשְׂרָאֵל חַ Using O365 v2505
Reply With Quote
  #3  
Old 05-26-2024, 10:41 PM
ArviLaanemets ArviLaanemets is offline When a value is selected for B2, auto-populate C2 with date that selection was made Windows 8 When a value is selected for B2, auto-populate C2 with date that selection was made Office 2016
Expert
 
Join Date: May 2017
Posts: 932
ArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant futureArviLaanemets has a brilliant future
Default

As start, are you sure you want to do this really. Using shortcut for current date (for me it is Ctrl+Shift+Semicolon, but it depends on your local settings) in cell in column C is so easy, there is no real reason to take much insecure approach.

Anyway, when you are sure about this, then formula in not a way for this. There is no way to prevent it to be recalculated at some appropriate moment. You even don't need to change anything - it will be enough the user double-click on cell (the editing of cell is activated), and then move to another one, and the formula is recalculated (and the date is replaced with probably new current date).

The only possible way I can think of, is to write a worksheet's Change event, which checks for values in columns B and C of active row, and when B<>"" and C="", then enters current date into cell of column C of currently active row. To make this even more foolproof, the script can check for active cell, and insert the date only when active cell is in column B or C. But the event fires whenever you edit any cell of worksheet, and at least checks for values of cells in columns B and C of active row - i.e. it spends some time for it and your workbook slows down as result.
Reply With Quote
  #4  
Old 06-03-2024, 07:09 PM
Guessed's Avatar
Guessed Guessed is offline When a value is selected for B2, auto-populate C2 with date that selection was made Windows 10 When a value is selected for B2, auto-populate C2 with date that selection was made Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 4,159
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

See this link Automatically Enter Date When Data Entered in Excel (2 Ways)

Note that you need to set your options to Enable Iterative Calculation before it works
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
Reply



Similar Threads
Thread Thread Starter Forum Replies Last Post
Trying to have selected values from one column populate date selection was made in 3 other columns SnakeDoctor Excel 1 05-01-2024 12:45 AM
Auto populate text from a drop-down form field selection in Word atr792 Word VBA 2 07-11-2023 05:44 PM
Auto-populate text boxes with Excel data based on drop down box selection charkelham Word VBA 0 08-04-2021 04:38 AM
Auto populate weekday based on given cell/date kelwea Excel 6 05-20-2019 03:26 AM
When a value is selected for B2, auto-populate C2 with date that selection was made Auto populate cell with today's date Phil H Excel Programming 7 10-18-2017 09:41 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:17 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2025, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2025 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft