I don't use it quite as much as you do, but have dreamt up some quite fancy workbooks in the past.
one of which has a fully automated dashboard sheet, which I use to communicate status using "send sheet as e-mail body". beats grinding through tons of data or holding a seperate administration just for reporting purposes.
it makes hefty use of dynamic defined names and array formulas.
i tend to stear clear of vba although I did so in the past - it makes it less "pure". lol. you can go a long way with formula's alone.
Some spreadsheet help
-
raum
- Posts: 3944
- Joined: Sun Jul 24, 2005 10:51 am
maybe you can help me with something.
I am trying to create a work book that basically acts like a relational database.
It is literally something I could do in SQL platforms in a few minutes, but I am unfamiliar with writing a lot of functions.
and the ones I have written, my accountants at work screw up. This is one of the reasons why i am going by way of vba.
another is i am working with two different sized arrays.
One is on a "Transactions" sheet, and is queried, based on criteria that are cells linked to user input combo boxes.
Basically, person selects "Plant", and the selects "Month" and this pulls the transactions for that plant for that month to the report page. I can't find a way to use Advanced VBA filter methods to reference different sheets, even with a "Public" method Global variable. And I am about ready to try the function method again.
But the problem with the Funtion method I know is My Report Range is only 20 rows, and my transaction page is more than 500 rows. I can only do this if the two ranges are the same size, but it doesn't fit my report model.
I have a sample file, that I can send you, if you want to help. The data isn't legit, but the form is.
I am trying to create a work book that basically acts like a relational database.
It is literally something I could do in SQL platforms in a few minutes, but I am unfamiliar with writing a lot of functions.
and the ones I have written, my accountants at work screw up. This is one of the reasons why i am going by way of vba.
another is i am working with two different sized arrays.
One is on a "Transactions" sheet, and is queried, based on criteria that are cells linked to user input combo boxes.
Basically, person selects "Plant", and the selects "Month" and this pulls the transactions for that plant for that month to the report page. I can't find a way to use Advanced VBA filter methods to reference different sheets, even with a "Public" method Global variable. And I am about ready to try the function method again.
But the problem with the Funtion method I know is My Report Range is only 20 rows, and my transaction page is more than 500 rows. I can only do this if the two ranges are the same size, but it doesn't fit my report model.
I have a sample file, that I can send you, if you want to help. The data isn't legit, but the form is.
| BBcode: | |
| Hide post links |
-
Brains
- Posts: 549
- Joined: Fri Nov 25, 2005 8:14 am
sure. it does seem like something i can not do out of the top of my head, but I'll come up with an answer eventually.
send me the workbook, it will be way easier communication. [email protected]
send me the workbook, it will be way easier communication. [email protected]
| BBcode: | |
| Hide post links |
-
AYHJA
- 392
- Posts: 37990
- Joined: Fri Sep 17, 2004 2:25 pm
- Location: Washington, D.C.
- Contact:
-
WAY
- Site Admin
- Posts: 2411
- Joined: Sat Nov 06, 2004 4:43 am
- Location: Sydney, Australia
- Contact: