{"text_format":"1","workbook":"xlsx","n_sheets":2,"sheets":["Sheet1","Sheet2"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":3,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Sheet1","index":0,"n_rows":20,"n_cols":6,"rows_kept":20,"cut":false,"hidden":false,"merged":[]}
["Location","Name","team","marks"]
[{"$f":"IF(B2<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,3,0),\"\")","v":"UK"},"neha",{"$f":"IF(B:B<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,2,0),\"\")","v":"z"},{"$f":"IF(B:B<>\"\",SUMIFS(Sheet2!D:D,Sheet2!A:A,Sheet1!B2),\"\")","v":85}]
[{"$f":"IF(B3<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,3,0),\"\")","v":"Aus"},"rahul",{"$f":"IF(B:B<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,2,0),\"\")","v":"d"},{"$f":"IF(B:B<>\"\",SUMIFS(Sheet2!D:D,Sheet2!A:A,Sheet1!B3),\"\")","v":288}]
[{"$f":"IF(B4<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,3,0),\"\")","v":"US"},"mohit",{"$f":"IF(B:B<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,2,0),\"\")","v":"y"},{"$f":"IF(B:B<>\"\",SUMIFS(Sheet2!D:D,Sheet2!A:A,Sheet1!B4),\"\")","v":175},null,"Range is created through Name manager. Please have a look."]
[{"$f":"IF(B5<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,3,0),\"\")","v":"India"},"ankit",{"$f":"IF(B:B<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,2,0),\"\")","v":"x"},{"$f":"IF(B:B<>\"\",SUMIFS(Sheet2!D:D,Sheet2!A:A,Sheet1!B5),\"\")","v":248}]
[{"$f":"IF(B6<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,3,0),\"\")","v":"SL"},"puja",{"$f":"IF(B:B<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,2,0),\"\")","v":"q"},{"$f":"IF(B:B<>\"\",SUMIFS(Sheet2!D:D,Sheet2!A:A,Sheet1!B6),\"\")","v":180}]
[{"$f":"IF(B7<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,3,0),\"\")","v":""},null,{"$f":"IF(B:B<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,2,0),\"\")","v":""},{"$f":"IF(B:B<>\"\",SUMIFS(Sheet2!D:D,Sheet2!A:A,Sheet1!B7),\"\")","v":""}]
[{"$f":"IF(B8<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,3,0),\"\")","v":""},null,{"$f":"IF(B:B<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,2,0),\"\")","v":""},{"$f":"IF(B:B<>\"\",SUMIFS(Sheet2!D:D,Sheet2!A:A,Sheet1!B8),\"\")","v":""}]
[{"$f":"IF(B9<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,3,0),\"\")","v":""},null,{"$f":"IF(B:B<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,2,0),\"\")","v":""},{"$f":"IF(B:B<>\"\",SUMIFS(Sheet2!D:D,Sheet2!A:A,Sheet1!B9),\"\")","v":""}]
[{"$f":"IF(B10<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,3,0),\"\")","v":""},null,{"$f":"IF(B:B<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,2,0),\"\")","v":""},{"$f":"IF(B:B<>\"\",SUMIFS(Sheet2!D:D,Sheet2!A:A,Sheet1!B10),\"\")","v":""},null,"Yellow highlighted cells have formulas "]
[{"$f":"IF(B11<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,3,0),\"\")","v":""},null,{"$f":"IF(B:B<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,2,0),\"\")","v":""},{"$f":"IF(B:B<>\"\",SUMIFS(Sheet2!D:D,Sheet2!A:A,Sheet1!B11),\"\")","v":""}]
[{"$f":"IF(B12<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,3,0),\"\")","v":""},null,{"$f":"IF(B:B<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,2,0),\"\")","v":""},{"$f":"IF(B:B<>\"\",SUMIFS(Sheet2!D:D,Sheet2!A:A,Sheet1!B12),\"\")","v":""},"Problem - While selecting range through macro, it also select the cells a7:d20."]
[{"$f":"IF(B13<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,3,0),\"\")","v":""},null,{"$f":"IF(B:B<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,2,0),\"\")","v":""},{"$f":"IF(B:B<>\"\",SUMIFS(Sheet2!D:D,Sheet2!A:A,Sheet1!B13),\"\")","v":""}]
[{"$f":"IF(B14<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,3,0),\"\")","v":""},null,{"$f":"IF(B:B<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,2,0),\"\")","v":""},{"$f":"IF(B:B<>\"\",SUMIFS(Sheet2!D:D,Sheet2!A:A,Sheet1!B14),\"\")","v":""},"Solution needed:-"]
[{"$f":"IF(B15<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,3,0),\"\")","v":""},null,{"$f":"IF(B:B<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,2,0),\"\")","v":""},{"$f":"IF(B:B<>\"\",SUMIFS(Sheet2!D:D,Sheet2!A:A,Sheet1!B15),\"\")","v":""}]
[{"$f":"IF(B16<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,3,0),\"\")","v":""},null,{"$f":"IF(B:B<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,2,0),\"\")","v":""},{"$f":"IF(B:B<>\"\",SUMIFS(Sheet2!D:D,Sheet2!A:A,Sheet1!B16),\"\")","v":""},"I have to sort the data on basis of highes marks location wise.for this I have to create a range that ignored formula blank cells"]
[{"$f":"IF(B17<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,3,0),\"\")","v":""},null,{"$f":"IF(B:B<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,2,0),\"\")","v":""},{"$f":"IF(B:B<>\"\",SUMIFS(Sheet2!D:D,Sheet2!A:A,Sheet1!B17),\"\")","v":""}," I want that it only selects the data up to row 6 i.e upto the data is displayed."]
[{"$f":"IF(B18<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,3,0),\"\")","v":""},null,{"$f":"IF(B:B<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,2,0),\"\")","v":""},{"$f":"IF(B:B<>\"\",SUMIFS(Sheet2!D:D,Sheet2!A:A,Sheet1!B18),\"\")","v":""}]
[{"$f":"IF(B19<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,3,0),\"\")","v":""},null,{"$f":"IF(B:B<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,2,0),\"\")","v":""},{"$f":"IF(B:B<>\"\",SUMIFS(Sheet2!D:D,Sheet2!A:A,Sheet1!B19),\"\")","v":""}]
[{"$f":"IF(B20<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,3,0),\"\")","v":""},null,{"$f":"IF(B:B<>\"\",VLOOKUP($B:$B,Sheet2!$A:$C,2,0),\"\")","v":""},{"$f":"IF(B:B<>\"\",SUMIFS(Sheet2!D:D,Sheet2!A:A,Sheet1!B20),\"\")","v":""}]

{"sheet":"Sheet2","index":1,"n_rows":22,"n_cols":4,"rows_kept":22,"cut":false,"hidden":false,"merged":[]}
["name","team","location","units"]
["neha","z","UK",10]
["rahul","d","Aus",50]
["mohit","y","US",30]
["ankit","x","India",20]
["puja","q","SL",40]
["rahul","d","Aus",90]
["mohit","y","US",10]
["ankit","x","India",20]
["puja","q","SL",30]
["neha","z","UK",50]
["rahul","d","Aus",80]
["mohit","y","US",90]
["ankit","x","India",68]
["puja","q","SL",96]
["rahul","d","Aus",32]
["mohit","y","US",45]
["ankit","x","India",75]
["ankit","x","India",65]
["puja","q","SL",14]
["neha","z","UK",25]
["rahul","d","Aus",36]