Lock a formula.

[tip32a]

I am doing an if statement comparing Column B to column D down 100 rows. This I can do in column E =IF(B2=D2, "yes", "No"). Easy. Then I drag it down the other 99 rows and just the row number updates in each formula. EASY. Now I need to shift down data in column a Lets say I need to move B7 through B10 down 4 rows. Then replace the now missing data in B7 to B10. The issue is the formula changed and tracked the moving of the cells in A. so the formula in E10 now reads =IF(B14=D10, "yes", "No") I need it to stay B10 not move to B14. I do know I can go to EVERY E Row and Add $. I need a simpler way. Because in fact I have over 1000 rows not 100....

So stated another way I need to "Lock" the formula in E so no matter what I B or D the formula only acts on the row originally stated.
Thanks...

 

[steve the fish]

Theres no way to do that in your formula in E by using $. Thats not how they work. If you are moving cells then formulas are going to compensate for that regardless of $ signs. There is a way of doing what you want but really dont move cells about. Thats not good design and will only lead to error.

 

[tip32a]

Hi Steve the Fish,
You answered "there is a way of doing that" Then you didn't give that way.
Then you stated that's not good design and will lead to error but you didn't elaborate on that either. Nor do you know what the design is or the intended outcome.

If you would like to share the There is a way answer it would be greatly appreciated.

 

[steve the fish]

I deliberately didnt because really grabbing cells and shifting them around on a formula driven spreadsheet is a bad idea. If you are going to do that you should then drag your formulas down so they refresh. Its a road to ruin though believe me you are going to create mistakes which you may or may not see. But that said index and row will only give you what is in the current row eg =INDEX(B:B,ROW())

 

[tip32a]

Hi Steve the Fish,
If there truly is no way to do this I can accept this. But your solution doesn't take into account that not all the rows will move. And I have to do it this way. In a nutshell I have to lineup the info in column B with the info in D The info comes from different sources and will be placed in a single spread sheet. Column D is complete and B and D are arranged in numerical order smallest to largest. D will lets say have 200 records. but b only has 70. The range of the numbers in both b and d is 5 digits. and there is a lot of overlap not not perfect overlay. So I need to move the items in B down until the first match with D then move b down some sore until the nest b matches the next D. and so on.

1​	1​	Yes
2​	2​	Yes
4​	3​	no
6​	4​	no
8​	5​	no
9​	6​	no
10​	7​	no
15​	8​	no
17​	9​	no
18​	0​	no
19​	10​	no
20​	11​	no
	12​	no
	13​	no
	14​	no
	15​	no
	16​	no
	17​	no
	18​	no
	19​	no
	20​	no

 

[steve the fish]

What I gave you will produce whatever is in column b of the row where the formula is housed. Isn't that what you asked for? Drag it wherever you like it will still produce the whatever is in the row where its housed. What are you trying to do with these two lists of numbers?

 

[tip32a]

To answer What am I trying to do. Lets say they are 5 digit serial numbers. The right list is complete.. May not be 99,999 long but is a complete data set. The left lets say the serial numbers that were inventoried. So there will be consecutive ranges but there will be gaps. But the data will be provided in a simple list format.
So I need to place the 2 lists next to each other and add gaps where the left column doesn't match the right column. there will never be an instance where the right column doesn't have a matching entry to the left. but the other is possible and highly likely. the goal of the formula was to be a quick way of finding where a sequence of 30 to 100 or so no longer match without having to eyeball and compare every set. It will also act as a verification that only true pairs are found.

 

[steve the fish]

Then you can use a vlookup in column E. I'm doing this without testing:

=IFERROR(VLOOKUP(D1,B:B,1,0),"")

Drag down to end of data in column D. It will then produce whatever is in D where its present in B. That work for you?

 

[tip32a]

Thanks Steve the Fish,
I don't have time to learn a new function at the moment and VLOOKUP does not appear to be what I am looking for. I will only do this once. ever. I was hoping to learn something new but I have paid for support and spent hours with no answer and thought I would try here. I believe the 0 in your formula is supposed to be a true or false and maybe o and 1 represent true and false. I will write a small python script to compare 2 CSv versions and be done with it.
Anyway thanks for the effort. If anyone thinks they have a workable solution I am still open.

 

[steve the fish]

There's is very little I couldn't do on a spreadsheet. The problem here is you haven't explained yourself very well. I'm not sure what you need to learn when I've given you a formula to try. Doesnt sound like you bothered. Anyway good luck.