Copy/paste value in Column based on formula in another

chrysti

Board Regular
Joined
Dec 20, 2006
Messages
218
I have a formula in column M, which returns a "N", "Y", or "S"
=IFERROR(IFERROR(IF(MATCH($H9,SW!$C$1:$C$20000,0),"S/W"),(IF(INDEX('Line Plan'!$A$1:$CA$20000,MATCH($H9,'Line Plan'!$Q$1:$Q$20000,0),MATCH(M$7,'Line Plan'!$A$4:$CA$4,0))="No","N",INDEX('Line Plan'!$A$1:$CA$20000,MATCH($H9,'Line Plan'!$Q$1:$Q$20000,0),MATCH(M$7,'Line Plan'!$A$4:$CA$4,0))))),"")

I have formulas in Columns BA-BG (BA7:BG10000) that I want to paste values in based on if Column M's formula result

I want to paste as values any cells in BA-BG that are labeled "S" in column M
At a later moment, I want to paste as values and cells in BA-BG that are labeled "N" in column M
And last same thing with "Y" at a different time

The range can also vary from 200 to 10000 rows of information

Last I would like to paste my formulas back in when my sheet needs to be reset in columns BA-BG, can I paste the formula in cell BA1 and have it copy to BA7:BA10000 based on cells that are filled with the master number in column H?

Thanks in advance for the help!
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

chrysti

Board Regular
Joined
Dec 20, 2006
Messages
218
The easier summary is I want to look at what is in column M1 and if it is a "N", then I want to paste as values the formulas in BA1:BG1, then move to the next row. Do the same thing, then move to the next row, until I have no more data, the list can be a different size so I need a loop or something that will stop when the data stops.

Thanks
 

abdulbasitb

New Member
Joined
Apr 21, 2015
Messages
17
To confirm the problem has been understood, do you want each row in BA7:BG10000 to have it's formula converted to value when the same row in column M has the value "N"?

If that's a yes, you can copy the solution you get to do the same for "Y" and "S".
If I'm not wrong, you'd run the macro for each of the three cases separately ("N", "Y" or "S") at different times.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,593
Messages
5,637,294
Members
416,963
Latest member
zazama

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
Top