MichaelMLM
New Member
- Joined
- Jan 7, 2014
- Messages
- 25
I have 4 Tabs on a spread sheet; 3 with existing data (Data Tabs or sheets) and then one report tab to compile desired data (Report tab or sheet). All 3 Data sheets contain one primary row of information to convey to the Report tab or sheet. Data sheets have stock or ETF symbols in column "C" with up to 100 listings or rows, and the 10 year performances of each in column "I" - same row. In the Reporting tab there are only 5 cells to choose from to report on each of the 3 Data sheets (so out of 100 choices only 5 can be chosen to report on). Data sheets are labeled "Equities-Indecies", "Bonds" and "Other". I have allowed the user to choose their symbol in cell columns “C” on the Report sheet from the list on the Data sheets when filling in the stocks they want to track in the Reporting sheet.
To do this, I duplicated the “If” function in the reporting sheet as follows (in cell columns E):
=IF(C10='Equities-Indecies'!$C$5,'Equities-Indecies'!$H$5)+IF('Investment Allocation Table'!C10='Equities-Indecies'!$C$6,'Equities-Indecies'!$H$6)+IF('Investment Allocation Table'!C10='Equities-Indecies'!$C$7,'Equities-Indecies'!$H$7… and so on.
Once completed, the user then goes to the reporting tab, types in the symbol they are interested in tracking and the corresponding 10 year yield appears in the cell of this formula. The goal being, to list several assets to see what combination will best suite ones appetite for risk and return.
My solutions seems very archaic. There has got to be a simpler way. Does anyone have any advice on this? (Note, this is for myself and a private group, not intended as a professional development) Thanks for your time and attention!!!
To do this, I duplicated the “If” function in the reporting sheet as follows (in cell columns E):
=IF(C10='Equities-Indecies'!$C$5,'Equities-Indecies'!$H$5)+IF('Investment Allocation Table'!C10='Equities-Indecies'!$C$6,'Equities-Indecies'!$H$6)+IF('Investment Allocation Table'!C10='Equities-Indecies'!$C$7,'Equities-Indecies'!$H$7… and so on.
Once completed, the user then goes to the reporting tab, types in the symbol they are interested in tracking and the corresponding 10 year yield appears in the cell of this formula. The goal being, to list several assets to see what combination will best suite ones appetite for risk and return.
My solutions seems very archaic. There has got to be a simpler way. Does anyone have any advice on this? (Note, this is for myself and a private group, not intended as a professional development) Thanks for your time and attention!!!
Last edited: