Data Box

[Robert Pollock]

Hi, thanks for having a look at this question.

I am trying to display data held on say sheet1, in sheet2,
in some kind of scroll box, probably about 4 rows deep.
The purpose is that as the user enters some data in
sheet2, he can refer to this scroll box to check on
previous data that other users have entered without having
to revert to looking at sheet1 itself.

I have tried, and tried to do this, but I seem to get
nowhere !

Robert

 

[Patrick Molloy]

easy enough using INDIRECT.



say you're on sheet1 and you need to see what's in sheet2

In sheet2, place a table of data in A1100

in sheet1 cell A1 put the text Sheet2
in sheet1 cell A2 put the value -12
in sheet1 cell A3 put the value -6

We will use the OFFSET function to get the value.
But first , just enter the formula, then we'll go through
it.

IN sheet1 cell F12 put the formula

=OFFSET(INDIRECT($A$1),ROW()+$A$2,COLUMN()+$A$3)

copy this right two more columns , and worn Five rows
this is our window....its the same width, 3 columns as
our table, but only 5 rows.

You'll see that the value in F12 is the same as sheet2!A1

Now make sure that Calcculation is Automatic
change A2 to say +5
You'll see the values in F12 correspond to Sheet2!A18

So A1 is the 'root' while A2 and A3 are the offsets.

changing A2 higher or lower returns a different view

I have a demo if anyone wants it...email me directyl.

Patrick Molloy
Microsoft Excel MVP