Formulae querry

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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?
 
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.
 
Thank you very much it worked!

Jim May said:
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
Click to expand...
 
Back
Top