I have this workbook with multiple spreadsheets that I use as data input
sheets.
What I want is to have a separate spreadsheet that it will calculate some
formulae for me which are standard for each of the input sheets. My problem
is that I want to specify in a cell in this worksheet, the name of the input
sheet that I want it to use. In other words I want the range in the formulae
in this single spreadsheet to be automatically adjusted based on the value I
will give in a cell.

Is this possible?

Let me ask if I understand correctly:
You have formulas on a sheet (Sheet1) that refer to other sheets, but which
of the other sheets depends on which of those sheets you entered a value into
last?

Example:
Initially you have a formula on Sheet1:
='Sheet2'!A5 * 10
but if you enter a value on Sheet3, cell A5, you wish this formula to change
to:
='Sheet3'!A5 * 10

is that your need? If so, it could be done with VBA code attached to
Sheet2 and Sheet3 and similar sheet's _Change() event.

We need more details describing what is to happen in various cells when you
make changes to others.

"Yiannos" wrote:

I have this workbook with multiple spreadsheets that I use as data input
sheets.
What I want is to have a separate spreadsheet that it will calculate some
formulae for me which are standard for each of the input sheets. My problem
is that I want to specify in a cell in this worksheet, the name of the input
sheet that I want it to use. In other words I want the range in the formulae
in this single spreadsheet to be automatically adjusted based on the value I
will give in a cell.

Is this possible?

The below formula in your lead sheet references:
Where
A4 = "MySheet3" << without the quotes
and Where
In B4 (all sheets) = some number, say 10 on Mysheet1, 20 on Mysheet2, 30 on
Mysheet3
somewhere enter:
=INDIRECT("'"& A4 &"'!B4")*1.5 << should display 45 (30*1.5)

Write back if problems...

Jim May



"Yiannos" wrote:

I have this workbook with multiple spreadsheets that I use as data input
sheets.
What I want is to have a separate spreadsheet that it will calculate some
formulae for me which are standard for each of the input sheets. My problem
is that I want to specify in a cell in this worksheet, the name of the input
sheet that I want it to use. In other words I want the range in the formulae
in this single spreadsheet to be automatically adjusted based on the value I
will give in a cell.

Is this possible?

Let me ask my question otherwise:

I have a sheet that it uses the sumif function with a range from another
source sheet (there are many source sheets, but my formulae use one source
sheet at a time).

I could have copied the formulas to the next column and change the source
sheet each time, but it would create a mess...

What I want is to have some kind of proforma in a sheet...the formulae in
this proforma will need to change with my command that it will be inserted in
a cell.

assume cell A1 in the summary sheet (containing the proforma) is my command
cell: I just want to type in the name of the source sheet and then all the
formulae in the proforma to be automatically changed to use the source sheet
I have input in cell A1.

Is it possible?

"JLatham" wrote:

Let me ask if I understand correctly:
You have formulas on a sheet (Sheet1) that refer to other sheets, but which
of the other sheets depends on which of those sheets you entered a value into
last?

Example:
Initially you have a formula on Sheet1:
='Sheet2'!A5 * 10
but if you enter a value on Sheet3, cell A5, you wish this formula to change
to:
='Sheet3'!A5 * 10

is that your need? If so, it could be done with VBA code attached to
Sheet2 and Sheet3 and similar sheet's _Change() event.

We need more details describing what is to happen in various cells when you
make changes to others.

"Yiannos" wrote:

I have this workbook with multiple spreadsheets that I use as data input
sheets.
What I want is to have a separate spreadsheet that it will calculate some
formulae for me which are standard for each of the input sheets. My problem
is that I want to specify in a cell in this worksheet, the name of the input
sheet that I want it to use. In other words I want the range in the formulae
in this single spreadsheet to be automatically adjusted based on the value I
will give in a cell.

Is this possible?

I believe that Jim May has a solution that will work for you. If it does
not, respond to him and perhaps between he and I, we can get something that
will work for you.

"Yiannos" wrote:

Let me ask my question otherwise:

I have a sheet that it uses the sumif function with a range from another
source sheet (there are many source sheets, but my formulae use one source
sheet at a time).

I could have copied the formulas to the next column and change the source
sheet each time, but it would create a mess...

What I want is to have some kind of proforma in a sheet...the formulae in
this proforma will need to change with my command that it will be inserted in
a cell.

assume cell A1 in the summary sheet (containing the proforma) is my command
cell: I just want to type in the name of the source sheet and then all the
formulae in the proforma to be automatically changed to use the source sheet
I have input in cell A1.

Is it possible?

"JLatham" wrote:

Let me ask if I understand correctly:
You have formulas on a sheet (Sheet1) that refer to other sheets, but which
of the other sheets depends on which of those sheets you entered a value into
last?

Example:
Initially you have a formula on Sheet1:
='Sheet2'!A5 * 10
but if you enter a value on Sheet3, cell A5, you wish this formula to change
to:
='Sheet3'!A5 * 10

is that your need? If so, it could be done with VBA code attached to
Sheet2 and Sheet3 and similar sheet's _Change() event.

We need more details describing what is to happen in various cells when you
make changes to others.

"Yiannos" wrote:

I have this workbook with multiple spreadsheets that I use as data input
sheets.
What I want is to have a separate spreadsheet that it will calculate some
formulae for me which are standard for each of the input sheets. My problem
is that I want to specify in a cell in this worksheet, the name of the input
sheet that I want it to use. In other words I want the range in the formulae
in this single spreadsheet to be automatically adjusted based on the value I
will give in a cell.

Is this possible?

Thank you very much it worked!

"Jim May" wrote:

The below formula in your lead sheet references:
Where
A4 = "MySheet3" << without the quotes
and Where
In B4 (all sheets) = some number, say 10 on Mysheet1, 20 on Mysheet2, 30 on
Mysheet3
somewhere enter:
=INDIRECT("'"& A4 &"'!B4")*1.5 << should display 45 (30*1.5)

Write back if problems...

Jim May



"Yiannos" wrote:

I have this workbook with multiple spreadsheets that I use as data input
sheets.
What I want is to have a separate spreadsheet that it will calculate some
formulae for me which are standard for each of the input sheets. My problem
is that I want to specify in a cell in this worksheet, the name of the input
sheet that I want it to use. In other words I want the range in the formulae
in this single spreadsheet to be automatically adjusted based on the value I
will give in a cell.

Is this possible?