Hi bro. എക്സൽ sheetile ഡാറ്റസിൽ നിന്നും വേണ്ട ഡാറ്റസ് മാത്രം ഓട്ടോമാറ്റിക് ആയിട്ട് മറ്റൊരു ഷീറ്റിലേക്ക് മാറ്റാൻ സാധിക്കുമോ example : ഒരു ഓഫീസിലേക്ക് വരുന്ന എൻക്യുര്യസിൽ നിന്നും succes ആകുന്ന ഡാറ്റാ അടുത്ത ഷീറ്റിലേക്ക് ആകുവാൻ സാധിക്കുമോ?
we can use macros for it, and we have to take care that each time when you take the second sheet the macros wil to run automatically to update the data
SUM(OFFSET(G4,COUNT(G5:G28)-5,0,6,1)) is the formula I have typed here. Sum function for adding the last 6 months data. Offset simply indicates where and which 6 months to be added. Offset shows the exact location of the sales data to be added to the Sum Function. When the Offset function is entered syntax: OFFSET(reference, rows, columns, height, width). Here REFERENCE simply refers to a starting point, ROWS refer to how many rows we need to move from the reference/ starting point to reach the particular area that we need, and COLUMNS refer to how many columns we need to move from the starting point to reach the particular area which we need, HEIGHT refers to the vertical area upto which we need(here for adding), WIDTH refers to horizontal area upto which we need (here width 1 for counting present column only). Here, we need to add last 6 months' data So firstly we use the Sum function, inside this function we are trying to reach the particular area that need to be added i.e last 6 months sales data. next, we begin with the OFFSET Function which is mainly used to indicate the location which needs to be added, inside offset starting with a Reference (starting point i.e Sales heading here), after which how many rows we need to move, initially we have to reach 7th row (ie Oct 23) as we know the sheet is dynamic and we will enter data in future, So we are using count function to count the total number of data entered (which is 12 initially ie April to march sales data, note that here after we select the entire table sales area so that each time when some data is entered it is counted)after getting 12 we have to reach row 7(ie october sales)so we less 5 from it, so we get 7(in row)we reach 7th row. After that number of columns we need to move(here we dont need to move to next column so we enter zero here).next we need to specify the height( ie number of rows to be added ie we take here oct to march sales which 6 number of rows- we write here 6 and finally width we write 1which is one column only since we dont need to take data from next column).
