Sunday 11 September 2016
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
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.
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.
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.
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.
Monday 1 February 2016
How to make a video or movie maker on windows 7
Initially go to your Start-> All programs -> Movie maker
if you are not able to find you can download from here
http://windows.microsoft.com/en-us/windows/movie-maker
And after download open movie maker and follow the steps as shown on video
if you are not able to find you can download from here
http://windows.microsoft.com/en-us/windows/movie-maker
And after download open movie maker and follow the steps as shown on video
Subscribe to:
Posts (Atom)