FrankNJohnson
New Member
- Joined
- Dec 27, 2017
- Messages
- 5
- Office Version
- 365
- 2013
- Platform
- Windows
Friends:
I'm currently using Excel 2013 for Windows and trying to arrive at a formula which will create a hyperlinked web page reference in one branch of a formula but not in another. The spreadsheet on which I'm working is meant to allow folks at our company to enter a product's sku number for one brand in Column A, and then have formulas return the equivalent sku number for a different brand in Column B, the product's title in Column C, and a link to the product's page on our website in Column D (there are subsequent columns which create links to the product's page on other websites). Here is a screenshot of the relevant portion of my spreadsheet (sorry, my company's IT department would frown on me installing the XL2BB add-in, otherwise I would do it that way):
The formula in cell D8 is as follows:
If I enter a sku in Cell A8 that has a value of zero in column 47 (for the row of the sku that is entered) of the invweb table, everything works fine - it returns "No Fry ID' and the cell is not hyperlinked to anything. You can see that in the screenshot in Row 8.
If I enter a sku in Cell A8 that has a corresponding value other than blank or zero in column 47, then everything works perfectly. You can see that in the screenshot in Row 9. The hyperlink to the product page is created correctly.
But if I subsequently enter a sku in Cell A8 that has a value of zero in column 47, it returns a value of "No Fry ID" but the cell is hyperlinked to a non-existent web page (and so returns an error). You can see that in the screenshot in Row 10 (I had previously entered a different sku in Cell A10 that had a non-zero value in column 47).
Is there a way to write a formula (or perhaps format the cell?) so that the hyperlinked state is not maintained but depends completely on what is returned by the formula?
Thanks in advance for any help anyone can offer - it's much appreciated!
I'm currently using Excel 2013 for Windows and trying to arrive at a formula which will create a hyperlinked web page reference in one branch of a formula but not in another. The spreadsheet on which I'm working is meant to allow folks at our company to enter a product's sku number for one brand in Column A, and then have formulas return the equivalent sku number for a different brand in Column B, the product's title in Column C, and a link to the product's page on our website in Column D (there are subsequent columns which create links to the product's page on other websites). Here is a screenshot of the relevant portion of my spreadsheet (sorry, my company's IT department would frown on me installing the XL2BB add-in, otherwise I would do it that way):
The formula in cell D8 is as follows:
VBA Code:
=IF(A8="","",IF(ISERROR(VLOOKUP(A8,invweb,47,0))=TRUE,"",IF(VLOOKUP(A8,invweb,47,0)=0,"No Fry ID",HYPERLINK(CONCATENATE("https://www.telescope.com/catalog/product.jsp?productId=",VLOOKUP(A8,invweb,47,0))))))If I enter a sku in Cell A8 that has a value of zero in column 47 (for the row of the sku that is entered) of the invweb table, everything works fine - it returns "No Fry ID' and the cell is not hyperlinked to anything. You can see that in the screenshot in Row 8.
If I enter a sku in Cell A8 that has a corresponding value other than blank or zero in column 47, then everything works perfectly. You can see that in the screenshot in Row 9. The hyperlink to the product page is created correctly.
But if I subsequently enter a sku in Cell A8 that has a value of zero in column 47, it returns a value of "No Fry ID" but the cell is hyperlinked to a non-existent web page (and so returns an error). You can see that in the screenshot in Row 10 (I had previously entered a different sku in Cell A10 that had a non-zero value in column 47).
Is there a way to write a formula (or perhaps format the cell?) so that the hyperlinked state is not maintained but depends completely on what is returned by the formula?
Thanks in advance for any help anyone can offer - it's much appreciated!
