# Cool Excel Formula for eBay Affiliates

1. Cool Excel Formula for eBay Affiliates
I posted this in another thread but I think it's worthy of it's own.

Most of you know that you can download an Excel spreadsheet of your eBay sales through CJ. Most of you also know that the OID cell contains the auction number.

How many of you sit there with the high ones double clicking the cell, plucking out the auction number, and going to eBay to see what it was? Can't resist, I know.

How cool would it be to just have a clickable link right in the spreadsheet for each item?

Code:
`=HYPERLINK(CONCATENATE("http://cgi.ebay.com/ws/eBayISAPI.dll?ViewItem&item=",MID(P28,((SEARCH(";",P28,1))+1),((SEARCH(";",P28,((SEARCH(";",P28,1))+1)))-(SEARCH(";",P28,1))-1))), "Show Me The Money!")`
Take that code, paste it in row 2 of the first new column, then copy that cell all the way down the column. Voila.

2. There is in a error in your formula. You say to place it in Row 2 of the last Column (which would be Column T); however, you have hard coded P28 as the reference cell. If you place the formula in that cell it's going to pull the auction for Row 28. Copying it to the next row will produce the results for Row 29, and so on.

You need to correct your code to replace all the P28's with P2 for your formula to work as you intend it to.

Here's the formula I've always used:

Code:
`="http://cgi.ebay.com/ws/eBayISAPI.dll?ViewItem&item="&MID(P2,FIND(";",P2)+1,12)`
It Also needs to be placed in Cell T2; however, it doesn't create an automatic hyperlink. I just place my cursor in the cell, and press copy (CTRL-C) and paste it into a browser. This way, I just use the same browser window instead of having a new window open for each auction.

3. I don't where those 8's came from. When I use my macro it adds it perfectly. Thanks Tracy.

Here it is corrected....

Code:
`=HYPERLINK(CONCATENATE("http://cgi.ebay.com/ws/eBayISAPI.dll?ViewItem&item=",MID(P2,((SEARCH(";",P2,1))+1),((SEARCH(";",P2,((SEARCH(";",P2,1))+1)))-(SEARCH(";",P2,1))-1))), "Show Me The Money!")`

4. Originally Posted by bobby131313
I don't where those 8's came from. When I use my macro it adds it perfectly. Thanks Tracy.

Here it is corrected....

Code:
`=HYPERLINK(CONCATENATE("http://cgi.ebay.com/ws/eBayISAPI.dll?ViewItem&item=",MID(P2,((SEARCH(";",P2,1))+1),((SEARCH(";",P2,((SEARCH(";",P2,1))+1)))-(SEARCH(";",P2,1))-1))), "Show Me The Money!")`
Great formula! Thanks for sharing that.