I have three sheets..
Data
GRID
Report..
Based on the values entered in the DATA sheet , the grid reflects the grade..
for a ll the store numbers..
Now I want to have an individual Site Report...
I am making use of Vlookup to extract the grades from GRID sheet, however I want to get the scores from DATA for competitor presence...
Have attached the file for further understanding with appropriate comments ..
The sumproduct function is working fine in the Grid as the store numbers are in the same order as in the main Data, however i want to extract or retrieve individual store numbers in the report sheet.. the sumproduct should now compare the values based in which row the correpsonding store is ...No wthis can be possible mAtch function...
The formula Im using in the Grid -t o provide the Grade is as follows..
[ VLOOKUP(SUMPRODUCT((Data!$E2:$I2="Yes")*{26,8,8,4,4}),{1,"E";8,"D";15,"C";22,"B";29,"A"},2))) ]
I want to have this portion coloured in red to simply give the scores as I can get the grade from the Grid by using Vlookup...
The formula Im looking to use would be as follows-
[ SUMPRODUCT((INDIRECT(("'Data!$E"&MATCH($B$2,Data!$B:$B,0)&":$I"&MATCH($B$2,Data!$B:$B,0))="Yes"))*{26,8,8,4,4}) ]
Bookmarks