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.

  • 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 '15 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. – Duraid May 18 '15 at 18:40
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

  • 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 – Duraid May 18 '15 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 '15 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 – Duraid May 18 '15 at 21:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.