Dynamic Range Reference to Another Sheet

[jasperhuang93]

Hi everyone,

This is my first post here, and from utilizing this forum to read on issues that I often run into with excel, I thought it would be useful to finally join the community .

I am currently referencing cells Sheet3!F81:F101 on Sheet2 of my workbook in the range N5:N25. However, because cells F81:F101 on Sheet3 are part of an array that is updated daily, those 21 values are not guaranteed to stay in rows 81-101. They will either shift upwards, or downwards. I have only found out recently that the data sets in the array are not always consistent, much to my chagrin. Thus far, the data set has only shifted upwards by one cell, F80:F100 - however, these Sheets belong to a much larger model that relies on this data, and having Sheet2 be referencing empty cells on Sheet3 because I can't be constantly e-mailing updated versions to model users daily is unacceptable.

I should also mention - the array is a two column array consisting of "Terms" and "PV01s" which is my data. The terms for my 21 PV01s are: 1W, 2W, 6M, 1Y, 2Y, 3Y, 4Y, 5Y, 6Y, 7Y, 8Y, 9Y, 10Y, 15Y, 20Y, 25Y, 30Y, 35Y, 40Y, and 50Y.

In the interim, since I have only encountered the cells shifting upwards by one, I am making do with the non-VBA formula of;
"=IF(ISNUMBER(SHEET3!$F$101),SHEET3!F81,SHEET3!F80)" and I use this formula for the range N5:N25 on Sheet2.

Ideally I would like to code in a way for this set of 21 cells to dynamically reference the data, regardless of of shifts. I have been contemplating some method of having Excel search for my above "Terms" and matching it up with their respective PV01s.. but I wouldn't know how to code that, personally.

Please advise! Any help would be greatly appreciated.

[venkat1926]

from a cursory look at your problem it looks you have to use indirect function. but it would be helpful if you post a very small extract of your sheets in a file. there is a provision in this newsgroup to attach a file. in reply window click "go advanced" where the body of the reply can be written and under that there will "manage attachments" click manage attachments, select the file and upload after you write the message.

[JBeaucaire]

INDEX/MATCH can be used to spot a specific value and set an anchor. From there you could use OFFSET() to reshape the anchor to your 21 cells.

[jasperhuang93]

This is a sample of my sheet with some dummy data, hopefully it will help a little. Thanks for everything in advance! I will try the above methods in the meantime Sample.xls

[JBeaucaire]

1) Take the "space" out of J5 changing 1 Y to 1Y. Do this for J5:J25. This makes those values now match exactly to the same strings found in the other sheet.

2) Put this formula in N5 and copy down:

=VLOOKUP(J5, SENSITIVITIES_PV01!E:F, 2, 0)

[jasperhuang93]

Wow, how simple and elegant. You've saved many hairs on my head :D

Cheers!