Can a cell display the text in a random number of other cells based on the status of cells in the same rows as the text? [Calibration Tracking]
Our take
The sheet has a column of names (of equipment), with dates (when calibration expires) in the same rows.
I want one cell at the top of the sheet to show the names of all equipment with calibration dates in the past.
> I have Conditionally Formatted the date cells so that any dates in the past so can be found by scrolling down the date column, but I want the cell at the top of the sheet to show the equipment names associated with the past due dates.
I'm struggling with showing an indeterminate number of names in a cell. is that possible?
Currently, there are over 200 rows. I hope there will never be more than ten dates or so in the past.
The application is a calibration date tracker.
Additionally:
I would also like to be able to enter a number which would indicate the number of days in the future to find dates in the next so many days and have the names of equipment whose calibration expiration dates which less than that number of days show up in another cell, but that should be easy once the first problem is solved. So, entering "5" would show the names of equipment due for calibration within the next five days.
Finally:
I want the sheet to always open with the top row at the top no matter how it was saved.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Is it possible? Populate a cell from a separate sheet dependent on today’s dateHi! I’ve spent a good hour trying to figure things out on my own, but I figured asking wouldn’t hurt. I have a spreadsheet listing out topics that rotate on a monthly basis. I want the title of that theme to auto-populate on a different sheet in the same spread so that it changes on a monthly basis, and for them to be the same in case the original topic changes as well I can get so far as to have the cell match the title using =‘2026’ ! Cell, but is there a way where I can get it to change based off the date? The original spreadsheet (not designed by me) is separated into columns titled Month, Location, Theme, and for some reason Date Range, though it’s always the beginning to end of the month. From there, we have 11 rows to each month. The only data that changes is the Topic. I am needing to create a new sheet based off these topics with additional information, but since the topics are subject to change, I’d like them to be attached to the original spreadsheet. submitted by /u/asclepicats [link] [comments]
- How to stop data in new cells from inheriting formatting from previous rows?I have a sheet where certain rows / columns have fill colors applied to make things easier to read. For example: Rows 1-12 (Jan - Dec 2025 ) have a fill color. Rows 13-24 (Jan - Dec 2026) do not have any fill color with the exception of column B, which has the manager's name. I can highlight the cells in rows 13-24 and set them to have no fill. But when I type a value in any of those cells and tab or enter, the cell inherits the color fill from the section above, even if the row directly above it has no fill. There is no conditional formatting in use on the cells/rows in question. I typed 15 in cell F9 and clicked enter, but I do not want this formatting to auto apply. submitted by /u/StandardWriting3069 [link] [comments]
- Conditional formatting date help neededI've been trying to use conditional formatting to help automate my work spreadsheet and the date formulas truly escape me. I feel like TODAY is a meany who likes to stick their tongue out at you and point for being stupid XD. This is a spreadsheet with a schedule on it. I am trying to get it to automatically grey out the text when the date passes so I can sort and filter by color and always keep the next upcoming appointment slot be top of the list, while still keeping the data in this sheet because another sheet refers to it via XLOOKUP. https://preview.redd.it/z1jqata8w6xg1.png?width=364&format=png&auto=webp&s=d49f71c8de80c402de1af923fc87e3371d606cc8 Here's the formula I'm using =AND($B$2<TODAY(), $D$2<> "") Column D is client names, for privacy purposes I didn't copy that. They end at D11, if it matters. I'm not sure why excel is treating the dates in May as if they are less than today, when they're not. Does anyone have any ideas? submitted by /u/tashykat [link] [comments]
- Conditional Formatting to show completion involving multiple cells.I'm building a progresss sheet that is based on completion of multiple conditionals. The conditionals are e22:e25 and I22:I25, each representing a different requirement. I want to do a tracking bar at the base of the sheet using three formating rules based on the completion date of the reqs. Rule 1 is =e22:e25,I22:25<Today() Rule 2 is =e22:e25,I22:25>=Today() These come with specific cell formats and work. I'd like to add a 3rd rule that shows a progess percentage. For example lets say e22, e24, e25 and I23 are all done but the rest remain with no completion date enter it would show a bar 50% filled and the words in progress across the bar. submitted by /u/juupmelech626 [link] [comments]