I am using 2003 and have a summary sheet in Excel that I want to use to
display information from other sheets. I know how to create a link.

The problem is, I have 25 other sheets and in the summary I want a drop down
box (I know how to do that too) so that I can select one of the other sheet
names i.e Training Solutions, and when I select a sheet name from the drop
down box the summary sheet beneath displays the sales and cost of sales
figures from that sheet using a link.

I was going to do a nested if that says if the value in the drop down box is
'Training Solutions' then display cell B30 (sales) from the training
solutions worksheet, if the value in the drop down box is 'Hubs' then display
the cell B30 from the Hubs sheet. Problem is I have 25 sheets and I can only
nest as many as 7 (I think). Can anyone help - I'm really stuck. I know how
to do lookups but not sure that would work either.

Thanks v. much indeed

Anita

Say your choice of sheet is in B1, then

=INDIRECT(B1&"!B30")


Regards,
Stefi

€žAnita€� ezt Ã*rta:

I am using 2003 and have a summary sheet in Excel that I want to use to
display information from other sheets. I know how to create a link.

The problem is, I have 25 other sheets and in the summary I want a drop down
box (I know how to do that too) so that I can select one of the other sheet
names i.e Training Solutions, and when I select a sheet name from the drop
down box the summary sheet beneath displays the sales and cost of sales
figures from that sheet using a link.

I was going to do a nested if that says if the value in the drop down box is
'Training Solutions' then display cell B30 (sales) from the training
solutions worksheet, if the value in the drop down box is 'Hubs' then display
the cell B30 from the Hubs sheet. Problem is I have 25 sheets and I can only
nest as many as 7 (I think). Can anyone help - I'm really stuck. I know how
to do lookups but not sure that would work either.

Thanks v. much indeed

Anita

Thanks Stefi but I can't get it to work - it keeps coming back with Ref#.

The drop down list is cell B26 - in this cell I have got selected "Training
Solutions" so my formula reads =INDIRECT(B26&"!O6") O6 is the cell on the
sheet specified in B26.

I am trying to understand the formula and understand as follows: the B26 in
my case is the sheet I want Excel to look at and O6 the value to display from
that sheet. I assume you don't have to tell it what sheet as I have done
that in B26 (the drop down box) - not quite sure why the speech marks are
there however "".

Hope you can help.

Anita


"Stefi" wrote:

Say your choice of sheet is in B1, then

=INDIRECT(B1&"!B30")


Regards,
Stefi

€žAnita€� ezt Ã*rta:

I am using 2003 and have a summary sheet in Excel that I want to use to
display information from other sheets. I know how to create a link.

The problem is, I have 25 other sheets and in the summary I want a drop down
box (I know how to do that too) so that I can select one of the other sheet
names i.e Training Solutions, and when I select a sheet name from the drop
down box the summary sheet beneath displays the sales and cost of sales
figures from that sheet using a link.

I was going to do a nested if that says if the value in the drop down box is
'Training Solutions' then display cell B30 (sales) from the training
solutions worksheet, if the value in the drop down box is 'Hubs' then display
the cell B30 from the Hubs sheet. Problem is I have 25 sheets and I can only
nest as many as 7 (I think). Can anyone help - I'm really stuck. I know how
to do lookups but not sure that would work either.

Thanks v. much indeed

Anita

Sorry, that was because I simplified my test and used "training" as sheet
name. If you have a space in your sheet name, you have to use this format:

=INDIRECT("'"&B26&"'!B30")

I suggest to use sheet names like "Training_solutions", they are simpler to
use!

Regards,
Stefi


€žAnita€� ezt Ã*rta:

Thanks Stefi but I can't get it to work - it keeps coming back with Ref#.

The drop down list is cell B26 - in this cell I have got selected "Training
Solutions" so my formula reads =INDIRECT(B26&"!O6") O6 is the cell on the
sheet specified in B26.

I am trying to understand the formula and understand as follows: the B26 in
my case is the sheet I want Excel to look at and O6 the value to display from
that sheet. I assume you don't have to tell it what sheet as I have done
that in B26 (the drop down box) - not quite sure why the speech marks are
there however "".

Hope you can help.

Anita


"Stefi" wrote:

Say your choice of sheet is in B1, then

=INDIRECT(B1&"!B30")


Regards,
Stefi

€žAnita€� ezt Ã*rta:

I am using 2003 and have a summary sheet in Excel that I want to use to
display information from other sheets. I know how to create a link.

