1

I want a function to select every 2nd column on a particular row and display it in another sheet.

I have two sheets: Sheet1 and Sheet2

In Sheet1 A2 I have a list of numbers that start from 1 to 1000.

In Sheet1 A3 I have links that I want to select/display in Sheet2.

I want to choose what link to display in Sheet2 A2 by writing a function in Sheet2 A1 so that when I write "1" I get the link on the same row as page 1.

See example: Image (GIF)

And I want to do this without using macros if possible.

Someone suggested a function like this:

=OFFSET(Members profiles!$A$2,(ROW(B2)-1)*0,2)

I played with the numbers a bit but if this could be changed to help with my problem it would be great.

Improve this question
2
  • Is the requirement for your formula to be in cell B1 a rigid requirement? It's relatively easy to use a formula in cell B2 to pull in the data you require from Sheet1 if the you don't have to have the formula in B1.
    – panhandel
    May 18, 2015 at 18:33
  • I'm trying to build an interactive sheet where you enter the number of the page and you get the link. This is why I have the info (page number, link) in a separate sheet.
    – Dur
    May 18, 2015 at 18:40

1 Answer 1

Reset to default
1

Let's see if either of these styles will get what you're looking for. I am a big fan of the INDEX(MATCH()) combo to find a value, but return back to me an associated value to that found value like you're needing (find the page number, but send back the link).

I have Sheet1 set up like you did:
enter image description here

And then I have two styles set up on Sheet2. Columns A & B would be what I suspect you will eventually move to, and columns D & E are what your sample was set up like.

Style A:

=INDEX(Sheet1!$B$1:$B$5,MATCH($A2,Sheet1!$A$1:$A$5,0))

You could copy this formula down the column and it will reference the static ranges from Sheet1, but look up the value from column A for each different row you copy the formula to.

enter image description here

Style B:

=INDEX(Sheet1!B1:B5,MATCH(E1,Sheet1!A1:A5,0))

This style will simply grab the link for a single value that you enter in cell E1.

enter image description here

Reference info here - http://www.contextures.com/xlFunctions03.html

EDIT: From comments; and I hope I understand the follow-up question correctly, but you can use the result of one of the Sheet2 formulas to concatenate stuff to the URL result, like the following example of adding "/index.htm" to one of them.

enter image description here

Improve this answer
3
  • It works! Thanks for the simple solution. ;) Question: Can you run formulas from different sheets? (not simply showing the formula as a string/static text) So like saying =Sheet1!A1 where in that cell you have a formula (=concatenate(something&something else). In this case, instead of getting "somethingsomething else" I get =concatenate(something&something else
    – Dur
    May 18, 2015 at 20:03
  • @Duraid I don't see why not, though referencing a cell with a formula in it isn't necessarily running it, you do get the value out of whatever the formula results in. I'm going to put in an edit with a screenshot from my example Sheet1 where I concat "/index.htm" to the result of one of the formula cells on Sheet2
    – panhandel
    May 18, 2015 at 21:06
  • I posted the question here in more detail: superuser.com/questions/916386/run-formula-from-different-sheet Thanks for the answer but it's not quite what I meant
    – Dur
    May 18, 2015 at 21:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .