How to create a report in Excel using QXL spreadsheet data
Problem Description:
I want to refresh my spreadsheet automatically from QXL exported spreadsheet. So, my spreadsheet data gets updated automatically using QXL exported spreadsheet.
How to create an MS Excel spreadsheet that automatically updates data from QXL exported Excel files?
Solution:
Please follow the below steps to create an MS Excel spreadsheet, which gets updated data automatically from QXL exported spreadsheet data:
1. Create a New MS Excel file.
2. Navigate to the "Data" tab & click on "Connections" to add QXL exported spreadsheet connection.
3. Add a connection by clicking on "Add."
Click on "Browse for More.."
Navigate to QXL Exported sheet folder.
The Default QXL Export sheet folder path is "This PC" --> "Documents" --> "QXL" --> YourQuickBooksCompanyName.
In this example, the QXL export sheet folder path is: "This PC" --> "Documents" --> "QXL" --> Rock Castle Construction.
Select QXL exported Excel file which you want to use as a source & click on "Open."
Click "OK."
Connection added. Click the "close" button.
4. Import Data from the added connection by clicking on "Existing Connection."
Select connection & click on "Open."
Configure the Imported Data property & click "OK."
5. Data Imported.
Now we are hiding a column that we don't want to use. You can hide column/columns by selecting a column/columns suitable, clicking on it & click on "Hide."
In this example, I want to see only an Unpaid Invoice. So, I have filtered the IsPaid column.
I have added a new column, Due Date, which will get data from the DueDate field. We are converting this DueDate data to Date using the DATEVALUE function.
By converting it to DateTime data type, the value displayed in Excel is numeric, and the correct value is shown only after formatting the cell.
Now we will hide the Original DueDate column & add the Aging column, which we use for counting the Aging Days of Invoice payment due.
We can count Aging using the DATEDIF function.
Now, let us sort data according to Customer name & use conditional formatting to show aging data.
After applying all the formatting, finally, the data looks as below.
To refresh your Excel sheet report, use the QXL Application and click the "Export" button. Once your source is updated, you can restore your Excel sheet.
To refresh the Excel sheet report, click the "Refresh All" button from the menu Data-->Refresh All menu.
|