should i use offset?

[jlm661]

Hello everyone! I have a question that might seem quite simple to som
but i can't seem to figure it out...

on a my worksheet (150#) i have a bunch of tables that correspond to
pressure of 150 psi....on my main worksheet (main) i want to displa
the desired worksheet based on the value inserted into a cell.

for example on my main sheet in C2 if you enter the value "Gate" i wan
to show the table which corresponds to the gate, and if you enter "Flow
i want to enter the 150# flow table...here is what i am trying to us
but it isn't working

=IF(C2="Gate", OFFSET('[Labor.xls]150#'!$A$1:$E$15, 0, 0, 15, 5)
IF(C2="Flow", OFFSET('[Labor.xls]150#'!$G$1:$K$15, 0, 0, 15, 5),"Ente
value in C2"))

this is returning a value of 5, when what i want is the complet
table...any suggestions? should i not be using offset? if not, wha
should i use?

THANKS!
jes

 

[JE McGimpsey]

To get the table, select a 15-row, 5-column range and array-enter your
formula.

 

[jlm661]

i don't think i understand what you are telling me to do...can yo
explain further?

thanks!
jes

 

[JE McGimpsey]

For Selection, see "Select text, cells, ranges, rows, and columns" in XL
Help.

For entering an array formula, see "About array formulas and array
constants" and "Enter an array formula" in XL Help.

 

[Jonathan Rynd]

for example on my main sheet in C2 if you enter the value "Gate" i
want to show the table which corresponds to the gate, and if you enter
"Flow" i want to enter the 150# flow table...here is what i am trying
to use but it isn't working

=IF(C2="Gate", OFFSET('[Labor.xls]150#'!$A$1:$E$15, 0, 0, 15, 5),
IF(C2="Flow", OFFSET('[Labor.xls]150#'!$G$1:$K$15, 0, 0, 15, 5),"Enter
value in C2"))

this is returning a value of 5, when what i want is the complete
table...any suggestions? should i not be using offset? if not, what
should i use?

You need to enter the formula as an array formula.

1. Select a range of cells.
2. Enter the formula into any cell in the range but don't press enter.
3. Press control-shift-enter.

But it would be easier on you if you just defined the range
'[Labor.xls]150#'!$A$1:$E$15 to have the name "Gate", and the range
'[Labor.xls]150#'!$G$1:$K$15 to have the name "Flow". Then you could
use =indirect(C2) as your formula.