+ Reply to Thread
Results 1 to 18 of 18

Check and Output Cell(s) Above/Below Input

  1. #1
    Registered User
    Join Date
    10-25-2014
    Location
    USA
    MS-Off Ver
    2013
    Posts
    28

    Check and Output Cell(s) Above/Below Input

    Hello all,

    I was working on a spreadsheet and got stumped on this problem. I have a list of processes that I labeled either main or iteration. Each process had been assigned a specific value.

    I used INDEX/MATCH to lookup a input value and determine whether it is "main" or "iteration"

    Now I am trying to output the value(s) of "iteration" for that "main" and vise versa in the named process. I was using IF(C3="main", C3, ... I think I need offset after this but I am unsure how to apply it. If the input value is a main, it should output all "iteration" values until it reaches the next "main" I would appreciate any help or advice. Thank you!

    Ray
    Attached Files Attached Files
    Last edited by Ray Storm; 06-12-2015 at 12:40 PM.

  2. #2
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Check and Output Cell(s) Above/Below Input

    Your profile says Office 2013 and you've included an *.xls file. Is backwards compatibility to be considered?

  3. #3
    Registered User
    Join Date
    10-25-2014
    Location
    USA
    MS-Off Ver
    2013
    Posts
    28

    Re: Check and Output Cell(s) Above/Below Input

    I do not need backwards compatibility; I attached the *.xls file so that no one has an issue opening the attachment. Thank you.

    I am still having trouble using the functions in excel to accomplish this.
    I would have ...

    If (
    C3 = "Main" , C3
    If ( Offset ((INDEX MATCH function), -1, 0) = "Main" , ( Offset ((INDEX MATCH function), -1, 0)
    If ...

    Am I on the right track here?

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Check and Output Cell(s) Above/Below Input

    If I understand correctly you wish to key on C3 and C5 to determine where to start and end the range subset(s) of numbers in Data column D. Is that correct?

    There is a column of numbers Data column B (1-5) that might be useful for outputting based upon "Main". Will that pattern remain consistent?

  5. #5
    Registered User
    Join Date
    10-25-2014
    Location
    USA
    MS-Off Ver
    2013
    Posts
    28

    Re: Check and Output Cell(s) Above/Below Input

    The pattern is consistent. Are you thinking of detecting the blank spaces? I had never thought of that!

    The input for C3 is what I use to determine whether the value is a "main" or "iteration" category (C5). I then want to output the "iteration" values if C3 is a main. ( I think this can be done with the " " detect that you hinted at! )

    If the value is a "iteration" (C5 = "iteration"), I think the formula needs to detect the blank space on either side? and then take the first "main" that appears above it. (still confused on how to do this) Thank you.

  6. #6
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Check and Output Cell(s) Above/Below Input

    Actually I hadn't thought of using blank spaces.....but I am now. Everything else has been a dead end.

    I have been trying to use those numbers in column B for setting start/end points for the Iterations. That hasn't panned out.

    So far this one has me stymied.

    Time to call in the virtual Cavalry.

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Check and Output Cell(s) Above/Below Input

    This is what I have so far.

    Some explanations:

    I've put an extra number (6) in Data column B to create a "stop" point for "Iteration" when C3 is "Main".

    The yellow highlighted numbers in column M of Formula is my "cheat reference" for where "Mains" begin. So far that is working in E8:E19. The formula will be a nightmare to debug, and is incomplete.

    What I need now is instructions for what you want done when C3 generates "Iteration" in C5. The initial upload provided a clear idea of what you wanted for "Main" in C5.
    If you would upload an equally clear example(s) of what you expect if C5 is "Iteration" it might be helpful. For now I just have a "place-holder" for that in my formula.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    BTW since it's the weekend there may be a wait for other help.
    Attached Files Attached Files
    Last edited by FlameRetired; 06-12-2015 at 09:53 PM.

  8. #8
    Registered User
    Join Date
    10-25-2014
    Location
    USA
    MS-Off Ver
    2013
    Posts
    28

    Re: Check and Output Cell(s) Above/Below Input

    Thank you for all the help thus far! I truly appreciate it. I tried to study your formula but it is definitely a nightmare!

    If C5 generates "iteration" I had wanted it to fill in the corresponding "main" and the other "iteration"(s) under it if applicable. I spent some time seeing if I could detect the blank space and this is what I have arrived at.

    I used the cell function to determine the address of the C3 value in data. I then offset it so that it shows the column that displays 1, 2, 3 etc. I had thought that I could search for the next non-blank cell with reference to the F5 cell. I had tried vlookup ("*",...) along with another method but neither seem to work. I thought that if I could obtain the range of cells I could output the cells in between. Any thoughts? Thank you for the help and I hope everyone's weekend is going well.
    Attached Files Attached Files

  9. #9
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Check and Output Cell(s) Above/Below Input

    I had a chance to sleep on this one. This one is much simpler.

    I haven't looked at your latest uploads, yet.


    Yes I know about "Iterations" showing up when C3 generates Iteration in C5.
    That is a place holder until you can provide some clear examples of desired output as you did with "Main". Until then I am in the dark about what to do in the next step.

    In the meantime I'll look at your latest upload.
    Attached Files Attached Files

  10. #10
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Check and Output Cell(s) Above/Below Input

    I created formula in column B to either mark "Main" with ascending number or last ending row (number 6)
    In E8 should be:

    Please Login or Register  to view this content.
    drag down.

    Hope it helps.
    Attached Files Attached Files
    Quang PT

  11. #11
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Check and Output Cell(s) Above/Below Input

    @ Ray Storm

    I went ahead and took a guess on what you want when C3 generates "Iteration" in C5. This replaces the second "choice" in the CHOOSE function with additional formula. The repeating "Iterations" are gone.
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    10-25-2014
    Location
    USA
    MS-Off Ver
    2013
    Posts
    28

    Re: Check and Output Cell(s) Above/Below Input

    Sorry for being unclear as well as the delayed reply. I have attached a more clear example of what I had meant when the value is an "iteration"

    Bebo's formula/help actually worked for the example I had posted. I tried my best to learn from it and I understood the majority of it
    When I tried to apply this formula to my data, however, I keep getting some error. I think it is due to my misunderstanding of the Row function. I had read up on how it is used but I can't seem to completely troubleshoot.

    Let's say my desired range is output: 117-121

    When I start with Row(2:2), it draws one cell too early and ends one cell too early. (output: 116 to 120)
    When I start with Row(3:3), it starts correctly, but still ends one cell too early. (output: 117 to 120) Any thoughts on what I might have done wrong? I am relatively sure my error is in this Row function.

    Thank you!
    Attached Files Attached Files

  13. #13
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Check and Output Cell(s) Above/Below Input

    Are you able to add columns to the data tab in the real file? If you can does this work for you?Example (2).xlsx
    Last edited by gak67; 06-15-2015 at 09:19 PM.
    Please add reputation by clicking on the * if I have helped.
    Please mark the thread SOLVED if your issue has been resolved.
    Thanks, Glenn.

  14. #14
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Check and Output Cell(s) Above/Below Input

    Thanks for the feedback. and rep!

    I'll look at the expected output of the most recent upload.
    Dave

  15. #15
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Check and Output Cell(s) Above/Below Input

    Try it again
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    10-25-2014
    Location
    USA
    MS-Off Ver
    2013
    Posts
    28

    Re: Check and Output Cell(s) Above/Below Input

    Thank you so much bebo, Flame, and gak! I found the fault in my function; I had merged two rows together in the title, and that caused the problem.

    Have a good week everyone.

  17. #17
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Check and Output Cell(s) Above/Below Input

    Nice to hear it works. Good luck!

  18. #18
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Check and Output Cell(s) Above/Below Input

    Glad to hear it works!

+ 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. [SOLVED] Check and Output Values of Cell(s) Above / Below given.
    By Ray Storm in forum Outlook Formatting & Functions
    Replies: 1
    Last Post: 06-12-2015, 12:36 PM
  2. Replies: 7
    Last Post: 04-15-2015, 09:32 AM
  3. [SOLVED] Output cell values in column to list with comma
    By vonrainer in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 06-30-2014, 10:32 AM
  4. Search cell text and output different values based on result in another cell
    By tbarn1980 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-14-2013, 09:54 PM
  5. Need macro to loop through input cell values and copy output value
    By jaf in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-27-2011, 02:59 PM

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