Lookup or index and match

[lstevenson]

Hi
I am setting up a spreadsheet, that will have multiple sheets for each branch, this will than be referencing back to another sheet for data. As i will be replicating the sheets I want to make the formulas as easy as possible so I dont have to change them in every sheet. I have attached an example, the data sheet will have a table for each month, the remaining sheets will be each branch, ideally I would like to have a formula that looks up the data sheet and returns a value for each month and then be able to replicate the formula across each sheet. As the formula is looking at 3 different areas (date, branch and mortgage balance) i have tried using idex and match, but are not having any success, so any help would be appreciated!

[FDibbins]

To begin with, it will help greatly if you can keep all the headings the same (as well as the sheet names matching the branch names in column B)

I will play around and see what I can come up with

[FDibbins]

OK will you always have the same amount of branches, and will they always be in that same order? I can work around if they are not, but it will be much easier if they are

[gak67]

You could try something like this in SC!B5:

Please Login or Register  to view this content.

But you would need to change A1 of the branch sheets to match the data in B3:B17 of the data sheet, eg SC!A1 should read "1.SC KPI Summary Results", or change column B of the data tab to not include the numbers, eg Data!B3 = SC

You would need to change the bold red 5 to match the column of data you want in lower rows.

[FDibbins]

This will work if you diont change the sheet names to match, but you will still need to change the column headings to match. All of these can be copied as-is from 1 sheet to the next.

1. Use this for the headings
A1=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)&" KPI Summary Results"

2. Use this to pull in teh values...
=INDEX(Data!$C:$N,MATCH("*"&LEFT($A$1,FIND(" ",$A$1,1)-1),Data!$B:$B,0)+MATCH(B$4,Data!$A:$A,0)-1,MATCH($A5,Data!$C$2:$N$2,0))