Use a cell value as part of a formula

[barberic5]

I am trying to create a formula that uses the value of another cell as an argument in my formula.

This is my current formula:
=IFS([@Item]="","",$B$1="x",Sheet2!$B$2,TRUE,HYPERLINK(SUBSTITUTE(TRIM(SUBSTITUTE("Log In — Leaf Trade"&[@Item],"&","%26"))," ","+"),"Search LeafTrade"))

This is the value in Sheet2!$B$2:
HYPERLINK(SUBSTITUTE(TRIM(SUBSTITUTE("High Mountain Imports"&[@Item],"&","%26"))," ","+"),"Search High Mountain")

My formula won't actually calculate based on the value in Sheet2!$B$2, it just display exactly what i typed in Sheet2!$B$2

Zen Leaf Inventory Shopping List 2.xlsx
	H
2	*Search
3	HYPERLINK(SUBSTITUTE(TRIM(SUBSTITUTE("https://highmountainimports.com/search.php?search_query="&[@Item],"&","%26"))," ","+"),"Search High Mountain")
4	HYPERLINK(SUBSTITUTE(TRIM(SUBSTITUTE("https://highmountainimports.com/search.php?search_query="&[@Item],"&","%26"))," ","+"),"Search High Mountain")
5	HYPERLINK(SUBSTITUTE(TRIM(SUBSTITUTE("https://highmountainimports.com/search.php?search_query="&[@Item],"&","%26"))," ","+"),"Search High Mountain")
6	HYPERLINK(SUBSTITUTE(TRIM(SUBSTITUTE("https://highmountainimports.com/search.php?search_query="&[@Item],"&","%26"))," ","+"),"Search High Mountain")
7	HYPERLINK(SUBSTITUTE(TRIM(SUBSTITUTE("https://highmountainimports.com/search.php?search_query="&[@Item],"&","%26"))," ","+"),"Search High Mountain")
8	HYPERLINK(SUBSTITUTE(TRIM(SUBSTITUTE("https://highmountainimports.com/search.php?search_query="&[@Item],"&","%26"))," ","+"),"Search High Mountain")
9	HYPERLINK(SUBSTITUTE(TRIM(SUBSTITUTE("https://highmountainimports.com/search.php?search_query="&[@Item],"&","%26"))," ","+"),"Search High Mountain")
10	HYPERLINK(SUBSTITUTE(TRIM(SUBSTITUTE("https://highmountainimports.com/search.php?search_query="&[@Item],"&","%26"))," ","+"),"Search High Mountain")
Sheet1

Cell Formulas
Range	Formula
H3:H10	H3	=IFS([@Item]="","",$B$1="x",Sheet2!$B$2,TRUE,HYPERLINK(SUBSTITUTE(TRIM(SUBSTITUTE("https://app.leaf.trade/shop/high-mountain-imports/search.php?search_query="&[@Item],"&","%26"))," ","+"),"Search LeafTrade"))

Zen Leaf Inventory Shopping List 2.xlsx
	B
2	HYPERLINK(SUBSTITUTE(TRIM(SUBSTITUTE("https://highmountainimports.com/search.php?search_query="&[@Item],"&","%26"))," ","+"),"Search High Mountain")
Sheet2

 

[bbrnx19]

try replacing

Sheet2!$B$2

with

indirect(Sheet2!$B$2)

or

indirect(text(Sheet2!$B$2))

 

[barberic5]

Neither of those worked. It returns a #REF! error for the first one. The TEXT formula was incomplete for the second one, it didn't include the format portion of the formula

 

[bbrnx19]

and this

indirect(chr(34)&Sheet2!$B$2&chr(34))

 

[barberic5]

Still returns an #REF! error.

Excel is so odd, sometimes the simplest tasks are the hardest thing to accomplish.

 

[Fluff]

How about

indirect("Sheet2!$B$2")

 

[barberic5]

That didn't work either. I get no errors, but it returns the same value as in my original post.

 

[Fluff]

The you need to clearly explain what you are trying to do, your formula is currently saying that if B1="x" return the value of B2 on sheet2

 

[barberic5]

The you need to clearly explain what you are trying to do, your formula is currently saying that if B1="x" return the value of B2 on sheet2

Yes, that is exactly what I am trying to do. However, B2 on Sheet2!$B$2 is a formula. So for Sheet1 Column H, I need it to actually calculate the formula from Sheet2!$B$2, as long as Sheet1!$B$1="x".


Sheet 1:
On my table in Sheet1 I have multiple columns representing: Product, Vendor, URL Link, etc.
Sheet1 Column H is the URL Link (Column named "*Search")

Sheet 2:
Sheet2 will have a table which lists every unique vendor from my table on Sheet1. Each vendor on Sheet2 will have a custom formula designed to modify its URL so non-managers can have access to a different website which hides sensitive company info (like pricing, etc.). The formula for how to modify the original URL will be different for each vendor.

IF $B$1="x": Modify original URL
IF $B$1<>"x": Use original URL

What I am trying to accomplish is a dynamic formula for Sheet1 Column H. The table on Sheet2 with the vendor list will change frequently and be quite long. That would make it a difficult to manage a long IF Statement for Sheet1 Column H. So, the table on Sheet2 is designed to hold all the URL modification formulas. Sheet1 Column H will then use XLOOKUP to match the vendor from Sheet1 to its corresponding URL modification formula (currently XLOOKUP is not necessary while I test this out with a single value in Sheet2. I will finish populating Sheet2 with all the vendors and add XLOOKUP to my Sheet1 Column H formula once I can get this working properly.)

 

[Fluff]

IF $B$1="x": Modify original URL

But the formula you posted if not trying to modify anything, it's just returning the value of B2 from sheet2.
If you need to modify the url, then you will need to explain how it should be modified.