Need Help!Extracting data

jaspal

New member
Joined
Jul 12, 2013
Messages
10
Reaction score
0
Points
0
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
 

Attachments

  • MASTER CASH FLOW - help.xls
    433 KB · Views: 19
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.
 
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
 

Attachments

  • MASTER CASH FLOW - Excel Help3.xls
    314 KB · Views: 17
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...
 
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
 
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)),"")))
 
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! :laugh:
 
Thanks NBVC- you've been a great help
 
Back
Top