2

I have utility that I've been working on (modified from an existing sheet) for a Tabletop game I run for my group.

Currently, it generates random items from a list. The name of each of the items has a corresponding hyperlink to the items description from the same sheet.

An editable copy of the generator may be found here.

My goal is to populate a new cell with with BOTH the item's name, and it's description.

So, if the generator returns an item name in cell "C4" with a hyperlink to its corresponding description cell, what formula should I use to populate this new cell (or cells) with both the name, and the descriptor?

Here is an image of the generator, with the hyperlink to the description cell on a different page. Generator Sheet

Clicking the hyperlink routes you to the corresponding "Description" cell. [Description Cell3

My ideal behavior, is for the formula to insert both the Name and the Descriptor into the new cell.

A visual example of the outcome I'm looking for can be found below. Cell with name and descriptor

Any advice or ideas would be appreciated.

1

I created a new sheet named marikamitsos and added 4 possible alternative solutions on cells I4:X4.

I believe one of them will suit your needs.

Solution 1
The first one uses an added helper column (M) on your Cyphers sheet. Using this formula we manage to have a single cell with a link as we had in this answer :

=vlookup(B4,Cyphers!A:Z,13,false)

The newly added Helper column has the following needed formula:

=ArrayFormula(HYPERLINK( 
                "#gid=0&range="&L2:L632,ArrayFormula(IF(LEN(J2:J632)<>0,K2:K632&CHAR(10)&CHAR(10)&J2:J632,""))))

Solution 2
For the second one we just put together the twice used VLOOKUP function which again produces a single cell but no link this time.

=vlookup(B4,Cyphers!A:L,2,false)&CHAR(10)&CHAR(10)&vlookup(B4,Cyphers!A:L,10,false)

Solution 3
On this third one we use the following formula which returns two separate fields one next to the other:

=TRANSPOSE(ArrayFormula(vlookup(B4,Cyphers!A:L,{2;10},false)))

Solution 4
Finally the fourth solution is very similar to the third one. The only difference is that this time the two fields are one under the other

=TRANSPOSE(ArrayFormula(vlookup(B4,Cyphers!A:L,{2,10},false)))

Pro Tip: Instead of having 4 formulas in cells C:F you can use a single one (look at my examples on the sheet):

=ArrayFormula(vlookup(B4,Cyphers!A:Z,{13,4,7,8},false))

  • Please remember -as always- that as per site guidelines when an answer addresses your question, you should accept it and even upvote it so others can benefit as well. – marikamitsos Feb 10 at 10:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.