Results 1 to 8 of 8

Thread: Need Help!Extracting data

  1. #1

    Need Help!Extracting data



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

    I have been trying to work this out for hours but cant seem to solve the problem.

    I am trying to extract data from 7 different tables (Rent, Fit out, Dilaps etc) into one consolidated table (AC474:CK676)
    The idea is that it brings in the information for each separate tenant- in this case there are 12.

    The problem I seem to be having is that from Line 585 on wards it still seems to be picking up data in the Rent line even though it should be empty

    Can anybody be kind enough to spot my error- Thanks
    Attached Files Attached Files

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    It is due to the formula looking for the last value in column AB, that will always be the 12 until the grey cells in column AC for the section is filled in.

    What I would suggest is to update the formula in AB477 to:

    =IF(AC477="",0,INDIRECT("Ab"&22+FLOOR(ROWS(AB$477:$AC477)/9,1)))

    this will return a 0 if there is no data in the corresponding grey cells. If you don't want to show the 0, then format the cells as Custom with Type: 0;-0;;

    Then formula in AD479 becomes:

    =IF(AD$475>=$AD$8,0,IF(LOOKUP(10^10,$AB$477:$AB479)=0,"",IFERROR(INDEX($AE$70:$AP$348,MATCH($AC479,$AB$70:$AB$348,0)+MATCH(INDEX($AC$477:$AC479,MATCH(10^10,$AB$477:$AB479)+1),$AD$22:$AD$43,0)+2,MATCH(AD$475,$AE$61:$AP$61,0)),"")))

    copied across and down as done before.


  3. #3
    HI NBVC

    Thanks a lot for that:

    It nearly works exactly how I want it too

    I seem to have a problem now where by if I change the end date of my spreadsheet beyond Dec 2016 (using AD8) say for example to Dec 2017 then the data in these cells is blank (so Dec-16,mar 17,jun,17,sep 17 will all be blank) upto DEC- 16 it works fine

    do you have any idea why it would do this?

    I have attached a spreadsheet that just shows the rent line (otherwise its too big)

    If you are able to help - Thank a lot
    Attached Files Attached Files

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    I don't understand. The tables for the items other than Rent are missing above, so you will get blanks in the other cells.

    In the first sheet you sent, after changing the date (and using my new formulas), I get info in those cells AD479:AD485...


  5. #5
    HI

    Sorry i dont think I explained myself properly.

    Ive deleted all the other info out just so that the spreadsheet was small enough to attach -so i left the rent line in to show the problem i am having.

    (second spreadsheet)
    If I pick any date (cell AD8) up-till Dec 16 then all the information gets picked up in line 479 (Rent) -However

    If I pick a date beyond this (in Cell AD8) Say 25 Dec 17, then when you look at line 479 (rent) you can see that AP479 - AS479 are blank and dont pick up any info.

    So your formula does exactly what i am after, except if i change the end date beyond DEC 16

    Thanks again if you are able to help me with this

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    You ranges in the formula in AD479 cover only upto column AP in the above tables. You need to change them to cover to AW

    =IF(AD$475>=$AD$8,0,IF(LOOKUP(10^10,$AB$477:$AB479)=0,"",IFERROR(INDEX($AE$70:$AW$348,MATCH($AC479,$AB$70:$AB$348,0)+MATCH(INDEX($AC$477:$AC479,MATCH(10^10,$AB$477:$AB479)+1),$AD$22:$AD$43,0)+2,MATCH(AD$475,$AE$61:$AW$61,0)),"")))


  7. #7
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by NBVC View Post
    You ranges in the formula in AD479 cover only upto column AP in the above tables. You need to change them to cover to AW

    =IF(AD$475>=$AD$8,0,IF(LOOKUP(10^10,$AB$477:$AB479)=0,"",IFERROR(INDEX($AE$70:$AW$348,MATCH($AC479,$AB$70:$AB$348,0)+MATCH(INDEX($AC$477:$AC479,MATCH(10^10,$AB$477:$AB479)+1),$AD$22:$AD$43,0)+2,MATCH(AD$475,$AE$61:$AW$61,0)),"")))
    Beat me to it you devil!

  8. #8
    Thanks NBVC- you've been a great help

Posting Permissions

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