3D function with dynamic reference

S

smaruzzi

I need to create a 3d function whouse argument is dynamically created by
assempling elements from different cells. I've been able to generate the
string as I wish according to different variable that might change over time.

When I pass that argument to the function I get an error. Is it because it
is a 3D string or because I do something wrong?

Thanks, Stefano
 
S

smaruzzi

This is what I would love to get to: =MIN(JP:IT!$E30).
The cell reference - E30 - chnages dynamically and could be other
coordinates such as F31 or D31. The following two examples illustrate how
thew formula could change:

=MIN(JP:IT!$F31)
=MIN(JP:IT!$D31)


Therefore the function argument is static in the first part - the reference
to multiple sheets - but changes in the cell reference.

Currently I have solved it by nesting multiple IF statements, but i would
rather prefer to have a more readable formula, and acapable of generating any
kind of range according to the inputs present in other cells.

Thanks, Stefano
 
S

smaruzzi

The formula I would like to generate dynamically looks like this:

=MIN(JP:IT!$E30)

I don't have to change the sheet reference (JP:IT!), but focus exclusively
on the cell reference (E30). this value could change according to values in
other cells used as inputs. Therefore the formula could look like the
following two examples:

=MIN(JP:IT!$D31)
=MIN(JP:IT!$F32)

Currently I have solved this issue by nesting multiple IF statements, a
solution not particularly smart, and not flexible enough. I f I could
dynamically create the 3D formula refence on the fly it would definitely
better and a clearer approach.

Thanks, Stefano
 
S

smaruzzi

The formula I would like to generate dynamically looks like this:

=MIN(JP:IT!$E30)

I don't have to change the sheet reference (JP:IT!), but focus exclusively
on the cell reference (E30). this value could change according to values in
other cells used as inputs. Therefore the formula could look like the
following two examples:

=MIN(JP:IT!$D31)
=MIN(JP:IT!$F32)

Currently I have solved this issue by nesting multiple IF statements, a
solution not particularly smart, and not flexible enough. I f I could
dynamically create the 3D formula refence on the fly it would definitely
better and a clearer approach.

Thanks, Stefano
 
S

smaruzzi

The formula I would like to generate dynamically looks like this:

=MIN(JP:IT!$E30)

I don't have to change the sheet reference (JP:IT!), but focus exclusively
on the cell reference (E30). this value could change according to values in
other cells used as inputs. Therefore the formula could look like the
following two examples:

=MIN(JP:IT!$D31)
=MIN(JP:IT!$F32)

Currently I have solved this issue by nesting multiple IF statements, a
solution not particularly smart, and not flexible enough. I f I could
dynamically create the 3D formula refence on the fly it would definitely
better and a clearer approach.

Thanks, Stefano
 
S

smaruzzi

The formula I would like to generate dynamically looks like this:

=MIN(JP:IT!$E30)

I don't have to change the sheet reference (JP:IT!), but focus exclusively
on the cell reference (E30). this value could change according to values in
other cells used as inputs. Therefore the formula could look like the
following two examples:

=MIN(JP:IT!$D31)
=MIN(JP:IT!$F32)

Currently I have solved this issue by nesting multiple IF statements, a
solution not particularly smart, and not flexible enough. I f I could
dynamically create the 3D formula refence on the fly it would definitely
better and a clearer approach.

Thanks, Stefano
 
S

smaruzzi

The formula looks like this:

=MIN(JP:IT!$E30)

The portion that I intend to change dynamically is the cell reference.
Alternative variations could look like these:

=MIN(JP:IT!$E31)

or

=MIN(JP:IT!$F32)

I have solved this issue by nesting multiple IF statements, a solution that
is not flexible enough, and quite cumbersome.

Thanks, Stefano
 
S

smaruzzi

The formula looks like this:

=MIN(JP:IT!$E30)

The portion that I intend to change dynamically is the cell reference.
Alternative variations could look like these:

=MIN(JP:IT!$E31)

or

=MIN(JP:IT!$F32)

I have solved this issue by nesting multiple IF statements, a solution that
is not flexible enough, and quite cumbersome.

Thanks, Stefano
 
S

smaruzzi

I should add that the cell reference is formed by analyzing two other cells
from where the column and row reference is derived extracting some
information from a text string.

Thanaks, Stefano
 
J

JNW

What is JP?
--
JNW


smaruzzi said:
I should add that the cell reference is formed by analyzing two other cells
from where the column and row reference is derived extracting some
information from a text string.

Thanaks, Stefano
Click to expand...
 
S

smaruzzi

JP and IT being the first and the last sheets from which I want to extract a
cell value.

Thanks, Stefano
 
Top