Excel 2010 - Accessing data based on conditions from other sheets

[DarkSnake]

I've tried goggling this a bit and can't seem to find exactly what I'm looking for.

I'm trying to setup a sheet that can run calculations based upon what someone else enters (ex: if they select 'option1', preferably from a drop down list, then two values will be set to the corresponding values, ex: '5' and '6' or whatever they may be). This would preferably actually be within a formula, so say if they have selected an option, it will perform the calculation with the appropriate data, without having to display it on the current sheet.

I was also trying to keep all the data on another sheet so the one I was actually using looked a bit more organized. I've never had to do anything like this in excel before and was looking for some help or a push in the right direction.

 

[Irene]

I have found the 'CHOOSE' function very useful.
Very difficult to explain, but here is Microsoft Office attempt... CHOOSE function - Excel - Microsoft Office

My scenario was to have a cell in which you would select Quarter 1, 2, 3 or 4. The values in the Profit & Loss Account would change accordingly. Here are examples of my scenario (the cells highlighted in the spreadsheet on the right refer to another sheet - CY Actual - in the workbook).

I hope this helps.

 

[Adrian]

I've tried goggling this a bit and can't seem to find exactly what I'm looking for.

I'm trying to setup a sheet that can run calculations based upon what someone else enters (ex: if they select 'option1', preferably from a drop down list, then two values will be set to the corresponding values, ex: '5' and '6' or whatever they may be). This would preferably actually be within a formula, so say if they have selected an option, it will perform the calculation with the appropriate data, without having to display it on the current sheet.

I was also trying to keep all the data on another sheet so the one I was actually using looked a bit more organized. I've never had to do anything like this in excel before and was looking for some help or a push in the right direction.

Depending on the level of experience creating a pivot table for data selection would be an easy option.