tlc53
Active Member
- Joined
- Jul 26, 2018
- Messages
- 399
Hi,
I have an idea about how I want my spreadsheet to work but I'm just not sure about the best way to go about it. As I will be arranging large volumes of data, I would like to keep it as simple as possible and avoid too many IF Statements which can cause the file to get too big. Can you please let me know if you have any ideas or suggestions?
The spreadsheet will have a data tab, where all the raw data is downloaded to. I would imagine this would take up about 50,000 rows. This data will be organised by account number, which every row will have.
There will then be separate tabs for each client. Say, 30 clients. Each client is responsible for approximately 10 account numbers each. At the top of each client tab it will have a summary showing the totals for the account numbers. This I guess I'll get by SUMIF looking at the data tab.
The next part is where I'm a little unsure about what to do. Under the clients totals summary report, I want a breakdown to follow which shows all the transactions for that client. I could allocate a large enough section for each account number and then hide any blank/unused rows. It just needs to be in the order it is currently in on the data tab. But how can I get the data here easily? Basically, how can I get it to report just the account number transactions for that client, from looking at the many transactions on the data tab.
Here's an example..
Data Tab
Client ABC Tab
SUMMARY (SUMIF totals looking at Account Numbers on Data Tab)
$2,050.56 - Account No. 1
$1,009.25 - Acounnt No. 6
$5,555.90 - Account No. 25
$2,000.00 - Account No. 66
$3,250.85 - Account No. 104
NARRATIVE
All Account No. 1 transactions (=$2,050.56)
All Account No. 6 transactions (=$1,009.25)
All Account No. 25 transactions (=$5,555.90)
All Account No. 66 transactions (=$2,000.00)
All Account No. 104 transactions (=$3,250.85)
The person who will be using this has little Excel experience so I intend to automate everything as much as possible by putting in Macros/VBA coding.
Thanks for your time!
I have an idea about how I want my spreadsheet to work but I'm just not sure about the best way to go about it. As I will be arranging large volumes of data, I would like to keep it as simple as possible and avoid too many IF Statements which can cause the file to get too big. Can you please let me know if you have any ideas or suggestions?
The spreadsheet will have a data tab, where all the raw data is downloaded to. I would imagine this would take up about 50,000 rows. This data will be organised by account number, which every row will have.
There will then be separate tabs for each client. Say, 30 clients. Each client is responsible for approximately 10 account numbers each. At the top of each client tab it will have a summary showing the totals for the account numbers. This I guess I'll get by SUMIF looking at the data tab.
The next part is where I'm a little unsure about what to do. Under the clients totals summary report, I want a breakdown to follow which shows all the transactions for that client. I could allocate a large enough section for each account number and then hide any blank/unused rows. It just needs to be in the order it is currently in on the data tab. But how can I get the data here easily? Basically, how can I get it to report just the account number transactions for that client, from looking at the many transactions on the data tab.
Here's an example..
Data Tab
Client ABC Tab
SUMMARY (SUMIF totals looking at Account Numbers on Data Tab)
$2,050.56 - Account No. 1
$1,009.25 - Acounnt No. 6
$5,555.90 - Account No. 25
$2,000.00 - Account No. 66
$3,250.85 - Account No. 104
NARRATIVE
All Account No. 1 transactions (=$2,050.56)
All Account No. 6 transactions (=$1,009.25)
All Account No. 25 transactions (=$5,555.90)
All Account No. 66 transactions (=$2,000.00)
All Account No. 104 transactions (=$3,250.85)
The person who will be using this has little Excel experience so I intend to automate everything as much as possible by putting in Macros/VBA coding.
Thanks for your time!
