Hi Guys n Gals,
I have been a fairly amateurish exceller!! over the years, and due to a recent sales job have I needed to do something a little more complex than sum(a1:a20) etc... !! lol
SO anyway... as I say. I started working for a home improvements company, who were operating in the stoneage wrt technology, and the overwhelming amount of sales brochures, survey pictures, data capture, invoices, commission forms (musnt forget that one) meant that after my first appointment I needed 6 hours on my PC writing the quote up, making sense of my survey notes, putting all the data into a spreadsheet, and then calculating my commission claim to the boss...
sheesh...
so I decided to think about making some user forms so that when I was sitting with the customer asking about conservatory style, ..(roof options, glass/poly, door size, survey info (drains, trees, old structures, soil type, etc etc)floor finish, plastering options, underfloor heating, tiling, laminates, electrical spec... etc etc... )I could easily click a button for each section and spit all the data to a single column, (appropriately indexed with row labels, )
so with the help of info on these boards, I assembled a very admirable selection of userform, with drop downs, picture indexers, flip out extending forms, in-form calculators, etc etc ...And I have to say, that it has taken some effort to piece it all together fixing little bugs wrt identifiers,labels, etc etc... and I have almost got there...
I can now run through slideshows, previous build info etc, style galleries, (in user forms), show all our trade mark reg info, collect all data from the customer, sizes etc etc... its all there.. in about 2 minutes... and my presentations will, I am sure, now come across very professional... and more importantly I have the roots of a database to then use in the future for generating the Customer Quote, and my commission claim etc..
So... whats the question im asking here... ?.
Well.... During several stages of the data collection I have extended list boxes so I that can chose several options... (ie: Edwardian, Glass Roof, Roof Vent) which are just three options from a list of 30 or so.... and it spits this data onto the spreadsheet form that I have labelled "QUOTESTORE"... under the appropriate "PRODUCT" Row...
but... it positions the text based on the selected items column position, with those items not selected blank...
eg: If Edwardian was the third option (lets say Victorian and Lean-To were 1 and 2) and Glass Roof was the 5th Option, (with Polycarbonate as the 4th), and finally "roof vent" is the 12th... with many others between , and after..... then my data appears (under row 14 "Product")
C1 C2 C3 C4 C5 ..........C12
" " " " Edwardian" " " Glass"......."Roof Vent"
which I can easily sort and filter... but... I need to have a button on main_menu user form called "Filter all blanks" (or whatever, which will loop from row 1 through 40 and, taking each row in turn, filter from column 2 (cos column 1 is the row index) to column 32, deleting blanks.... so that only the RELEVENT selected data from each multi-select-list box, is displayed in the row,
sheet name is QUOTESTORE
When I have this data, then I can start looking at a "Quote" form that pulls in each cell with VLOOKUP or something...
I have succesfully muddled through so far, but this one has had me scratching me head for 3 hours now... and im sure that when someone revels (probably 2 lines of code) I wil have a senior Homer Simpson moment.... "DOH !!!"
many thanks guys... you are all legends in helping me get this far...
I have code in my macros that I still struggle with, but it works a treat !! lol
slugifur (Sluggy Fur) after a scruffy old dog we affectionately names SLUGS.. but she moulted like a b***h !!
I have been a fairly amateurish exceller!! over the years, and due to a recent sales job have I needed to do something a little more complex than sum(a1:a20) etc... !! lol
SO anyway... as I say. I started working for a home improvements company, who were operating in the stoneage wrt technology, and the overwhelming amount of sales brochures, survey pictures, data capture, invoices, commission forms (musnt forget that one) meant that after my first appointment I needed 6 hours on my PC writing the quote up, making sense of my survey notes, putting all the data into a spreadsheet, and then calculating my commission claim to the boss...
sheesh...
so I decided to think about making some user forms so that when I was sitting with the customer asking about conservatory style, ..(roof options, glass/poly, door size, survey info (drains, trees, old structures, soil type, etc etc)floor finish, plastering options, underfloor heating, tiling, laminates, electrical spec... etc etc... )I could easily click a button for each section and spit all the data to a single column, (appropriately indexed with row labels, )
so with the help of info on these boards, I assembled a very admirable selection of userform, with drop downs, picture indexers, flip out extending forms, in-form calculators, etc etc ...And I have to say, that it has taken some effort to piece it all together fixing little bugs wrt identifiers,labels, etc etc... and I have almost got there...
I can now run through slideshows, previous build info etc, style galleries, (in user forms), show all our trade mark reg info, collect all data from the customer, sizes etc etc... its all there.. in about 2 minutes... and my presentations will, I am sure, now come across very professional... and more importantly I have the roots of a database to then use in the future for generating the Customer Quote, and my commission claim etc..
So... whats the question im asking here... ?.
Well.... During several stages of the data collection I have extended list boxes so I that can chose several options... (ie: Edwardian, Glass Roof, Roof Vent) which are just three options from a list of 30 or so.... and it spits this data onto the spreadsheet form that I have labelled "QUOTESTORE"... under the appropriate "PRODUCT" Row...
but... it positions the text based on the selected items column position, with those items not selected blank...
eg: If Edwardian was the third option (lets say Victorian and Lean-To were 1 and 2) and Glass Roof was the 5th Option, (with Polycarbonate as the 4th), and finally "roof vent" is the 12th... with many others between , and after..... then my data appears (under row 14 "Product")
C1 C2 C3 C4 C5 ..........C12
" " " " Edwardian" " " Glass"......."Roof Vent"
which I can easily sort and filter... but... I need to have a button on main_menu user form called "Filter all blanks" (or whatever, which will loop from row 1 through 40 and, taking each row in turn, filter from column 2 (cos column 1 is the row index) to column 32, deleting blanks.... so that only the RELEVENT selected data from each multi-select-list box, is displayed in the row,
sheet name is QUOTESTORE
When I have this data, then I can start looking at a "Quote" form that pulls in each cell with VLOOKUP or something...
I have succesfully muddled through so far, but this one has had me scratching me head for 3 hours now... and im sure that when someone revels (probably 2 lines of code) I wil have a senior Homer Simpson moment.... "DOH !!!"
many thanks guys... you are all legends in helping me get this far...
I have code in my macros that I still struggle with, but it works a treat !! lol
slugifur (Sluggy Fur) after a scruffy old dog we affectionately names SLUGS.. but she moulted like a b***h !!