In excel I have a folder and within that folder I have 3 folders called A1, A2 and A3.
In each of these three folders are more folders, each one being the name of a client. Within these folders will either be spreadsheets or more folders with spreadsheets within these folders. Each folder once you have drilled down will have a spreadsheet with the word ledger in the title somewhere. Each client folder could have more than one sheet in with the word ledger in the name and we need data from each of them. There will be other spreadsheets that don't have the word ledger in them and we do not need to retrieve data from them.
Each sheet that has ledger in the title is an accounts ledger.
In A1 the majority of ledger sheets will only have 1 transaction on it but it could have more or non at all. In A2 the ledger should always have a balance. In A3 I would expect all the ledgers to have had at least 2 entries but now have a zero balance.
Each sheet with the word ledger in the title is the same template and contains the client info and accounts transactions. The client info on each sheet can be found in the same cells on each sheet. The accounts transactions start on the same row on each sheet but there could be a number of transaction rows. From 0 to maybe 30, possibly more.
The columns for the transactions are the same on each sheet starting in the same columns. There are 8 columns. date, type of entry and then debit and credit for one account (office) then a running total and then debit and credit for a second account (client) and the running total. There is a formula in both running totals columns using the information input in to the credit and debit columns. When we add transactions to the ledger we copy the last row and paste onto the next so it copies the formula and then type over the entries with the new data we need to enter.
There may be text at the bottom of the ledger which is irrelevant to the data I need.
I need to run a report that tells me the client name, code and account balance for client and office on each ledger for a specific date. This figure could be in a different row cell reference on each ledger sheet depending on how many entries have been recorded on each sheet but will be in the same columns. This could be in the middle of the list of transactions and there could be a number of transactions on that date so it would need to choose the last transaction with that date. The figure could be positive or negative.
It would be helpful if I can run the report on each folder (A1, A2, A3) independently of each other.
34 freelancers are bidding on average £77 for this job
I mostly help on free tech support forums to help create customized workbooks with vba. If you are using Mac software it will be a little trickier, but still quite possible.