Previous Topic Next Topic

Transfer data from excel sheet to excel sheets.

Closed
bhaskarroy Posts 1 Registration date Friday November 20, 2015 Status Member Last seen November 20, 2015 - Nov 20, 2015 at 01:36 PM
vishnumethas55 Posts 1 Registration date Thursday November 26, 2015 Status Member Last seen November 26, 2015 - Nov 26, 2015 at 02:10 AM
Hello

I have a table contains about 5000 rows and 4 columns. The columns are Date, Codes, Department and Amount. I entered one year's data in that table. Now I want to create several worksheets which will contain data only for a particular code, for a particular date range and department. I am doing this by using IF-AND functions. This process transfers data correctly but when the criteria are not met it brings zero or blank cells. I need to filter to remove the zeros or blank cells. Is it possible to use a function which will transfer only data, no zeros or blank cells?

Thank you

Bhaskar

1 response

Answer 1 / 1
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Nov 24, 2015 at 09:40 PM
See this thread
https://ccm.net/forum/affich-258700-interesting-question-for-excel-champions
0
vishnumethas55 Posts 1 Registration date Thursday November 26, 2015 Status Member Last seen November 26, 2015
Nov 26, 2015 at 02:10 AM
1. Step 1: Create a connection with the workbook and its worksheets
2. In the Workbook Connections dialog box, click Add.
3. Near the bottom of the Existing Connections dialog box, click Browse for More.
4. Find your workbook, and click Open.
5. In the Select Table dialog box, select a table (worksheet), and click OK.
6. NOTE You can select and add only one table at a time.
7. Because any table that's added is named after its workbook's name, you can rename it if you want a more meaningful name.
8. Select a table, and click Properties.
9. Change the name in the Connection name box.
10. Click OK.

11. Step 2: Add the tables to your worksheet
12. Click Existing Connections, choose the table, and click Open.
13. In the Import Data dialog box that appears, choose where to put the data in your workbook and whether to view the data as a table, PivotTable report, or PivotChart.
14. Optionally, you can add the data to the Data Model so that you can combine your data with other tables or data from other sources, create relationships between tables, and do much more than you can with a basic PivotTable report.
0