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.
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.
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?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
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.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
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.
Last edited by AViolet07; 11-10-2014 at 09:41 PM.
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
Last edited by martindwilson; 11-10-2014 at 09:56 PM.
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Thanks ChemistB, Richard, and especially martindwilson~ ; )
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.
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
Last edited by martindwilson; 11-11-2014 at 09:41 AM.
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.
4. Close VBA Editor. Save workbook as xls or xlsm file.Please Login or Register to view this content.
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.
Last edited by AViolet07; 11-11-2014 at 10:25 AM.
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)
Does that work better?Please Login or Register to view this content.
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)
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.
Thanks ChemistB~ This works perfectly~
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks