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