dynamic formula? not sure if it's even possible to do this with Excel ...

[Ray]

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

 

[NickHK]

Ray,
Maybe look at a Pivot Table ?

NickHK

 

[Ray]

Ray,
Maybe look at a Pivot Table ?

NickHK

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

 

[dq]

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