A
AG
I work for a university and have been trying to automate a staff expense
claim template but have been stuck with the following for far too long
(help is much appreciated and please excuse the long list):
1. Data Summation
The university's accounting system has 5 components of 4-6 digits each
(call them column A-E); expenses can be coded to any combination of
A-E's (although in practical usage the unique combinations average about
5-12). Directly linking the coding and amounts from a 25 line detail
sheet to a summary sheet is cumbersome. I have reviewed and played
around with the list formulas from Chip Pearson's site but cannot figure
out how to combine the unique A-E combinations with the respective
numerical totals (I am arrayed to death at the moment). The Summary
sheet should be as follows (each line could have 1 or many source
transactions from the detail sheet):
A B C
D E Total
------ ------ ------ ------
----- ------
1234 1234 11111 1234
333 100.00
9876 5678 22222 5678
555 200.00
1234 1234 11111 1278
N/A 300.00
6666 1234 33333 1234
888 400.00
2. Data Validation
I have used named ranges for two columns (list for column D is ~75 lines
& the list for column E is ~8 lines) to ensure proper coding but have
run into a problem with the 'linked' validation. I have successfully
used the examples where one list is dependent upon another but cannot
find a solution to the issue of 8 entries from column requiring a unique
entry from column E while the other 67 entries from D require nothing
(or an N/A).
3. I have incorporated some VBA based on a tip from Deb Dalgleish's site
(Data Validation "Columns") re: swapping a selection made from a list to
a related info item (in my case the account description in column D to
the account number) as well as widening the drop down list (our
descriptions are somewhat long). Is it possible to incorporate VBA to
"select via typing / typeahead" so the list is not so cumbersome or will
there be issues re: the setup identified in #2?
4. I would like to incorporate a data entry form for foreign exchange
denominated transactions that places the computed value in the "Amount"
cell and the particulars (base amount, currency type, exchange rate,
etc.) in a non-printing row. The form would use checkboxes/radio buttons
re: business credit card / cash and source of exchange rate (bank / cc
statement / FX purchase receipt). Is this difficult to create and ensure
that users with varying screen resolutions can use it?
5. Lastly, there are four possible pages to print (summary / detail /
mileage / per diem); I am having problems creating a macro robust enough
to print only pages with numerical data (i.e. entering and deleting text
affects my macro and blank pages print). Suggestions (I can post the
code if needed).
Thanks very much in advance; I apologize for the long list of questions
but cannot seem to get one problem solved properly to go onto the next
task.
Dylan
claim template but have been stuck with the following for far too long
(help is much appreciated and please excuse the long list):
1. Data Summation
The university's accounting system has 5 components of 4-6 digits each
(call them column A-E); expenses can be coded to any combination of
A-E's (although in practical usage the unique combinations average about
5-12). Directly linking the coding and amounts from a 25 line detail
sheet to a summary sheet is cumbersome. I have reviewed and played
around with the list formulas from Chip Pearson's site but cannot figure
out how to combine the unique A-E combinations with the respective
numerical totals (I am arrayed to death at the moment). The Summary
sheet should be as follows (each line could have 1 or many source
transactions from the detail sheet):
A B C
D E Total
------ ------ ------ ------
----- ------
1234 1234 11111 1234
333 100.00
9876 5678 22222 5678
555 200.00
1234 1234 11111 1278
N/A 300.00
6666 1234 33333 1234
888 400.00
2. Data Validation
I have used named ranges for two columns (list for column D is ~75 lines
& the list for column E is ~8 lines) to ensure proper coding but have
run into a problem with the 'linked' validation. I have successfully
used the examples where one list is dependent upon another but cannot
find a solution to the issue of 8 entries from column requiring a unique
entry from column E while the other 67 entries from D require nothing
(or an N/A).
3. I have incorporated some VBA based on a tip from Deb Dalgleish's site
(Data Validation "Columns") re: swapping a selection made from a list to
a related info item (in my case the account description in column D to
the account number) as well as widening the drop down list (our
descriptions are somewhat long). Is it possible to incorporate VBA to
"select via typing / typeahead" so the list is not so cumbersome or will
there be issues re: the setup identified in #2?
4. I would like to incorporate a data entry form for foreign exchange
denominated transactions that places the computed value in the "Amount"
cell and the particulars (base amount, currency type, exchange rate,
etc.) in a non-printing row. The form would use checkboxes/radio buttons
re: business credit card / cash and source of exchange rate (bank / cc
statement / FX purchase receipt). Is this difficult to create and ensure
that users with varying screen resolutions can use it?
5. Lastly, there are four possible pages to print (summary / detail /
mileage / per diem); I am having problems creating a macro robust enough
to print only pages with numerical data (i.e. entering and deleting text
affects my macro and blank pages print). Suggestions (I can post the
code if needed).
Thanks very much in advance; I apologize for the long list of questions
but cannot seem to get one problem solved properly to go onto the next
task.
Dylan