क्या आपको Importrange Function के बारे में पता है ?

नमस्कार दोस्तो !  मैं  पवन कुमार पाल (Google Sheets Developer & Instructor) आप सभी साथियों का Google  Drive ब्लॉग पर हार्दिक स्वागत करता हूं |  
क्या आपको  Importrange Function के बारे में पता है ? अगर नहीं तो ये पोस्ट  खास आपके लिए ही है ,

इस ब्लॉग में हम Importrange  Function  के बारे में बात कर  रहे है, जो Google  Sheet  में Data Fetch करने के लिये Amazing Function  है, इससे  हम एक Sheet  से दूसरी शीट में Data  Import  करते है| तो 
हम आज इसके बारे में  विस्तार से समझेगें | तो चलिए शुरू करते है :-

Importrange  Function का Syntex :-

IMPORTRANGE(spreadsheet_url, range_string)

जहाँ spreadsheet_url - The URL of the spreadsheet from where data will be imported.

• The value for spreadsheet_url must either be enclosed in quotation marks or be a reference to a cell containing the URL of a spreadsheet.

और range_string - A string, of the format "[sheet_name!]range" (e.g. "Sheet1!A2:B6" or "A2:B6") specifying the range to import.

• The sheet_name component of range_string is optional; by default IMPORTRANGE will import from the given range of the first sheet.

• The value for range_string must either be enclosed in quotation marks or be a reference to a cell containing the appropriate text.

याद रखने योग्य महत्वपूर्ण बिंदु :-

• Spreadsheets must be explicitly granted permission to pull data from other spreadsheets using IMPORTRANGE. The first time the destination sheet pulls data from a new source sheet, the user will be prompted to grant permission. Once access is granted, any editor on the destination spreadsheet can use IMPORTRANGE to pull from any part of the source spreadsheet. The access remains in effect until the user who granted access is removed from the source.

• If the data you are trying to import is too large, you may get an error.

दोस्तों इस article में Importrange  Function के बारे में  बताई गई  जानकारी से related  अगर आपके मन में कोई भी सवाल है, तो आप हमें info.edutech19@gmail.com पर mail  कर सकते है , हम जल्द से जल्द आपकी समस्या का समाधान करेंगे।

Importrange  Function के  बारे में और ज्यादा जानकारी के लिए आप हमारे Youtube Channel पर पर visit  करें , अगर हमारी  videos  series आप के लिए knowledgable  हो तो videos  को like  करे , अपने दोस्तों में share  करें , जिससे आप के साथ -साथ आपके साथी भी Google  Drive  के Features का लाभ सके |  साथ ही साथ आप हमारे चैनल को सब्सक्राइब करे और घंटी जरूर दबाएं , जिससे आप तक हमारा  हर वीडियो आसानी से पहुँच सके|  

Read More

क्या आप Google Drive के बारे  में जानते हैं ? 

Read More

Logical Function in Google Sheet

                                           Logical Function in Google Sheet

Logical functions - overview

Google Sheets provides logical functions to work with the logical values. The functions are AND, OR,  IF, IFS. You use these functions when you want to carry out more than one comparison in your formula or test multiple conditions instead of just one. As well as logical operators, Excel logical functions return either TRUE or FALSE when their arguments are evaluated.

Now you can go with Delete and Restore files in Google Drive.

Read More

How to Fix Query Errors???

How to Debug  Query Errors???

You are here for learn about different error types in Google Sheets and how to correct It. Google Sheets returns the error value if the formula in the cell has some kind of error.

The different error types in Google Sheets are as follows and all these error values start with the # number sign.

You should properly understand the error values to correct the formula error in Google Doc Spreadsheet.

Query Errors in Google Sheets

All the below formula error values have a number associated with it. The prefix in the values below are the error numbers associated with it.

1. #NULL!

2. #DIV/0!

3. #VALUE!

4. #REF!

5. #NAME?

6. #NUM!

7. #N/A

8. All other error types

The above are the 8 different error types in Google Sheets. If any of the cells contain error values you can test it and get the error numbers in another cell.

You can use the Error.Type function for this purpose. This function can identify all the above 8 types of errors in Google Sheets and can return the numbers from 1 to 8 accordingly.

How to Use Error.Type Function in Google Sheets

Syntax:

ERROR.TYPE(reference)

Usage:

Suppose cell A1 has an error value #DIV/0!. The following formula would return number 2 as result.

=ERROR.TYPE(A1)

Then what is the use of Error numbers in Google Sheets?

Example:

The value in cell A1 is 20 and B1 is 0. The following formula would return an error.

Formula:

=A1/B1

Result: #DIV/0!

In the cell that contains the formula, you can see a formula error notification saying “function DIVIDE parameter 2 cannot be zero”.

If you divide a value in a cell with 0 in another cell the above error occurs. See one more example. Here Value in A1 is 20, B1 is a text string.

Formula:

=A1/B1

Result: #VALUE!

In both the above cases you can use Google Sheets ERROR.TYPE function as below in an IF logical test.

=iferror(if(ERROR.TYPE(A1/B1)=2,"Value Can't be Zero",IF(ERROR.TYPE(A1/B1)=3,"TEXT")),A1/B1)

This formula would return “Value Can’t Be Zero” if the error type is #DIV/0!, “TEXT” if the error type is #VALUE! and else the final part A1/B1.

Do you know why I’ve used IFERROR function in this formula?

The ERROR.TYPE function itself returns an #N/A error if there is no error in the calculation. So if the error value is #N/A we can decide that there is no error in our formula.

With the help of IFERROR, we can execute the A1/B1 calculation.

You May Also Like: Difference Between ISERR and ISNA Functions in Google Sheets

You have already learned about different error types in Google Sheets and the use of Error.Type function. Now time to learn in details about all error types and how to correct it in your Spreadsheet.

Understand Error Values in Google Sheets

1. #NULL!
I’ve never seen #NULL! error in Google Sheets. Of course, it’s there in Excel. Anyway in Google Sheets there is an error number associated with it and it’s 1.

2. #DIV/0! Error in Google Sheets and How to Correct It.

It’s easy to correct #DIV/0! Error in Google Sheets. See the below example.

Here the value in Cell B2 is 0 that’s why the error happens. It shouldn’t be 0 or blank. I’ve applied the formula in Cell C1. Check the error notification in that cell.

3. #VALUE! Error in Google Sheets and How to Correct It

This error is one of the most commonly appearing error in Google Sheets. It happens when you apply mathematical operations in one or more cells that contain text.

In the below example the multiply function encountered an error in B3 as it’s text.

#VALUE! Error in Google Sheets and How to Correct It

But even if the values are in number, in certain cases you may find this error! Because in certain cases the numbers in the reference cells may be numbers formatted as text. You can check it form the menu FORMAT > Number.

4. #REF! Error in Google Sheets and How to Correct It

This error is related to Invalid Cell References in Google Sheets. There are two main reasons for the #REF! error in Google Sheets.

Example 1: Circular Dependency.

Circular Dependency

See the above formula. The formula is to sum Cell B11 and B12. But in the SUM formula, the cell references include the cell B13 too. B3 is the cell where I’ve keyed in the SUM formula.

Example 2: If you delete any cell, row, column or sheet which is already used as a reference in any formula, then the formula would return the above error.

5. #NAME? Error in Google Sheets and How to Correct It

The below logical test is the best example to show you why Google Sheets returns #NAME? error.

#NAME Error in Google Sheets and How to Correct It

In the above IF formula, I should have entered the text string in double quotes. Otherwise, Google Sheets would consider it as a named range. Since there is no named range in the sheet, the error occurs.

6. #NUM! Error in Google Sheets and How to Correct It?

This error is not common. The #NUM! error is caused by an invalid argument in a formula in Google Sheets. Below is the example.

Formula:

=rate(75, -1500, -10000, 200000)

Result: #NUM!

Formula:

=rate(69, -1500, -10000, 200000)

Result: 1%

7. #N/A Error in Google Sheets and How to Correct It

This error is common in Google Sheets and this simply means the value is Not Available.

Cell Value in A8=”Apple”

If I use the formula as below in any other cell, it would return the #N/A error. Because there is no value in cell A8 as “Orange”

=ifs(A8="Orange",500)

But if the function used is IF not IFS, it would return FALSE.

=IF(A8="Orange",500)

Finally, for any other error types, normally typo, which is not specified above, Google Sheets would return #ERROR!.

Formula:

=sum(A9 b9)

Result: #ERROR!

That’s all. Hope this tutorial can help you in finding  various types error of query  function in Google Sheets.

Live with joyfully.

Read More

 Learn the most Powerful Formula for Google Spreadsheet.

The Query formula in Google Sheet is quite a powerful and versatile function. So much so that,we can call it a one-stop-shop for all the logical, lookup, summation, counting, averaging, filtering and sorting requirements. It helps us fetch specific information from a data set, through a query statement. Much like fetching result sets from a database using queries. The syntex of the Query language used in this formula is similar to SQL.

SYNTEX

QUERY (data, query, [headers])

data – is the reference to the range of cells on which we want to query upon.

query – is the text using which the QUERY formula churns out the information we are looking for from the data set. Since it is expected to be a string, it has to be enclosed within a set of quotes. Or, it can also be a reference to a cell, where the query text is stored.


headers – is an optional parameter that indicates the number of header rows at the top of the data. If left out, Google Sheets guesses the value based on the content within the data.

Usage: Query Formula

First of all, to understand how the formula is put to use, let us consider the following sample data. It consists of information corresponding to a list of students who have enrolled into various courses at a university. For all the demonstration purposes, we’ll enter the formula in the cell G1. It will be displayed in the formula bar in the snapshots.

Example # 1:

We will start off with a very fundamental demonstration. So, we use the QUERY formula to fetch the names of the students who are residing on campus.

Example # 2:

Having dealt with the basic example, let us now try fetching the names of the students who are NOT residing on campus.

Example # 3:

Now, we will fetch the names, ages, departments of the students whose have taken more than 7 courses.

Example # 4:

We will now attempt taking this a step further. We bring up the names, departments, join dates of the students aged 25 or below, and have joined the university between 25-Dec-2016 and 20-Jan-2017. Please note, in the query text, the dates always have to go with yyyy-mm-dd format, enclosed within single quotes.

Example # 5:

What if we need to reference the date from a cell? No problem there! We will get around with the help of concatenating operators and a text function. Therefore, in the example below, we will get the names and join dates of the students that joined after 1-Jan-2017.

Example # 6:

Is there a way to list out all of the departments and the display numbers of courses taken from the respective department? Yes, there is! And, we might as well understand the power and versatility that the QUERY formula offers.

You will notice that the QUERY formula returned the second column with the header “sum Courses”. Honestly, it is a bit awkward to have that for a header. But, we can fix that and rename it. Not only that, we will also use the second column (now renamed to ‘Courses Taken’) to sort in ascending order. Here is how we do it.

Example # 7:

Can we display the number of instances of each of the departments? Of course, we can! The QUERY formula got us covered here as well.

Example # 8:

Consequently, we will now experiment with the third parameter. While this is an optional input, it might come in handy when we come across headers that span across multiple rows. In such cases, this parameter helps us combine the headers in one single row, as shown below.

Query function video coming very soon. I explain very easy & simple way to how to use query function to generate amazing report. 

Read More