Increment Column in formula when copying/pasting down a row?

Nordicrx8

Board Regular
Joined
Dec 10, 2015
Messages
143
Office Version
  1. 365
Platform
  1. Windows
Hi all!

I'm hoping someone has a simple solution that can save me a lot of time here. I have a formula that I need to copy and paste down 200 ROWS, and need the below COLUMNS to increment. (In bold)

=IF(INDEX(Main!$R:$R,MATCH(1,(Material!$B$1=Main!$A:$A)*(Material!B$4=Main!$C:$C),))=0,"",INDEX(Main!$R:$R,MATCH(1,(Material!$B$1=Main!$A:$A)*(Material!B$4=Main!$C:$C),)))

I also need to copy this formula across 10 columns, which is why they are surrounded by $'s. Without the $'s, when I copy the formula down, the columns do not increment, requiring me to manually update them.

I did a google search, and it seems like the column function [might] help here, but I'm not entirely sure. If anyone could provide some guidance, I'd greatly appreciate it!

Thanks all!
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
Hi Fluff! Good call - Updated! :) I'm running 365 on Windows
 
Upvote 0
Thanks for that, how about
Excel Formula:
=LET(f,INDEX(FILTER(Main!R:Z,(Main!A:A=$B$1)*(Main!C:C=B$4),""),ROWS(B$5:B5)),IF(f="","",f))
change the B$5:B5 to reflect the cell the formula 1st goes in
 
Upvote 0
Thanks for that, how about
Excel Formula:
=LET(f,INDEX(FILTER(Main!R:Z,(Main!A:A=$B$1)*(Main!C:C=B$4),""),ROWS(B$5:B5)),IF(f="","",f))
change the B$5:B5 to reflect the cell the formula 1st goes in
Good Morning! I wasn't able to get this one to work, even with some fiddling. But I don't fully understand what the LET, F, and FILTER functions are doing, so I I'm not sure if my edits were correct. It's not returning an error, but it's also not returning data.
 
Upvote 0
What edits did you make?
 
Upvote 0
What edits did you make?
I bolded edits below. I wasn't sure if the Main!R:GG was a search range for data, so I expanded it to encompass the range I needed it to look in. Other than that, I locked in col C, and updated B5 as the starting formula cell.

=LET(f,INDEX(FILTER(Main!R:GG,(Main!A:A=$B$1)*(Main!$C:$C=B$4),""),ROWS(B5)),IF(f="","",f))
 
Upvote 0
The rows needs to be like I showed it.
 
Upvote 0
The rows needs to be like I showed it.
Ah ok! I got it! It's working perfectly now - thank you so much! Here's the final formula.

=LET(f,INDEX(FILTER(Main!$R:$GZ,(Main!$A:$A=$B$1)*(Main!$C:$C=B$4),""),ROWS(BC$5:BC5)),IF(f="","",f))

If you have time, could you break it down by section and explain how it works? Always trying to build on my knowledge!
 
Upvote 0
The filter function is doing the same as your original formula, but it returns all the data from col R to GZ, then index then pulls data from that, but for 1 col which is decided by the Rows function & finally the If statement at the end is simply returning "" instead of 0 if there is nothing to return.
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top