The problem is, I have 25 other sheets and in the summary I want a drop down
box (I know how to do that too) so that I can select one of the other sheet
names i.e Training Solutions, and when I select a sheet name from the drop
down box the summary sheet beneath displays the sales and cost of sales
figures from that sheet using a link.

I was going to do a nested if that says if the value in the drop down box is
'Training Solutions' then display cell B30 (sales) from the training
solutions worksheet, if the value in the drop down box is 'Hubs' then display
the cell B30 from the Hubs sheet. Problem is I have 25 sheets and I can only
nest as many as 7 (I think). Can anyone help - I'm really stuck. I know how
to do lookups but not sure that would work either.

Thanks v. much indeed

Anita

Thank you so much - you're a star !

"Stefi" wrote:

Sorry, that was because I simplified my test and used "training" as sheet
name. If you have a space in your sheet name, you have to use this format:

=INDIRECT("'"&B26&"'!B30")

I suggest to use sheet names like "Training_solutions", they are simpler to
use!

Regards,
Stefi


€žAnita€� ezt Ã*rta:

Thanks Stefi but I can't get it to work - it keeps coming back with Ref#.

The drop down list is cell B26 - in this cell I have got selected "Training
Solutions" so my formula reads =INDIRECT(B26&"!O6") O6 is the cell on the
sheet specified in B26.

I am trying to understand the formula and understand as follows: the B26 in
my case is the sheet I want Excel to look at and O6 the value to display from
that sheet. I assume you don't have to tell it what sheet as I have done
that in B26 (the drop down box) - not quite sure why the speech marks are
there however "".

Hope you can help.

Anita


"Stefi" wrote:

Say your choice of sheet is in B1, then

=INDIRECT(B1&"!B30")


Regards,
Stefi

€žAnita€� ezt Ã*rta:

I am using 2003 and have a summary sheet in Excel that I want to use to
display information from other sheets. I know how to create a link.

The problem is, I have 25 other sheets and in the summary I want a drop down
box (I know how to do that too) so that I can select one of the other sheet
names i.e Training Solutions, and when I select a sheet name from the drop
down box the summary sheet beneath displays the sales and cost of sales
figures from that sheet using a link.

I was going to do a nested if that says if the value in the drop down box is
'Training Solutions' then display cell B30 (sales) from the training
solutions worksheet, if the value in the drop down box is 'Hubs' then display
the cell B30 from the Hubs sheet. Problem is I have 25 sheets and I can only
nest as many as 7 (I think). Can anyone help - I'm really stuck. I know how
to do lookups but not sure that would work either.

Thanks v. much indeed

Anita

You are welcome! Thanks for the feedback!
Stefi


€žAnita€� ezt Ã*rta:

Thank you so much - you're a star !

"Stefi" wrote:

Sorry, that was because I simplified my test and used "training" as sheet
name. If you have a space in your sheet name, you have to use this format:

=INDIRECT("'"&B26&"'!B30")

I suggest to use sheet names like "Training_solutions", they are simpler to
use!

Regards,
Stefi


€žAnita€� ezt Ã*rta:

Thanks Stefi but I can't get it to work - it keeps coming back with Ref#.

The drop down list is cell B26 - in this cell I have got selected "Training
Solutions" so my formula reads =INDIRECT(B26&"!O6") O6 is the cell on the
sheet specified in B26.

I am trying to understand the formula and understand as follows: the B26 in
my case is the sheet I want Excel to look at and O6 the value to display from
that sheet. I assume you don't have to tell it what sheet as I have done
that in B26 (the drop down box) - not quite sure why the speech marks are
there however "".

Hope you can help.

Anita


"Stefi" wrote:

Say your choice of sheet is in B1, then

=INDIRECT(B1&"!B30")


Regards,
Stefi

€žAnita€� ezt Ã*rta:

I am using 2003 and have a summary sheet in Excel that I want to use to
display information from other sheets. I know how to create a link.

The problem is, I have 25 other sheets and in the summary I want a drop down
box (I know how to do that too) so that I can select one of the other sheet
names i.e Training Solutions, and when I select a sheet name from the drop
down box the summary sheet beneath displays the sales and cost of sales
figures from that sheet using a link.

I was going to do a nested if that says if the value in the drop down box is
'Training Solutions' then display cell B30 (sales) from the training
solutions worksheet, if the value in the drop down box is 'Hubs' then display
the cell B30 from the Hubs sheet. Problem is I have 25 sheets and I can only
nest as many as 7 (I think). Can anyone help - I'm really stuck. I know how
to do lookups but not sure that would work either.

Thanks v. much indeed

Anita