Results 1 to 4 of 4

Thread: Help needed for a newbie

  1. #1

    Help needed for a newbie



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi there,

    I am trying to create a spreadsheet where for each entry (rows) I need to put some values (36 in total with numeric values from 1 to 50). For each entry I also have to choose a position (between 36 positions) and a duty (among 5 duties), now I have wrote a formula which returns a different number according to both the position and the duty that I've chosen for that entry. I need this because for every single combination of position and duty I need to "highlight" some important values. I have 66 possible combinations but for the nature of Excel I can only create a maximum of 64 nested level so I needed to divide the formula in two cells. This is the formula I've created, since I am Italian (so I apologize for my English skills) I have the Italian language so please replace SE with IF and E with AND:

    Code:
    =SE(E(D3=CC3,AO3=CE5),1,SE(E(D3=CC4,AO3=CE5),2,SE(E(D3=CC4,AO3=CE6),3,SE(E(D3=CC4,AO3=CE7),4,SE(E(D3=CC5,AO3=CE5),5,SE(E(D3=CC6,AO3=CE6),6,SE(E(D3=CC6,AO3=CE7),7,SE(E(D3=CC7,AO3=CE5),8,SE(E(D3=CC7,AO3=CE4),9,SE(E(D3=CC7,AO3=CE3),10,SE(E(D3=CC8,AO3=CE5),11,SE(E(D3=CC8,AO3=CE4),12,SE(E(D3=CC8,AO3=CE3),13,SE(E(D3=CC9,AO3=CE5),14,SE(E(D3=CC9,AO3=CE4),15,SE(E(D3=CC9,AO3=CE3),16,SE(E(D3=CC10,AO3=CE5),17,SE(E(D3=CC10,AO3=CE6),18,SE(E(D3=CC10,AO3=CE7),19,SE(E(D3=CC11,AO3=CE5),20,SE(E(D3=CC12,AO3=CE5),21,SE(E(D3=CC12,AO3=CE6),22,SE(E(D3=CC12,AO3=CE7),23,SE(E(D3=CC13,AO3=CE7),24,SE(E(D3=CC14,AO3=CE5),25,SE(E(D3=CC14,AO3=CE6),26,SE(E(D3=CC15,AO3=CE5),27,SE(E(D3=CC15,AO3=CE6),28,SE(E(D3=CC16,AO3=CE5),29,SE(E(D3=CC16,AO3=CE6),30,SE(E(D3=CC17,AO3=CE5),31,SE(E(D3=CC18,AO3=CE5),32,SE(E(D3=CC19,AO3=CE6),33,SE(E(D3=CC20,AO3=CE5),34,SE(E(D3=CC20,AO3=CE6),35,SE(E(D3=CC20,AO3=CE7),36,SE(E(D3=CC21,AO3=CE6),37,SE(E(D3=CC22,AO3=CE6),38,SE(E(D3=CC22,AO3=CE7),39,SE(E(D3=CC23,AO3=CE5),40,SE(E(D3=CC23,AO3=CE6),41,SE(E(D3=CC24,AO3=CE5),42,SE(E(D3=CC24,AO3=CE6),43,SE(E(D3=CC24,AO3=CE7),44,SE(E(D3=CC25,AO3=CE6),45,SE(E(D3=CC25,AO3=CE7),46,SE(E(D3=CC26,AO3=CE6),47,SE(E(D3=CC26,AO3=CE7),48,SE(E(D3=CC27,AO3=CE7),49,SE(E(D3=CC28,AO3=CE7),50,SE(E(D3=CC29,AO3=CE7),51,SE(E(D3=CC30,AO3=CE6),52,SE(E(D3=CC30,AO3=CE7),53,SE(E(D3=CC31,AO3=CE6),54,SE(E(D3=CC31,AO3=CE7),55,SE(E(D3=CC32,AO3=CE6),56,SE(E(D3=CC32,AO3=CE7),57,SE(E(D3=CC33,AO3=CE7),58,SE(E(D3=CC34,AO3=CE6),59,SE(E(D3=CC34,AO3=CE7),60,SE(E(D3=CC35,AO3=CE7),61,SE(E(D3=CC36,AO3=CE6),62,SE(E(D3=CC36,AO3=CE7),63,SE(E(D3=CC37,AO3=CE5),64,0))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
    As you can see what it does is checking which position and duty I had given to the entry in the row 3 (D3 position and AO3 duty) and then it returns a number according to the combination created. This formula is really basic so I think that it could be replaced by a cleaner and much easier formula. The problem with it is that if I copy the formula in a different cell, all the "cell references" change while I need that CC3to37 and CE3to7 don't change.

    Is there anybody who can help me?

    Thanks,

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    You need to just place a $ in front of the row and/or column reference in the cell reference.

    e.g. =$CC3 will "freeze" the column
    =CC$3 will "freeze" the row
    =$CC$3 will "freeze" the column and row.


  3. #3
    thanks a lot! I've found another solution but I have a new question

    now I need: if C2 = 1 then cells H2, M2, N2 and P2 must have bold and red font and green background, if C2 = 2 then H2, J2, R2 and Z2 must have bold and red font and green background and so on for values from 1 to 66. Is that only possible via the Conditional Formatting tool?

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Yes it is.


Posting Permissions

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