Intelligent Document Tracker - Please Help!

liamthefiends

New Member
Joined
Nov 24, 2022
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Good morning, Afternoon or Evening,

I am an engineer and have recently changed the company I work for. At my old place of work we had a very clever engineering document tracker, and I am trying to re-invent this at my new place of work to help everything stay organised when we are busy. However, I am having great difficulty getting my head around how the author of the excel sheet achieved this at my previous work.

I want to learn to walk before I try running so I just want to get the most basic function working first, of which I will explain below.

So as an engineering company, suppliers of equipment send us documents. The first time the supplier sends the document it is on revision A or 0 (depending on supplier). We then mark up any comments on the document and send it back to the supplier. The supplier then sends us the document as revision B or 1 (again depending on supplier). This process repeats until all parties are happy, sometimes up to revision G (or 5) and beyond. On a project there could be multiple suppliers with 50 documents each so a good spreadsheet is essential to keep track of this.

We have a basic spreadsheet that tracks things entirely by manual data entry, no calculations or formulas. This is where I would like to add some intelligence.

Please see the screenshot below. All we need to look at for now is the Rev cells (highlighted blue) and Latest Rev cell (highlighted red). The spreadsheet keeps on going to the right off screen to account for the supplier sending up to ten revisions of the document. What I need the sheet to do is, look at all ten Rev cells (only 3 shown on screen) and pick out the latest revision and display it in the Latest Rev Cell. The formula would need to somehow work from right to left until it finds a Rev cell that isn't blank and then display that value shown in the first Rev cell it landed on. Bear in mind this formula needs to work for both numbers and letters or maybe even both (the row below shows a numbered example), it really just needs to pick up and display the first value in a non-blank Rev cell, working from right to left.

I have no idea how it was achieved at my previous work, and I am completely stuck. Any help would be greatly appreciated!!

1669318418980.png
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Welcome to the forum
try this formula :
=INDEX(A3:W3,1,5*ROUNDDOWN((COUNT(B3:W3)/5),0))
I have assumed yor columns go out to column W , but this can be changed to whatever you want
 
Upvote 0

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top