INDIRECT?
 

Hi,
I have a 20 sheets of data relating to differing regions. Each worksheet
holds the same layout and formulas. I want to have one front sheet that
will allow a user to select a region from a combo box and have the chosen
regional data displayed on that sheet. i.e. bypassing the need for a user to
flick through various worksheets.

Would it be correct to use the INDIRECT & MATCH functions for this, if so
can someone point me towards a website giving a good explanation and
example?

Thanks,
Mickey

INDIRECT?
 

One way (although not all that "slick"):

Fill your front page with formulas that each simply pull in the contents
from one cell from the regional sheets. The formulas would look like this,
using a variable for the sheet name:

=INDIRECT("'"&$B$1&"'!A6")

B1 is the cell where the user selects a region from a drop-down list -
assuming that the worksheets are named after those regions. A6 is the cell
that this particular formula is pulling in. The cell below it needs to
reference A7, the one to the right of it needs to reference B6, etc. Note
that the text pieces of the formula are enclosed in double quotes, so the
first piece is double quote-single quote-double quote, and the second one is
double quote-single quote-exclamation point-cell reference-double quote.

The main catch with this is you have to edit all the formulas by hand to get
the right cell references. You can't "copy down" the formula because the
reference part - cell A6 in the example - does not update. But hopefully
that's not a show-stopper, just takes more time to develop.

HTH

"Mickey" wrote:

Hi,
I have a 20 sheets of data relating to differing regions. Each worksheet
holds the same layout and formulas. I want to have one front sheet that
will allow a user to select a region from a combo box and have the chosen
regional data displayed on that sheet. i.e. bypassing the need for a user to
flick through various worksheets.

Would it be correct to use the INDIRECT & MATCH functions for this, if so
can someone point me towards a website giving a good explanation and
example?

Thanks,
Mickey

INDIRECT?
 

Hi andy62,
That's a great help and many thanks. I don't mind the manual formula
editing as by the end of it I should be able to remember how to do it!.

Thanks again,
Mickey



"andy62" wrote in message
...
One way (although not all that "slick"):

Fill your front page with formulas that each simply pull in the contents
from one cell from the regional sheets. The formulas would look like
this,
using a variable for the sheet name:

=INDIRECT("'"&$B$1&"'!A6")

B1 is the cell where the user selects a region from a drop-down list -
assuming that the worksheets are named after those regions. A6 is the
cell
that this particular formula is pulling in. The cell below it needs to
reference A7, the one to the right of it needs to reference B6, etc. Note
that the text pieces of the formula are enclosed in double quotes, so the
first piece is double quote-single quote-double quote, and the second one
is
double quote-single quote-exclamation point-cell reference-double quote.

The main catch with this is you have to edit all the formulas by hand to
get
the right cell references. You can't "copy down" the formula because the
reference part - cell A6 in the example - does not update. But hopefully
that's not a show-stopper, just takes more time to develop.

HTH

"Mickey" wrote:

Hi,
I have a 20 sheets of data relating to differing regions. Each worksheet
holds the same layout and formulas. I want to have one front sheet that
will allow a user to select a region from a combo box and have the chosen
regional data displayed on that sheet. i.e. bypassing the need for a user
to
flick through various worksheets.

Would it be correct to use the INDIRECT & MATCH functions for this, if so
can someone point me towards a website giving a good explanation and
example?

Thanks,
Mickey