• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

I get the feeling I'm not doing this right....

brianjgeraghty

New Member
so...I have a spreadsheet which I've put together over some time to attempt to make the tracking of our RFPs easier. I'll say that I'm probably in the intermediate category of users, I have in the past set up ODBC connections to databases, pivot tables, and was reasonably familiar with VB 6/VBA a while back, but that was a while ago.


Essentially, I feel like I'm not creating this tracking spreadsheet in the most efficient manner I could be. There are things that every RFP has (Sales person, price, products) and I want to be able to standardize and report off of them, and I have been doing some validation rules etc..my problem being that I'm not sure how to add more than one product in a way which would allow me to report off of it, you know? What would be the most efficient way of accomplishing this? Any input would be very helpful.


Thanks!

~Brian
 

keymaster

New Member
Welcome Brian...


2 ideas come to me when you ask.. "my problem being that I'm not sure how to add more than one product in a way which would allow me to report off of it, you know? What would be the most efficient way of accomplishing this? Any input would be very helpful."


if you have some idea as to what is the max. number of products possible (say 5) then put them in columns in the same table. It keeps things simple. Since you are anyway going to write VBA, it would be easy to merge the products list and do something with it.


Otherwise, create one more table (in another sheet preferably) where you link products to RFP. So if a RFP has 6 products, it will have 6 rows in this table. This is the ideal way to manage the data. You can write a simple query on this table and join the results with the main table to do what you want.
 

brianjgeraghty

New Member
Is that done strictly in excel? or does that involve a database as well, like access? I didn't know you could put "tables" per se in excel.
 

keymaster

New Member
Excuse my usage of the term "table" I meant a named range or a normal lists (in 2003) or tables (in 2007 onwards). Unless you have lots of data or too many tables, you can make do with excel. As long as the ranges are named you can access them from VBA. (actually you can access the normal cell references too, but it would be convenient to have named ranges)
 
Top