# Cross Section in same sheet formula

1. ## Cross Section in same sheet formula

Hi, I'm new in Excel, may I ask how to write formula for:

Same sheet, cross section

e.g.

if B1 is 1, A2 is 0 (vice versa)
if C1 is 1, A3 is 0 (vice versa),
ifC2 is 1, B3 is 0 (vice versa) etc.

cross section throughout.

Is for tournament usage.  Register To Reply

2. ## Re: Cross Section in same sheet formula

You can't have the "visa versa" with regular formulas. A cell needs to either have a formula or be manually entered. This can be done with VBA (macros or programming). Are you okay with that?  Register To Reply

3. ## Re: Cross Section in same sheet formula

Hi, and welcome to the forum.

It's not clear exactly what you're trying to do. Please upload your workbook and manually add the data and formatting you start with and in another area the resulst and formatting that you want to achieve along with notes that explain how you get from one to the other.  Register To Reply

4. ## Re: Cross Section in same sheet formula

Yes, I agree with Richard. I would expect each of your four quadrants to be square but in your picture they are not. I see where A2 would be reflected in B1, however, what about A11 (2nd cell in Quadrant 3) would it be reflected in B10 (2nd column in Quad 3) or in K1 in Quad 2?  Register To Reply

5. ## Re: Cross Section in same sheet formula

ChemistB: I'm not programmer, but I know how to put in =IF function. Just that it's hard to make it cross-opposite with only 1 formula input.

Richard: that's the picture I captured, it wrongly cut, but actually it's a square.

EXAMPLE.png

the pictures goes on up to 60. It'll be trouble some to write out the IF formula line by line. Maybe there are ways that I don't know?

Example formula: =IF(A2=1,0,IF(A2=0,1,)) <----> this is written on B1
I can drag this throughout the entire row, but can't for the column. And this formula got various problems also.  Register To Reply

6. ## Re: Cross Section in same sheet formula

in b1
=IF(INDEX(\$A:\$BH,COLUMN(),ROW())="","",ABS(INDEX(\$A:\$BH,COLUMN(),ROW())-1))
fill across to bh
then select c1 fill down to c2 then across
then d2 fill down to d3 then across and so on  Register To Reply

7. ## Re: Cross Section in same sheet formula

Thanks ChemistB, Richard, and especially martindwilson~ ; )  Register To Reply

8. ## Re: Cross Section in same sheet formula

OKAY. Now the next problem.

EXAMPLE.jpg

=IF(INDEX(\$A:\$BH,COLUMN(),ROW())="","",ABS(INDEX(\$A:\$BH,COLUMN(),ROW())-1)) <-----> formula by martindwilson

Position: When editing the Lower Quadrant (either 1 or 0), the Upper Quadrant value changes accordingly, which is perfect. But ....

Question: How can make it so that, Upper Quadrant can be edit (either 1 or 0) and the Lower Quadrant will also change.

Problem: Upper Quadrant has formula in it, so whenever a 1 or 0 being key-in the formula will gone.

Thanks for helping.  Register To Reply

9. ## Re: Cross Section in same sheet formula

Please don't upload pictures - see recommendation in the rules and post #1.

Almost always we prefer the actual workbook so that we have something to work with. None of us like having to recreate your data just to get to first base.  Register To Reply

10. ## Re: Cross Section in same sheet formula

You cant unless you go to vba  Register To Reply

11. ## Re: Cross Section in same sheet formula

Assuming your square is in A1:J10,
1. Remove the formulas. As both martin and I said, it can't be done with formulas.
2. Right click on the sheet tab and "View Code". The VBA editor will pop up.
3. In the large white text box, paste this code. ``Please Login or Register  to view this content.``
4. Close VBA Editor. Save workbook as xls or xlsm file.
See example attachment  Register To Reply

12. ## Re: Cross Section in same sheet formula

Sorry Richard. Me new here, and will post my work here in the future (can't find the attachment button, but now got it).

Thanks martin & ChemistB.

My worksheet starts on H3:BO62, so when copy the code it didn't work. So sorry for all the trouble.

*And regarding ChemistB (thanks a lot) post, me test it and it works great.

But one question: Because it's registered as 1 or 0, so let say me made mistake and want to delete my input, it'll be read as 0 although nothing is there, and the opposite quadrant will show 1. How to correct this? Thanks.  Register To Reply

13. ## Re: Cross Section in same sheet formula

In the code, you'll find where it has the range (A1:Q17) just change that to H3:BO62

The code is set up such that it only pays attention if you are changing a single cell. One way is to pick both cells (hold down the cntrl key and click on both) then clear them. Otherwise the mirror will remain. If you will have multiple users, that probably won't work. I can try to modify the code.  Register To Reply

14. ## Re: Cross Section in same sheet formula

Enter this new code (clear the old) ``Please Login or Register  to view this content.``
Does that work better?  Register To Reply

15. ## Re: Cross Section in same sheet formula

Tried with my table:

Expected: When 1 input into I3, H4 suppose to appear 0.

Results: When 1 input into I3, instead, C9 appear 0.

(I'll be idle for 2 days, will come back on 14th Nov. to continue, thanks for the help guys)  Register To Reply

16. ## Re: Cross Section in same sheet formula

Sorry, I didn't take into account the offset of the initial row and column. This code should work for you and will allow you to easily clear errors. You can also use Data Validation to only allow 0's and 1's. ``Please Login or Register  to view this content.``  Register To Reply

17. ## Re: Cross Section in same sheet formula

Thanks ChemistB~ This works perfectly~  Register To Reply