Reporting Using A Drop Down List From A Preconceived Table.
Rate this:
(3/5 from 1 vote)
I have created a table of 25 columns by about 400 rows. The information is Numerical and text. I have created some reports based upon VLookup and countif. I am trying to create a report based upon the contents of a cell with a drop down list which would produce a number of rows containing relevant data. I cannot get the info from pivot tables. The information I'm looking to reproduce would be driven by a Project Managers Name which would (I hope) generate selected cells from all those rows which contain the individuals name.If anyone can spare a couple of minutes to help I'd be very grateful.
In the table, is the project manager name always in the same column? If so, you could use a simple filter to return the rows relevant for a project manager, then use Goto > Special > Visible cells only to copy the rows into a separate sheet.
Also, do you want all columns in the row, or only a selection?
Thanks for the very quick reply. The PM name is only in one column. I only want the contents of some (6) of the relevant columns. This may result in 4 or 5 rows each with 6 or so columns - I think that is what is throwing me. I have created a drop down list in a cell for the user. The PM name selected in that cell is to drive the selection I have described above. I'm going to try the advice you've kindly given me i.e. filter by name etc. Any other tips gratefully received.
Good. Another tip is to have the list of names in a separate sheet. This should drive the contents of the drop down menu control. In the same sheet, you can dedicate a cell for the control cell which will give the index number of the selected name in the drop down. This cell can in turn populate another cell with the name chosen by using the INDEX function.
A simple VBA macro can be recorded to do the filter. This macro can then be modified to capture the name chosen in the drop down menu from the cell with the name and then the macro can be attached to the drop down menu "onchange" event. This way, when the user chooses the name in the drop down menu, the filtering automatically takes place along with any other data manipulation tasks that were recorded in the original macro.
I was rather hoping that the answer was not going to be a macro - looks as though the next few weeks are taken care of. I really appreciate the swift response - not sure if I can ever return the favour but you never know.
Thanks again - Mike M
MAM
Posted by MikeM on
Displaying page 1 of 1
Find relevant Excel templates and add-ins for Reporting using a drop down list from a preconceived table. in the Excel Business Solutions Directory
