Hey everybody!

So, I'm on to another Workbook, this time tracking the release of versioned Task Cards by Month and Week (of the month).

Each Month has its own Tab, with Named Ranges for each Week (1-5) of that month.

The data we need to search through includes the Task Card Number, its Release Date, and the Version, and each data point has its own column.

What we need is a formula to search all previous Release Date entries, from 01/01/2025 until the date of this entry, for duplicate Task Card Number entries, and if found - return the most recent Release Date and Version of that Task Card entry in another two columns titled Last Release Date. (I haven't added this column yet!) Okay - this column has been added to EXAMPLE B)

For Example:

In the March tab we enter: Task Card Number - ABC123; Version - 3; Release Date 03/03/2025.

The last times this Task Card was released, was on 02/15/2025 Version - 2, and on 01/07/2025 Version - 1.

The formula needs to search for previous entries of Task Card ABC123 and, if found, populate the Release Date and Version, in this case 02/15/2025 Version - 2.

I have attached an example of the whole spreadsheet for reference.



Any ideas would be appreciated, I would prefer to avoid VBA if possible as this will be housed on a SharePoint site (out of my control) that does not allow VBA, Macros, etc. (yea, I know).

Thanks!

Attached Files

	Task_Card_EXAMPLE.xltx (188.4 KB, 5 views)
	Task_Card_EXAMPLE_B.xltx (192.9 KB, 5 views)

Instead of adding new sheets every month, and updating reports every time, I'd advice more database-like approach, with all tasks registered in single table, and getting the summary info from specific year, month, or even week using report sheets, where you select the report period, and wanted info is returned (in any format you like it) from data entry sheet.

Added is an example file for data entry (I entered data from 2 weeks from 2 different months).

Attached Files

TaskInfo.xlsx (28.2 KB, 4 views)

Thank you, ArviLaanemets, for the feedback!

This is an interesting way to approach this project, I will have to start again and reconfigure to this structure!

However, this still doesn't give me what I'm looking for. In your example, each Task Card number is used twice, once in March and once in April. What I need to know is when the Task Card number is entered for April, that the same Task Card number was entered before, perhaps multiple times (so just the most recent entry), the (previous) Task Card Released Date and Version number that was released.

A possible solution for your last post.

I added 3 helper columns, and a table on same sheet, which demonstrates, how to get info about previous use of same Task Card number. You can add this info into Table tTasks instead, or you can use the logic of my example to get this info into some of your reports (to keep several Tables on same sheet permanently is not very good practice). In case the Task Card was not used previously, the formula in PrevTCardRow returns an empty string.

And columns for year, month and week numbers are also helper columns, and needed only when you design reports. After this is done, you can hide them (I colored headers for all such columns orange).

NB! As you see, those formulas don't depend on position of entry in table - i.e. you can sort your data entry table in any way, and the results remain same!

The only limitation with current formulas is, you can't have the same Task Card number on same date more than once! But of-course it is possible to change formulas in columns TCardOrd and PrevTCardRow so, that the task cards are numbered separately for some additional condition (e.g. by column Processor - then the formula in TCardOrd returns e.g. the order number of given card for this card and processor)

Attached Files

TaskInfo.xlsx (30.0 KB, 3 views)

Thanks, again, ArviLaanemets!

Trying it now. I figured helper columns would be needed as they are the only way I have been able to perform other tasks in Excel.

I will let you know!

Oh joy. The boss just saw my new rendition of this project (based on your example) and she chewed me out. She wants it exactly as it was, with multiple tabs, etc.

It was working, too...

I guess it's back to the drawing board! Thanks again for your help...

OH! - Unless of course you can still help me figure this out with the original structure?!

The positive effect of this is, you don't have to worry about having to work on something You are always needed!

I myself prefer to work on something new instead updating the same file for years.

I had a case, where I got a task to update the Excel file I created about 20 years earlier, as there was a new workplace added. All those 20 year the file was in use without any need for updates so long (users added new data daily, and in case the file did slow down, removed some bulk amount of obsolete data running a procedure)

Why make things easy when complicated things also work

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

ArviLaanemets

Yea, right! But I am a Technical Writer, NOT an Excel expert - and yet, you do one thing in Excel and suddenly that's everything you do!