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)
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,402
Messages
5,601,475
Members
414,452
Latest member
Dannysamworth

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