Alex has attended:
Excel Advanced course
Lookups
I have a spreadsheet with values for 43 police forces for a variety of crimes, where data is organised as one month per tab. Is there a lookup function (or macro) which I can use to obtain one figure from a particular cell to draw data from the same position for each month. The final result would be a table for the year for one force and one crime type. I hope that makes sense!!
RE: Lookups
Hi Alex
Thank you for your question. One approach would be to use the consolidate feature in Excel.
Create a blank sheet in your workbook. This will be the page that contains the summary of the monthly sheets.
Click in cell A1 in your new sheet then from the Data menu choose consolidate. Make sure the function is set to Sum then click on the button in the Reference field (to fold the dialogue box out of the way). Switch to the first monthly sheet and select the rows and columns of date INCLUDING the row labels and Column labels.
Expand the dialog box by clicking on the button in the Reference cell once more and click Add.
Repeat this process with each monthly sheet. When they have all been added select the two boxes for "Use Labels In" then click on OK. Excel will create a table that totals each of the sheets. If you want the table to update when any of the sub sheets changes then also click the "Create links to source data" check box before clicking OK.
I hope this helps - do let us know if you have any further questions.
Kind regards,
Andrew
