liamthefiends
New Member
- Joined
- Nov 24, 2022
- Messages
- 1
- Office Version
- 365
- Platform
- 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!!
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!!