Populating Cells

clappouk

Board Regular
Joined
Sep 2, 2010
Messages
54
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)
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

ZAX

Well-known Member
Joined
Jul 5, 2012
Messages
715
Here, enter this in D9 and auto fill down to D20:
Code:
=INDEX(Sheet5!$B$2:$I$13,ROW()-8,MATCH($A$2,Sheet5!$B$1:$I$1,0))
ZAX
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,855
Messages
5,833,998
Members
430,250
Latest member
Reggie Mcqueen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top