Looking for some spreadsheet help please... (1 Viewer)

[Galileo]

I have several formulas that I wish to copy down their respective columns.  Here is an example...

=INDEX('Team Selection'!$A$3:$A$66,MATCH(6,'Team Selection'!K$3:K$66,0))

I would like the formula to stay identical except for the bolded column reference, K.  For each subsequent row, I want that to change to L, M, N, etc...   Is there a way to accomplish this?  The formula currently copies with the K for every entry and I would have to manually change it.

 

[Ilov80s]

I have several formulas that I wish to copy down their respective columns.  Here is an example...

=INDEX('Team Selection'!$A$3:$A$66,MATCH(6,'Team Selection'!K$3:K$66,0))

I would like the formula to stay identical except for the bolded column reference, K.  For each subsequent row, I want that to change to L, M, N, etc...   Is there a way to accomplish this?  The formula currently copies with the K for every entry and I would have to manually change it.

If you have the formula for K in the K Column, you should be able to click on the cell and drag in the direction you want the formula pasted into. It will adjust the formula to match the new column. 

 

[higgins]

Mmmm….spreadsheets 

 

[bushdocda]

I have several formulas that I wish to copy down their respective columns.  Here is an example...

=INDEX('Team Selection'!$A$3:$A$66,MATCH(6,'Team Selection'!K$3:K$66,0))

I would like the formula to stay identical except for the bolded column reference, K.  For each subsequent row, I want that to change to L, M, N, etc...   Is there a way to accomplish this?  The formula currently copies with the K for every entry and I would have to manually change it.

Can you get it to work by dragging formula going across columns by letting the column reference unlocked then copy/paste special transpose the formulas down a single column?

 

[Galileo]

If you have the formula for K in the K Column, you should be able to click on the cell and drag in the direction you want the formula pasted into. It will adjust the formula to match the new column. 

There is no formula for K.  K is a column on a different sheet.  I am searching that column for a particular value and retrieving a piece of data from another column in the same row as the value my search found.  I then want to tell the next row of my working sheet to do the search for column L and so on...

 

[JaxBill]

might be a question for @Ned

he set up this thread

 

[Galileo]

Can you get it to work by dragging formula going across columns by letting the column reference unlocked then copy/paste special transpose the formulas down a single column?

Hmmmm...This sort of worked, but seemed to start in a bit of a random place.  I may be able to tweak this, but I probably have to dedicate another sheet to do all these in rows first.

 

[dkp993]

If you have the formula for K in the K Column, you should be able to click on the cell and drag in the direction you want the formula pasted into. It will adjust the formula to match the new column. 

The $ symbol in formulas locks the columns or cell as a consistent so when dragging to copy it will hold.  If you remove the $ symbol after the K it will allow for the new column to replace K when you copy/drag the formula.  

 

[Ilov80s]

There is no formula for K.  K is a column on a different sheet.  I am searching that column for a particular value and retrieving a piece of data from another column in the same row as the value my search found.  I then want to tell the next row of my working sheet to do the search for column L and so on…

Yeah the formula you are using for column K. Have you tried the thing where you slide it and over it? Hard for me to know for sure without seeing it but have you tried that? It may change the formula to align with each column as you drag it over. I also is this Excel or Sheets?

 

[Brony]

You can use the OFFSET function to accomplish this, but it would be a lot easier if your spreadsheet was set up to get the results in columns instead of rows. 

Specifically, put offset in front of MATCH(6,'Team Selection'!K$3:K$66,0) and have another column with sequential 1,2 3 etc to specify the number of columns to analyze. 

 

[Galileo]

The $ symbol in formulas locks the columns or cell as a consistent so when dragging to copy it will hold.  If you remove the $ symbol after the K it will allow for the new column to replace K when you copy/drag the formula.  

The $ is there to lock the rows.  The rows will change without it.  I want to keep the row references.

 

[Galileo]

You can use the OFFSET function to accomplish this, but it would be a lot easier if your spreadsheet was set up to get the results in columns instead of rows. 

Specifically, put offset in front of MATCH(6,'Team Selection'!K$3:K$66,0) and have another column with sequential 1,2 3 etc to specify the number of columns to analyze. 

I tried playing around with the OFFSET earlier, but couldn't ever seem to get it right.  Admittedly, I am a novice with regard to most of the functions available.  I am not sure what the bolded is referring to.

 

[Runkle]

There is no formula for K.  K is a column on a different sheet.  I am searching that column for a particular value and retrieving a piece of data from another column in the same row as the value my search found.  I then want to tell the next row of my working sheet to do the search for column L and so on...

