Populating Cells

[clappouk]

Afternoon All,

I am trying to create an expenses report for my boss. I have created the Data Spreadsheet and the Summary Page for the calculations.

The problem I have is that I have 8 Cost Centres and do not want to make 8 summary pages.

On my Summary Page in Cell B2 I have used Validation to create a dropdown of Cost Centres.

In Cells D9:20 I need to populate the monthly Budget amounts for each Cost Centre. These are held on Sheet2 in ranges B2:B13, C2:C13, D2:D13 etc

Is there a way that when I select a CC from the dropdown in B2 the Range will populate to the appropriate Cost Center amounts from teh ranges on sheet2. So whent I select CC 1 cells D9:D20 will have the data from B2:B13, CC 2 Cells D9:D20 will have range C2:C13. I thought the choose function may help, but I do not now how to convert the CC into an index number.

hope I have provided enough information

Thanks
Ray (excel 2003)

 

[ZAX]

Here, enter this in D9 and auto fill down to D20:

=INDEX(Sheet5!$B$2:$I$13,ROW()-8,MATCH($A$2,Sheet5!$B$1:$I$1,0))

ZAX