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]

suggest splitting Sheet2!$b$2 formula into two parts ie cells B2 and say C2

1 . SUBSTITUTE(TRIM(SUBSTITUTE("High Mountain Imports"&[@Item],"&","%26"))," ","+")
2 . "Search High Mountain"

Part 1 B2

=SUBSTITUTE(TRIM(SUBSTITUTE("https://highmountainimports.com/search.php?search_query="&[@Item],"&","%26"))," ","+")

Part2 C2

Search High Mountain

and Modify your ifs formula by replacing Sheet2!$B$2 with HYPERLINK(Sheet2!$B$2,Sheet2!$C$2)

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

 

[barberic5]

suggest splitting Sheet2!$b$2 formula into two parts ie cells B2 and say C2

1 . SUBSTITUTE(TRIM(SUBSTITUTE("High Mountain Imports"&[@Item],"&","%26"))," ","+")
2 . "Search High Mountain"

Part 1 B2

=SUBSTITUTE(TRIM(SUBSTITUTE("https://highmountainimports.com/search.php?search_query="&[@Item],"&","%26"))," ","+")

Part2 C2

Search High Mountain

and Modify your ifs formula by replacing Sheet2!$B$2 with HYPERLINK(Sheet2!$B$2,Sheet2!$C$2)

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

This doesn't work because Part 1 of your suggestion uses [@Item] in the formula which references a column in the table on Sheet1.

I need the formula in Sheet1 to conduct a search of a Product Name on the respective wholesalers website. Therefore the search URL will be different for every product in Sheet1 depending on the selected Wholesaler. The search URL will also be different for each product, since every row in the Sheet1 table will be a different product. It uses [@Item] (the Product Name), to search that product on the respective wholesalers website. So when I use try Part 1 of your suggestion, it does not work because it includes [@Item], which references the Product Name in Sheet1.

Sorry if that's confusing, I'm not good at articulating this. Let me know if I need to explain this better.

 

[barberic5]

Here is a link to the spreadsheet if that helps

Inventory Example.xlsx

Sheet1 Logged Off LeafTrade,x Item,Selected,Unit Cost,Quantity,Category,Supplier,*Link,*Search,Column1,*Search2,Notes,URL,*Logged Off LeafTrade URL,*Clean URL,*Clean URL Results Keck Clip,$1.00,High Mountain Imports,<a href="https://highmountainimports.com/k-clip">Link</a>,HYPERLINK(SUBSTITUTE(T...

docs.google.com