Sandcastor
Board Regular
- Joined
- May 14, 2012
- Messages
- 97
Hello all again. It's been a while.
As usual, I have scoured, and cannot find an existing answer. If an answer exists, please redirect, otherwise, I need some help.
Situation: I keep a growing database (excel) of samples, for multiple customers, that I have cataloged and shipped for my job. There are many fields per shipment, but see below for a pared-down version. I also have a label-making sheet that references the database, and am building an at-a-glance report for the customers as well. The source data looks like this:
https://pasteboard.co/IkWdrn7.png
EDIT: Got something here:
<div id="PROTECHNICS SAMPLES LOG SHEET_2253" align="center
xublishsource=" excel"="">Sample Example
<tbody>
</tbody></div>
Currently, I have the label and report sheets reference a specific row number for INDEX-MATCH purposes. Specifically I will Type "120" to reference all of the data on line 120. You know how that works.
What I would like to do instead is to type the WellID associated with the job I'm working on (in the example 56881) and have the INDEX-MATCH refer to the LAST entry in the list that matches. I would prefer to have this done without VBA, as the database is macro-free for ease of use and ease of reference for multiple read-only users.
Is there any solution to this? I expect it will be a new combination of functions I haven't seen before. Thanks!
As usual, I have scoured, and cannot find an existing answer. If an answer exists, please redirect, otherwise, I need some help.
Situation: I keep a growing database (excel) of samples, for multiple customers, that I have cataloged and shipped for my job. There are many fields per shipment, but see below for a pared-down version. I also have a label-making sheet that references the database, and am building an at-a-glance report for the customers as well. The source data looks like this:
https://pasteboard.co/IkWdrn7.png
EDIT: Got something here:
<div id="PROTECHNICS SAMPLES LOG SHEET_2253" align="center
xublishsource=" excel"="">Sample Example
Company | Well ID | Project | Job Type | Sample Received | Sample Range | Received Date | Shipped Date | Samples Billed | Samples Remain | COMMENTS |
Bravo | 56881 | 3794 | C | 01 | 17-12-13 | 17-12-19 | 17-12-19 | 10 | 9 | |
Bravo | 56882 | 3794 | C | 01 | 17-12-13 | 17-12-19 | 17-12-19 | 0 | 0 | |
Charlie | 53439 | 4013 | C | 13 | 17-12-14 | 17-12-21 | 17-12-21 | 20 | 7 | |
Charlie | 53510 | 3238 | C | 10 | 17-12-17 | 17-12-21 | 17-12-21 | 20 | 9 | |
Alpha | 55280 | 3659 | C-O | 10 | ---- | 17-12-21 | 17-12-21 | 10 | 1 | |
Alpha | 55281 | 3659 | C-O | 10 | ---- | 17-12-21 | 17-12-21 | 10 | 3 | |
Bravo | 56881 | 3794 | C | 02-03 | 17-12-14 - 17-12-21 | 17-12-28 | 18-01-02 | 10 | 7 | |
Juliet | 55311 | ---- | C-O | 13-20 | 17-12-06 - 08-01-02 | 18-01-04 | 18-01-05 | 20 | 7 | |
Alpha | 55795 | ---- | C-O | 07-10 | 17-12-29 - 18-01-04 | 18-01-08 | 18-01-08 | 10 | 0 | |
Tango | 55237 | ---- | S | 38-50 | ---- | 18-01-16 | 18-01-22 | 70 | ---- | |
Romeo | 55836 | 3837 | C | 05-07 | 18-01-06 - 18-01-10 | 18-01-25 | 18-01-26 | 20 | 13 | |
Romeo | 46711 | 3837 | G | 18-01-24 | 18-01-25 | 18-01-26 | 0 | (26) | ||
<tbody>
</tbody>
Currently, I have the label and report sheets reference a specific row number for INDEX-MATCH purposes. Specifically I will Type "120" to reference all of the data on line 120. You know how that works.
What I would like to do instead is to type the WellID associated with the job I'm working on (in the example 56881) and have the INDEX-MATCH refer to the LAST entry in the list that matches. I would prefer to have this done without VBA, as the database is macro-free for ease of use and ease of reference for multiple read-only users.
Is there any solution to this? I expect it will be a new combination of functions I haven't seen before. Thanks!
Last edited: