Thanks roaminoz, thought there had to be a better way!

First 10 Last

Share
Profile

Follow
Whataboutthisone

97 Posts.

4

15/05/18

15:42

Post #: 33109345
Share

Originally posted by roaminoz ↑

In your current holdings tab, you should put in the stock code as your reference to make the changes to the sheet rather than the company name. Unless one knows the exact company name value the table will not populate. Since everyone knows their stock code and after all it is only 3 characters it is a much better choice for the users

I see you have used vlookup as your formula in the table. This works fine in your way of set up, but you should use the index/match formula. Doing so allows you to look up column B, but return results from column A. A task that is impossible for vlookup to do.

Formula For A4 in portfolio sheet
INDEX(ASX!$A$4:$A$2407,MATCH('Current Holdings'!B4,ASX!$B$4:$B$2407,0))
You can then copy that and paste it down the column from A5 to A25.

This then allows the user to change column B, the rest of the table (vlookup) can stay the same as once company name (column A) populates the columns C to M will populate.

---------------------------------------------------------

In sheet where you have value errors, use the If(Iserror() function. This is just easy on the eye if a cell is blank compared to #value in the cell.

Formula would be Cell:ASXH4 = is(iserror(D4)=true,"",D4/D4*100)

Not sure how D4 is populating, probably through a marco/or Data Connections is grabbing data from a website?

-------------------------------------------------------

I see you only have the ability to search ASX stocks in the current holdings sheet, if you wish to also have the ability to search NASDAQ or NYSE you'll need to add another column in the current holdings probably between A and B to have which stock exchange you wish to search upon. Then use the indirect formula to search a certain sheet on your choosing.

The user will have to put in their stock code and also disallowed
Col B = ASX ____ Col C = TLS = ASX:TLS
Col B = NYSE ___ Col C = ABC = NYSE:ABC
Use the indirect formula to search disallowed sheet, and use the vlookup to lookup the code

------------------------------------------------------

All the above is just food for though anyway. Happy formulating.

Expand

Thanks roaminoz, thought there had to be a better way!
1 Upvote

Reply