Background

Email requests are received. They can be classified into 4:

1. A.
2. P.
3. N.W.
4. S.E.

There is a spreadsheet with 5 tables namely, Monitoring, A., P., N.W., S.E. tables. When an email is received, a power automate flow adds the email details as a new row into the Monitoring Table and also into the Table where the request falls under which could be either of the 4 above. Each email is also given an autonumber, the BVC Number which is the "Serial Number" I was referring to in the title of this question.

Let's say an email request came in for A., A., N.W., P., then S.E. then the resulting spreadsheet would look like this:

Monitoring Table:

    Cell A1 BVC Number Cell B1 Classification
    Cell A2 BVC0001    Cell B2 A.
    Cell A3 BVC0002    Cell B3 A.
    Cell A4 BVC0003    Cell B4 N.W.
    Cell A5 BVC0004    Cell B5 P.
    Cell A6 BVC0005    Cell B6 S.E.

"A." Table:

    Cell A1 BVC Number
    Cell A2 BVC0001
    Cell A3 BVC0002

"N.W." Table:

    Cell A1 BVC Number
    Cell A2 BVC0003

"P." Table:

    Cell A1 BVC Number
    Cell A2 BVC0004

"S.E." Table:

    Cell A1 BVC Number
    Cell A2 BVC0005

What I need to be able to do is to turn the BVC Numbers into a hyperlink that leads to where the BVC Number is in the category tabs i.e. if i click on the Cell A2 in the Monitoring table, it would take me to cell A2 in Tab "A." and vice versa i.e. if i click on Cell A2 in the the "S.E." Tab, i would be taken to the Monitoring tab at Cell A6.

I am open using any of the tools like Power Automate, excel hyperlink formula or even excel scripts. Thank you in advance to any soul who will take the time to look at my question.

CodePudding user response：

I would think VBA could provide a cleaner way to accomplish, but of course takes time to write and test. In the meantime, you could use a formula with a helper column.

In my data below, I have 4 sheets (Master, A, B and C). The following formula on the Master sheet, cell C2, will search all other sheets and land on the first match.

=IFERROR(HYPERLINK("#"&CELL("address",INDEX(A!A:A,MATCH(master!A2,A!A:A,0))),master!A2),IFERROR(HYPERLINK("#"&CELL("address",INDEX(B!A:A,MATCH(master!A2,B!A:A,0))),master!A2),HYPERLINK("#"&CELL("address",INDEX('C'!A:A,MATCH(master!A2,'C'!A:A,0))),master!A2)))

Or perhaps easier to view as this:

=IFERROR(HYPERLINK("#"&CELL("address",INDEX(A!A:A,MATCH(master!A2,A!A:A,0))),master!A2),
 IFERROR(HYPERLINK("#"&CELL("address",INDEX(B!A:A,MATCH(master!A2,B!A:A,0))),master!A2),
         HYPERLINK("#"&CELL("address",INDEX(C!A:A,MATCH(master!A2,C!A:A,0))),master!A2)))

Basically, the Hyperlink will find the first match on that sheet, and then each has an IFERROR wrapper to go to the next sheet. You'll need to modify this formula to search all your other sheets.

Conversely, you'll need a helper column in your other sheets to go back to Master. That formula will be much shorter, such as this one found in sheet "A":

=HYPERLINK("#"&CELL("address",INDEX(master!A:A,MATCH(A!A4,master!A:A,0))),A!A4)

"Master" sheet:

"A" sheet: