should i use offset?

  • Thread starter Thread starter jlm661
  • Start date Start date
J

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
 
To get the table, select a 15-row, 5-column range and array-enter your
formula.
 
i don't think i understand what you are telling me to do...can yo
explain further?

thanks!
jes
 
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.
 
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?
Click to expand...

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.
 
I dont know whether this will serve your prupose(may not be as elegant as
the solution given by others)

sample worksheet1. G2 to J8 is the table (some values) is RV table
G13 to G19 has some values and is RR table
use worsheet macro as follows
that is -in project explorer click sheet1 of the workbook
you will get sheet1 code window
choose on the left hand side <worksheet>
choose on the righthandside <change>
the code is
(the first and last line will already be there - you have to copy only from
the second line to the line above end sub)
-----------
Private Sub Worksheet_Change(ByVal Target As Range)
Dim name As String
name = Range("a1").Value
Range("g2:j8").name = "rv"
Range("g13:j19").name = "RR"
Range(name).Cells(1, 1).Activate

End Sub
---------------------
if you type RV in A1 and hit <enter> you will get the first cell of RV table
if you change A1 to RR and clik <enter> you will get the first cell of RR
table

if this is ok for you then customise it.
 
Back
Top