Access fields from one sheet to another based on 1 criteria, multiple columns

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!!!
 
Last edited:

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

MichaelMLM

New Member
Joined
Jan 7, 2014
Messages
25
Works for me! Thanks, I've only used it on summing, didn't thing about it for this application- thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,130,448
Messages
5,642,218
Members
417,262
Latest member
andrewd1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top