Budget plate Auto Compilation

        Currently general method used to create budget plate is "Copy & Paste", which is time consuming and multiple people intervention,  some of them having no idea about those works. For example, Circle/Division office personnel (Project Officers) don't know anything about "T-6" or "Bhagwan Birsa Munda Yojna" work. In such cases considering quantum of works received in Excel files, duplication of works is common. Also, Project Officers are not conversant with the names of villages as that of field Sectional officers, so names which Section officers think that accidentally given wrong in politicians letters and needs to be changed becomes a difficult task and most often sent as it is. Similar thing is observed with missing Chainages, Wrong Chainages etc.

        The best way to counter this problem is one time entry system in which only that person enters the data who knows best (here field person) and rest changes happens automatically, this reduces human intervention, time, errors and most importantly rectification of errors is possible. The best thing is that it is possible with Free, Simple and Familiar tool like Google sheets which is almost similar to Microsoft Excel which we are currently using for preparing budget plate with some advance features.

Image :- Compiled data from all sheets

Here are the steps for a circle office to do things the way mentioned above.

        1) Open Google Sheets

        2) Rename Google sheet with Name of Head e.g. T-6, 04, 03 etc.

        3) Create New sheet for each subdivision, with similar columns as per required format

        4) Now each subdivision will enter proposed works data as per desired format.

        5) Use following formula with desired modifications in master sheet (Circle sheet) in which you want to compile data.

=QUERY({'D1'!A7:T;'D2'!A7:T;'T1'!A7:T;'T2'!A7:T;Peth!A7:T},"select * where Col4 is not null or Col8 is not null or Col1 is not null",0)

        6) In above picture 1 can see data from D1, D2, T1, T2, Peth

Explanation of Formula: - 

        The formula you provided is using the QUERY function in Google Sheets, which is a powerful tool for querying and analyzing data. Let's break down the formula step by step:

QUERY(

  {'D1'!A7:T;'D2'!A7:T;'T1'!A7:T;'T2'!A7:T;Peth!A7:T},

  "select * where Col4 is not null or Col8 is not null or Col1 is not null",

  0

)

{'D1'!A7:T;'D2'!A7:T;'T1'!A7:T;'T2'!A7:T;Peth!A7:T} :- This part specifies the range of data to be queried. The curly braces {} are used to create an array containing multiple ranges separated by semicolons. The ranges are from sheets 'D1', 'D2', 'T1', 'T2', and 'Peth', and the data is selected from columns A to T starting from row 7.

"select * where Col4 is not null or Col8 is not null or Col1 is not null" :- This is the query string that defines the conditions for selecting data from the specified range. It uses SQL-like syntax. Here's a breakdown of the conditions:

select *: Select all columns.

where Col4 is not null: Filter rows where data in column 4 is not null.

or Col8 is not null: Include rows where data in column 8 is not null.

or Col1 is not null: Include rows where data in column 1 is not null.

0 :- The last argument in the QUERY function is the number 0, which indicates that the data range includes a header row. In this case, it assumes that the first row in each range is a header row, and it should be treated as such.

        So, the entire formula is querying data from multiple ranges in different sheets based on the specified conditions and combining the results into a single output. It selects all columns where at least one of the conditions related to columns 1, 4, or 8 is met and assumes that the data has a header row.

Link for Sample Google sheet :- Link