Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic formula? not sure if it's even possible to do this with Excel ...
Hi -
I'm attempting to 'merge' three reports into one .... basically, three of the same exact report, just with slightly different base data. At this point, I've transferred the 'raw data' from each of the reports into one (each with it's own tab) and I've got the structure of the report built. However, I'm struggling with how to make use of the 3 datasheets. My thought is to use radio-buttons to let the user select what 'point- of-view' (ie data set) they want to see ... with their selection causing the existing formulas to 'adapt' to use the right data set. So, as example: Datasheets: 'Store' - 'Food' - 'Internet' ('Store' is the default selection) Default formula: ='Store'!A1 Adapted formula: ='Food'!A1 (assuming user selects 'Food' radio button) The formulas are MUCH longer than this, so using nested formulas (ie nested-if statements) isn't really an option due to character limits in the cells. I'm thinking that some kind of Worksheet_Change code might work, but I have no idea where to start with this! Can this be done or am I wasting my time trying to merge these reports? TIA, Ray |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic formula? not sure if it's even possible to do this with Excel ...
Ray,
Maybe look at a Pivot Table ? NickHK "Ray" wrote in message ps.com... Hi - I'm attempting to 'merge' three reports into one .... basically, three of the same exact report, just with slightly different base data. At this point, I've transferred the 'raw data' from each of the reports into one (each with it's own tab) and I've got the structure of the report built. However, I'm struggling with how to make use of the 3 datasheets. My thought is to use radio-buttons to let the user select what 'point- of-view' (ie data set) they want to see ... with their selection causing the existing formulas to 'adapt' to use the right data set. So, as example: Datasheets: 'Store' - 'Food' - 'Internet' ('Store' is the default selection) Default formula: ='Store'!A1 Adapted formula: ='Food'!A1 (assuming user selects 'Food' radio button) The formulas are MUCH longer than this, so using nested formulas (ie nested-if statements) isn't really an option due to character limits in the cells. I'm thinking that some kind of Worksheet_Change code might work, but I have no idea where to start with this! Can this be done or am I wasting my time trying to merge these reports? TIA, Ray |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic formula? not sure if it's even possible to do this with Excel ...
On Apr 18, 7:07 am, "NickHK" wrote:
Ray, Maybe look at a Pivot Table ? NickHK "Ray" wrote in message ps.com... Hi - I'm attempting to 'merge' three reports into one .... basically, three of the same exact report, just with slightly different base data. At this point, I've transferred the 'raw data' from each of the reports into one (each with it's own tab) and I've got the structure of the report built. However, I'm struggling with how to make use of the 3 datasheets. My thought is to use radio-buttons to let the user select what 'point- of-view' (ie data set) they want to see ... with their selection causing the existing formulas to 'adapt' to use the right data set. So, as example: Datasheets: 'Store' - 'Food' - 'Internet' ('Store' is the default selection) Default formula: ='Store'!A1 Adapted formula: ='Food'!A1 (assuming user selects 'Food' radio button) The formulas are MUCH longer than this, so using nested formulas (ie nested-if statements) isn't really an option due to character limits in the cells. I'm thinking that some kind of Worksheet_Change code might work, but I have no idea where to start with this! Can this be done or am I wasting my time trying to merge these reports? TIA, Ray Hi NIck - I don't think a pivot will do what I need it to do ... but I'll take a look at that option ... How about a worksheet_change event (connected to the radio-buttons) that find/replaces the Sheet Name and re-calcs? How would I capture the current tab name being referenced? The initial one is easy (since I set the default), but for subsequent selections? ray |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
dynamic formula? not sure if it's even possible to do this with Excel ...
I think the easiest way is the function 'Indirect'
=INDIRECT(A1&"!B3",TRUE) If cell A1 is 'Internet' (w/o the quotes) then this will return the value in cell B3 on that sheet. To get the name in A1 you can use =CHOOSE(A2,"Store","Food","Internet") with A2 being the cell you linked to the option buttons DQ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic pivot table linked to dynamic excel spreadsheets | Excel Worksheet Functions | |||
Dynamic Formula with Dynamic Address | Excel Worksheet Functions | |||
Help with copying dynamic column selected based on remote cell value and dynamic formula fill | Charts and Charting in Excel | |||
Help with copying dynamic column selected based on remote cell value and dynamic formula fill | Excel Programming | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel |