Blogger help videos

Blogger help videos

How to copy fields from sheet1 to sheet2

For example we have sheet 1 (Column names A, B, C) and sheet2 (Column names A,B, C)

Assume you are in sheet 2
You want to copy Column B from sheet1 and put in sheet 2


On sheet 2
Just create or go to column D
Type this formula in column field .. this copy the field.

=’Sheet1’!B3

How to do LOOKUP from other sheet

Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. By default, the table must be sorted in an ascending order.
Syntax
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
▪ lookup_value: is the value to be found in the first column of the table, and can be a value, a reference, or a text string.
▪ table_array: is a table of text, numbers, or logical values, in which data is retrieved. Table_array can be a reference to a range or a range name.
▪ col_index_num: is the column number in table_array from which the matching value should be returned. The first column of values in the table is column 1.
▪ range_lookup: is a logical value: to find the closest match in the first column (sorted in ascending order) = TRUE or omitted; find an exact match = FALSE.

How to do SUMIF from other sheet

Problem : I want to know what is the quantity sum on Sheet 1 for all similar order id. Result I want to show in sheet 2.

Sheet1

Column A               Column D          
ordernumber1              1
ordernumber1               2
ordernumber1               1
ordernumber1                3


ordernumber2                1
ordernumber2                 3

Sheet 2
Column A               Column D           Column Results E
ordernumber1              1
ordernumber1               2
ordernumber1               1
ordernumber1                3


ordernumber2                1
ordernumber2                 3


=SUMIF(’Sheet1’!G:G,Sheet2!A3,’Sheet1’!T:T)

=SUMIF(’Sheet1’!A:A,Sheet2!A3,’Sheet1’!D:D)


Means .. on sheet1 order id range, and sheet 2 order id criteria sum_range sum the quantity

Here A3 means first create formula for one field .. and then copy paste for all other fields.. by selecting and dragging the field.

How to do SUMIF with in the same sheet

Problem: I want to sum the quantity of all similar id
If you click on Fx on bar .. it will show formula builder and syntax details.

Problem:  Order id , Quantity

Column A               Column D           Column Results E
ordernumber1              1
ordernumber1               2
ordernumber1               1
ordernumber1                3


ordernumber2                1
ordernumber2                 3

Formula

In results column  field  enter like this.
=SUMIF(A:A,A3,D:D)

First type = and choose SUMIF()

Now Range means Column A .. Just click on top of column to select total column

put,  and now Criteria means Order id  so click on field of Column A

put,  and now sum_range means which column we have to do sum

We have to do Quantity

now click on qty column .. Column D


Adds the cells specified by a given condition or criteria.
Syntax
SUMIF(range,criteria,sum_range)
▪ range: is the range of cells you want evaluated.
▪ criteria: is the condition or criteria in the form of a number, expression, or text that defines which cells will be added.
▪ sum_range: are the actual cells to sum. If omitted, the cells in range are used.