Using cell's contents as an index to a row ... ?

M

Mac

Hello group,

assume two worksheets, named e.g. WS1 and WS2; on WS1 I have a listbox
filled with values 1 through 10. Now, if I make a selection, I want to use
that value as an index to the corresponding row of WS2, to acces its columns.
E.g. if I select a 2, I'd be reading columns of row 2 of WS2. I hope my
explanation is not too confusing.:) Which formula should I use for this?
 
M

MyVeryOwnSelf

assume two worksheets, named e.g. WS1 and WS2; on WS1 I have a listbox
filled with values 1 through 10. Now, if I make a selection, I want to
use that value as an index to the corresponding row of WS2, to acces
its columns. E.g. if I select a 2, I'd be reading columns of row 2 of
WS2. I hope my explanation is not too confusing.:) Which formula
should I use for this?

Suppose the WS1!$A$1 is in
Format control > Control > Cell link
for the listbox.

Then
=OFFSET(WS2!$A$1,WS1!$A$1-1,COLUMN()-1)
Should return the cell in the "corresponding row" for the column containing
the formula.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top