FearTheDuchess
New Member
- Joined
- May 27, 2016
- Messages
- 5
Hello community!
I have really been struggling with this formula for a while now, I'll try and describe it as succinctly as possible. I have an externally referenced workbook with many tabs (December 2015, January 2016, and so on...) on it containing Ticker Symbols for stocks in the first column, and a table for column periods on the horiz. axis. It looks something like this on every tab in that worksheet:
<colgroup><col><col><col><col span="2"><col><col><col span="3"><col><col><col span="2"><col></colgroup><tbody>
</tbody>
In a separate workbook, I essentially need the formula to vlookup an indirect cell (Where I can dynamically change to March 2016, April 2016, etc), look up the ticker symbol within that sheet name, and return the correct time period column value for that month. I got the vlookup to work on one tab so far ("Current performance"), I am just having a lot of diffculty forcing the formula to read the tab names with indirect and go to the corresponding one (December 2015, March 2016, etc.). Here is what I have so far that works to return the correct period values:
VLOOKUP($G11,'Z:\EandF\BRENT\PerformanceReferences\Performance & Fund Fact Sheets\[PerformanceDatabase.xlsx]CurrentPerformance'!$A$4:$AB$250,HLOOKUP(H$10,'Z:\EandF\BRENT\PerformanceReferences\Performance & Fund Fact Sheets\[PerformanceDatabase.xlsx]CurrentPerformance'!$C$2:$O$3,2,FALSE),FALSE)
G11 is the ticker symbol
A4:AB250 is the table full of performance data (External workbook)
H10 is the column title (3 months, 1 year, 3 year)
C2:O3 are the column titles (3 months, 1 year, 3 year) (External workbook) that the H10 reference matches to.
Anyone know how to do this? I realize my description is probably incredibly confusing. Please let me know what kind of additional info I need to provide. I will take you out to dinner if you can help! Thanks!
I have really been struggling with this formula for a while now, I'll try and describe it as succinctly as possible. I have an externally referenced workbook with many tabs (December 2015, January 2016, and so on...) on it containing Ticker Symbols for stocks in the first column, and a table for column periods on the horiz. axis. It looks something like this on every tab in that worksheet:
Ticker | Fund Name | Current Month | 3 Months | YTD | 1 Year | 2 Years | 3 Years | 4 Years | 5 Years | 6 Years | 7 Years | 8 Years | 9 Years | 10 Years |
3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | ||
ASFYX | Natixis ASG Managed Futures Fund | -1.34% | 6.35% | 6.35% | -5.97% | 14.60% | 11.98% | 7.62% | 4.95% | --- | --- | --- | --- | --- |
Newedge | Newedge Trend Index | -2.91% | 3.16% | 3.16% | -4.11% | 14.57% | 6.95% | 5.09% | 3.13% | 3.64% | 3.21% | 3.84% | 5.86% | 5.25% |
AVGRX | Dreyfus Dynamic Total Return Fund | 2.59% | -2.47% | -2.47% | -7.49% | 3.19% | 4.88% | 5.78% | 5.19% | 6.48% | 10.50% | 3.72% | 2.36% | --- |
MSCI Blend | MSCI Blend |
<colgroup><col><col><col><col span="2"><col><col><col span="3"><col><col><col span="2"><col></colgroup><tbody>
</tbody>
In a separate workbook, I essentially need the formula to vlookup an indirect cell (Where I can dynamically change to March 2016, April 2016, etc), look up the ticker symbol within that sheet name, and return the correct time period column value for that month. I got the vlookup to work on one tab so far ("Current performance"), I am just having a lot of diffculty forcing the formula to read the tab names with indirect and go to the corresponding one (December 2015, March 2016, etc.). Here is what I have so far that works to return the correct period values:
VLOOKUP($G11,'Z:\EandF\BRENT\PerformanceReferences\Performance & Fund Fact Sheets\[PerformanceDatabase.xlsx]CurrentPerformance'!$A$4:$AB$250,HLOOKUP(H$10,'Z:\EandF\BRENT\PerformanceReferences\Performance & Fund Fact Sheets\[PerformanceDatabase.xlsx]CurrentPerformance'!$C$2:$O$3,2,FALSE),FALSE)
G11 is the ticker symbol
A4:AB250 is the table full of performance data (External workbook)
H10 is the column title (3 months, 1 year, 3 year)
C2:O3 are the column titles (3 months, 1 year, 3 year) (External workbook) that the H10 reference matches to.
Anyone know how to do this? I realize my description is probably incredibly confusing. Please let me know what kind of additional info I need to provide. I will take you out to dinner if you can help! Thanks!