Results 1 to 8 of 8

Thread: Help with lookup values and tables.

  1. #1

    Help with lookup values and tables.



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

    Hi all,

    All help is appreciated.

    Currrently Column R contains a Y or N.
    Column S is a drop down selection from another tab
    Column T is the target of this question.

    What I want to achieve is the following:

    If R contains a Y, check S, S will contain a value from tab 3 range B2 through B13, then enter the value from tab 3 column G on the same row and the drop down selection is S.

    I hope I have explained this correctly!

    Ben

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Do you mean?

    =IF(R2="Y",VLOOKUP(S2,'Tab 3'!$B$2:$G$13,6,FALSE),"")


    change Tab 3 to actual name of tab 3.


  3. #3
    Hi, Can you explain what each part of the formula is doing so I can work it out in my head, I have changes the tab and put in into the document and it does not work, but because i do not understand the working out Im not sure what to change?

  4. #4
    It does work, I worked out what it all means and had to change the target cell.

    Many thanks

  5. #5
    Along the same lines as the above...

    I now have data spread across 6 tabs, I want to take all of that data and pit in in one place on tab 2 as a summary to save having to go through every tab. Any assistance with this one?

    Ben

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Easiest way would be to copy and paste each into tab 2 to make one long list.


  7. #7
    Hi, Its not that easy.

    The formulae above is to work out costs if a set parameter is true, so the costs appear only on those rows where the parameter is met. I have 6 tabs where this occurs. What I now need is for the rows where the there is data to be moved to a new tab to summarise, if I just map the cells then there would be lots of blank rows in between the data and this would not work. I need a way to say, look here (Tab 3 Cells R4:U300), if there is data present move to tab 2 next free cell after D4, then look here (Tab 4 cells R4:U300), if there is data present move to tab 2 next free cell after D4. Repeat until all relevant tabs are covered.

    Ben

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    That'll probably require VBA code. I'll pass it to someone willing to take that up.


Posting Permissions

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