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?

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.

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?

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.
2nd cell in Quadrant 3,1=2nd column in Quad 3,0

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.

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

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.

You cant unless you go to vba

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

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.

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.

Enter this new code (clear the old)

Please Login or Register  to view this content.

Does that work better?

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.