+ Reply to Thread
Results 1 to 17 of 17

Cross Section in same sheet formula

  1. #1
    Registered User
    Join Date
    11-10-2014
    Location
    Malaysia
    MS-Off Ver
    2010
    Posts
    7

    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.
    Attached Images Attached Images

  2. #2
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    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?
    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

  3. #3
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    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.
    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.

  4. #4
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    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?

  5. #5
    Registered User
    Join Date
    11-10-2014
    Location
    Malaysia
    MS-Off Ver
    2010
    Posts
    7

    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.
    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.

  6. #6
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    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
    Attached Files Attached Files
    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

  7. #7
    Registered User
    Join Date
    11-10-2014
    Location
    Malaysia
    MS-Off Ver
    2010
    Posts
    7

    Re: Cross Section in same sheet formula

    Thanks ChemistB, Richard, and especially martindwilson~ ; )

  8. #8
    Registered User
    Join Date
    11-10-2014
    Location
    Malaysia
    MS-Off Ver
    2010
    Posts
    7

    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.

  9. #9
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    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.

  10. #10
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Cross Section in same sheet formula

    You cant unless you go to vba
    Last edited by martindwilson; 11-11-2014 at 09:41 AM.

  11. #11
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    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
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    11-10-2014
    Location
    Malaysia
    MS-Off Ver
    2010
    Posts
    7

    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.
    Last edited by AViolet07; 11-11-2014 at 10:25 AM.

  13. #13
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    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.

  14. #14
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    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?

  15. #15
    Registered User
    Join Date
    11-10-2014
    Location
    Malaysia
    MS-Off Ver
    2010
    Posts
    7

    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)

  16. #16
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,577

    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.

  17. #17
    Registered User
    Join Date
    11-10-2014
    Location
    Malaysia
    MS-Off Ver
    2010
    Posts
    7

    Re: Cross Section in same sheet formula

    Thanks ChemistB~ This works perfectly~

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to use INDEX formula to output a cross section of data
    By Pope_003 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-09-2014, 12:02 AM
  2. convert data from cross section to panel format
    By diquez in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-21-2012, 12:58 PM
  3. Make panel structure data from cross section
    By jhound in forum Excel General
    Replies: 5
    Last Post: 11-11-2011, 12:36 AM
  4. How do I create a cross section between two columns?
    By Yasko in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-26-2006, 11:15 AM
  5. Calculate areas from a cross section chart??
    By nated7777 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 02-18-2006, 11:00 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1