Hyper linking by concatenating values in two cells

Dear experts,

Need a formulae which will do the following
1. Concatenate value in cell D1 and upper most value in cell D7, and
2. create an hyperlink in cell D7 to location 'sheet'!2B3
3. Cell D7 will have formulae which generates value 'jhj'
4. for expample what appears in cell D7 will be as follows: ="b3"&"          "&formulae which give bring the value of 'jhj' which is generated as a result of formulae in other sheets

Kindly see if there is possibility of providing an excel based formulae.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:

it's possible to create a hyperlink with the formula

=HYPERLINK("[Hyperlink-sheetname-and-cell-to-.xlsx]"&D1&LEFT(TRIM(D7),2),"click here")

You need to ensure that the text in D1 references an existing sheet, though. In your sample file it does not. A correct entry would be


Also, the formula will need to be in a cell other than D7, since D7 contains text that goes into the formula. You can't have both input text and a formula in the same cell.

see attached.

cheers, teylyn


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rahul GadeSr. ArchitectCommented:
Value to the D7 can be assigned directly, while for creating Hyperlink programatically you can use:
Call Target.Hyperlinks.Add(Anchor:=Target, Address:="", SubAddress:="Sheet2!B3", TextToDisplay:="Sheet2!B3")

-Rahul Gade
ExcellearnerAuthor Commented:

thank you for the comment.

Rahul Gade:

Thank you for the comment too.

Can you please put it in the spreadsheet and forward it to me.

Thank you
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.