Forum Discussion
Excel formel
I am going to, have the same date pasted in about 50 places for labels.
Request: I did Create a cell, AL2 - wrote, 27 Oct
Then I want to create a formula in the 20 different places, that picks up 27 Oct from AL2, from the same Excel sheet.
I have tried =!AL2, =$AL2... etc. but nothing works đ
3 Replies
HelloPiuffâ,
To pull the same date from cell AL2 into multiple places on the same worksheet, use a normal cell reference.
Use:
=AL2
or, to keep the reference fixed when copying to many cells (best for labels):
=$AL$2Do not use ! unless you are referencing a different worksheet (for example: Sheet1!AL2). Using =!AL2 is not valid Excel syntax.
If the value in AL2 is meant to be a date, ensure it is entered as a real date and then format the cells as a Date (for example, Custom format d mmm). Any change in AL2 will automatically update all linked cells.
Microsoft documentation:
Use cell references in a formula
https://support.microsoft.com/en-us/office/use-cell-references-in-a-formula-fe137a0d-1c39-4d6e-a9e0-e5ca61fcba03Switch between relative and absolute references
https://support.microsoft.com/en-us/office/switch-between-relative-absolute-and-mixed-references-981f5871-7864-42cc-b3f0-41ffa10cc6fcYou may need TEXT() function to display a proper format as your input value is date. Try-
=TEXT($AL$2,"dd mmm")so any of the following should work:
=AL2
=$AL$2
=$AL2
=AL$2
when you say it didn't work, did you by chance get some weird number? because a DATE is actually a number representing the number of days after a start/zero date. If that is/was the case, you just need to change the format of those cells to be Date or under more options you can choose a more specific Date format.
Another alternative is to NAME that AL2 cell. click on the AL2 cell then on the AL2 label shown above the list of row numbers and to the left of the formula bar. After highlighting that AL2 there, type a 'name' for that cell (e.g. 'MyDate'. just make sure it isn't a cell reference like D1 or DD1234 and doesn't use illegal characters or spaces). Alternatively you can click Formulas -> Name Manager -> New and then enter a name in the name box and in the Refers to box you can enter =$AL$2 (or if the cell was already highlighted it will pre-populate the reference). Then your formula will be:
=MyDate
or whatever you named that cell