Because the K in the formula does not have a $ in front of it, it is a relative reference based on what cell the formula is currently in. So, for example, if you're putting this formula in a cell in column "P", Excel is treating the "K" as a reference that means "5 columns to the left".

So if you copy/paste one to the right, in column "Q", it'll look five to the left in column "L". If you copy/paste it into "R", it'll always look five left to "M". And so on. It's all relative to the original formula's placement and reference. 

 

[Galileo]

Because the K in the formula does not have a $ in front of it, it is a relative reference based on what cell the formula is currently in. So, for example, if you're putting this formula in a cell in column "P", Excel is treating the "K" as a reference that means "5 columns to the left".

So if you copy/paste one to the right, in column "Q", it'll look five to the left in column "L". If you copy/paste it into "R", it'll always look five left to "M". And so on. It's all relative to the original formula's placement and reference. 

Right.   This is what I wanted, but this is not what it was doing though.  It was not changing the K at all.  It kept referencing K no matter what, even without the $.

Oh well, I ended up using brute force on this damn thing.  It is not very elegant, but it is working as intended now.  It won't be easy to make changes later though should I want to.  

 

[Runkle]

Right.   This is what I wanted, but this is not what it was doing though.  It was not changing the K at all.  It kept referencing K no matter what, even without the $.

Oh well, I ended up using brute force on this damn thing.  It is not very elegant, but it is working as intended now.  It won't be easy to make changes later though should I want to.  

Were you copying the cell, then putting the cursor in another cell, and hitting CTRL-V to paste? Or were you just pasting text into the formula bar?

 

[Galileo]

Were you copying the cell, then putting the cursor in another cell, and hitting CTRL-P to paste? Or were you just pasting text into the formula bar?

CTRL-C, CTRL-V...clicking the corner of blue box and dragging...tried several ways.  The row numbers would change if I removed the $, but the column would not.  

 

[ChiefD]

Are you guys speaking Dungeons and Dragons here?

 

[acarey50]

CTRL-C, CTRL-V...clicking the corner of blue box and dragging...tried several ways.  The row numbers would change if I removed the $, but the column would not.  

The column reference won't change if copying the formula down a row. As @Runklesaid, it's a relative reference, so if this formula is entered in column P, and then copied down column P, all of them will reference column K.

This is the closest I could find to do what it sounds like you are trying to do

 

[Ned]

I have several formulas that I wish to copy down their respective columns.  Here is an example...

=INDEX('Team Selection'!$A$3:$A$66,MATCH(6,'Team Selection'!K$3:K$66,0))

I would like the formula to stay identical except for the bolded column reference, K.  For each subsequent row, I want that to change to L, M, N, etc...   Is there a way to accomplish this?  The formula currently copies with the K for every entry and I would have to manually change it.

You can hack your way into this by using INDIRECT and ROW.  

=INDEX('Team Selection'!$A$3:$A$66,MATCH(6,INDIRECT("'Team Selection'!R3C"&ROW()&":R66C"&ROW(),FALSE),0),0)

The only drawback to this approach is it relies on the row you've placed the formula.  So if you put this in cell D19, it's going to think you need column 19 from the Team Selection tab.  You can tweak this by adding/subtracting.  

Example: assume you put this in D19, but you wanted column K (11th column) to be matched.  You'll need to subtract 8 to get to 11.  Then you can drag the formula down as far as you want and it'll adjust based on the rows your formula in.  (D20 = 12th column, D21 = 13th, D22 = 14, etc)

=INDEX('Team Selection'!$A$3:$A$66,MATCH(6,INDIRECT("'Team Selection'!R3C"&ROW()-8&":R66C"&ROW()-8,FALSE),0),0)

 

[Fat Nick]

I have several formulas that I wish to copy down their respective columns.  Here is an example...

=INDEX('Team Selection'!$A$3:$A$66,MATCH(6,'Team Selection'!K$3:K$66,0))

I would like the formula to stay identical except for the bolded column reference, K.  For each subsequent row, I want that to change to L, M, N, etc...   Is there a way to accomplish this?  The formula currently copies with the K for every entry and I would have to manually change it.

I think the issue is you want the K to change (Columns) but you want the result to be in rows, right?

Try this - Drag the above ACROSS so the K changes with each column you drag it across.  Then highlight the resulting cells, and do a find/replace on [Selection'!] and make it [Selection'!$] and then another on [$3:] and make it [$3:$].  This will force the result to be absolute references.  Then Copy and Paste Special Transpose it into the row you want and it will keep the references.