CODEWITHSUNDEEP
excelexcel-formula
platinums
platinums

Reputation: 673

Referencing values in rows, base on column

i need a formula to reference 3 different rows, depending on which column i paste into. What i know is the width is constant, 34 columns across

i want to paste into columns A1, L1, W1 I want to retrieve the values for cells, AK1,AK2,AK3

im using this at the moment to grab parts of the text in that cell. eg

="<tag" &MID($AK3,FIND(" ",$AK3),FIND(" ",$AK3,FIND("1",$AK3))-FIND(" ",$AK3))&" endtext>"

my idea was when i paste into column 22 i need something to return the number 2, pass that to an offset formula on AK1, as this is 2 thirds the way across, then i can reference 2 rows down from AK1 and get the value.

Hope that makes sense.

Here is an example. Cells A1 C1 E1 are retrieved from the H column

enter image description here

Upvotes: 0

Views: 93

Answers (1)

Terry W
Terry W

Reputation: 3257

Presume you have named the three values sitting in Column AK as Rng_AK,

when you paste this formula =INDEX(Rng_AK,ROUNDUP(COLUMN()/34*ROWS(Rng_AK),0))

  • anywhere between Column A to K, you will get the first value in the given range;
  • anywhere between Column L to V, you will get the second value in the given range;
  • anywhere between Column W to AH, you will get the third value in the given range;

I am using your logic of locating the value in Column AK by the relative position of the column in the 34-Columns range.

See below screen-shot for more clarifications. Cheers :)

Solution

Upvotes: 1

Related Questions