Is this possible - type of Vlookup dynamic range

bottera,

Welcome to the forum. If I understand you correctly (and I'm not convinced I do!) you're having trouble populating the "Original Weight" column in the "Consolidated" tab - is this correct? Forgive me, as I'm unfamiliar with some of the terms & logic behind what you're doing - I just need to know what you want, and where you want it

If I am on the right track, have a look at Column K in the "Consolidated" tab, and see if/how that might work for you.

it seems like it is on the right track. I was trying to change portfolio names and it works but not completely. in cell i2 if I change the portfolio 1 to portfolio 2 it automatically changes the original weight (this is great), but if I change it to portfolio 3 i get an error N/A. how could I fix this.

i will have to learn more about index and matching it seems.

does what I am trying to do make sense at all or is it confusing?

Originally Posted by bottera

I was trying to change portfolio names and it works but not completely. in cell i2 if I change the portfolio 1 to portfolio 2 it automatically changes the original weight (this is great), but if I change it to portfolio 3 i get an error N/A. how could I fix this.

You can fix this easily by correcting the typing error you have in your "Portfolio" tab - you have PorTfolio 3 entered as "Porfolio 3" - you're missing the "T".

Whoops, looks like you're not the only one making mistakes Put this:

Please Login or Register  to view this content.

in K2 (committed with Ctrl, Shift & Enter at the same time). I didn't make the cell references absolute initially, so when the formula was copied down through Column K, the cell references were changing, when of course they shouldn't, as we want to look in the same place for each search. Sorry about that!

works perfect...I will have to adjust everything to match, but this seems like it will work great. If I have any other issues I will let you know.

you mentioned ctrl+shift+enter and I notice {} around the formula...what does this do by chance? I see how it makes a difference, but don't know what/how this does.

edit: googled it and it seems you can array an excel formula...no idea this could be done...i have a lot to learn still it seems

any guides that describe all these things and other useful things?

The formula I gave you is an array formula, and it requires those curly braces {} to work properly. See Google/MSOffice Help/countless online articles for a far better explanation of the ins & outs than I could ever manage

Glad to hear you're on the right track. How does that leave you fixed for the other column you want to populate (current weight)?

bottera, is there some (any?) reason to keep the original data when you have new data from a different point in time?

(It would be a lot more straightforward if you didn't need to keep the "old" data, and just overwrote existing data, hence my question.)

no I do not need to keep the old data and would prefer not to . I gave that example just to show two periods in time and what types of changes would occur.


the next part I am wondering is if it is possible to have the array function loop through the data to check for an increase or decrease in positions/tickers and consolidate/grab the list.

in the 2nd example I posted if you notice portfolio 1 had two tickers added. now I didn't set those cells = to the respective part on bb report 1, but that is how I currently have it. Is there a way to say count the number of positions and create a vlookup/array function/etc and print the tickers?

I noticed a small issue with the array...it isn't grabbing the correct value for some reason. I have attached an example from my actual worksheet which might be more clear.

even though the portfolio name matches in cell a1 and in the reference area, it is not grabbing the correct value. when the name of the portfolio is say value1 it returns N/A, but when the name of the portfolio is value2 it returns the value which should have been input when the name was value1.

there are notes on sheet1 which can show the issue happening.

edit: it seems to be because they are the last entries to that portfolio name..is this common or am i just missing something small?

Originally Posted by bottera

no I do not need to keep the old data and would prefer not to

So why not just paste the new data over the old data? Simples

Also, formula is not working in your latest spreadsheet because you're using incorrect cell references. You need:

Please Login or Register  to view this content.

The data on Sheet2 starts in Row 1, so you need to use Row 1 references - you were referencing Sheet2!$A$2 and Sheet2!$C$2 in your formula.

I'll have a little look at your 2nd request later, should be do-able.

Originally Posted by bottera

the next part I am wondering is if it is possible to have the array function loop through the data to check for an increase or decrease in positions/tickers and consolidate/grab the list.

Why not have a 2nd sheet for data - one called "beginning weight" and one called "current weight" (or whatever.) Column D on your sheet1 can look at the tab "beginning weight" and return the % as it does now. Column E can look to the "current weight" tab in the same way, and Column F can then show the difference between the 2, allowing you to highlight increases/decreases.

If you get NEW data, copy the "current weight" data into the "beginning weight" tab, copy the NEW data into the "current wight" tab, and..... you get the picture. That might not be as elegant a solution as you're hoping for, but it's a solution that you currently have the know-how to put in place.

Originally Posted by bottera

in the 2nd example I posted if you notice portfolio 1 had two tickers added. now I didn't set those cells = to the respective part on bb report 1, but that is how I currently have it. Is there a way to say count the number of positions and create a vlookup/array function/etc and print the tickers?

I actually haven't a clue what you're asking for here - sorry.