• 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.

DropDown should result in giving all the matching date

B

brijendraydv

Member
Hi Everyone,


I have a task to do where i have a raw data in which i have various Vendor names and my task is to create a list or combo or any dropdown and for one vendor name there are many Tool numbers so I want that when i select any one vendor name it shuld show all the corresponding data matching to that vendor name(normally more than 1 row) and once the data reflects i need to print the outcome data.Note-the data range will be dynamic so how can i select dynamic print area.
 
Have you tried a PivotTable? This seems like a perfect setup where Vendor Name would be one of the Page Fields and whatever associated data you are wanting would be displayed in the report. Change filter, print. Repeat as needed.
 
@Montrey

Sure thing. If you manually setup a print range, you can then check the Name Manager and see that XL has defined print_range. You can then modify the formula to make it dynamic.
 
Hi Luke,


thanks for this but even i wanted to give a fancy look to my report if i would have created one datavalidation list and once i have selected any supplier name it would have resulted in giving all the realted rows but again thanks for pivot idea ,i dont know why it did not came to my mind.


Also,i want the outcome of selected supplier name to be printed without setting up the print area manually all d time.I wanted to have print area selected automatically.


Lastly ,for example if i have Vendor name and location in the 4 and 5th column of output result after selecting any vendor name then the header of the page shoould be combination of these 2 attributes(vendor name ,location)for ex like- ABC LTD,VICTORIA and header should print ABC LTD,VICTORIA.


Many thanks,
 
Hi Luke,


Please explain your comment-Sure thing. If you manually setup a print range, you can then check the Name Manager and see that XL has defined print_range. You can then modify the formula to make it dynamic.


do you have any example page or link for this to understand better.
 
Dynamic print range:

www.youtube.com/watch?v=aLkNIVMnAZY


For your header, can you use a regular cell? If so, you could concatenate values like:

=C2&" "&D2&" "&E2


The actual header can't be made dynamic; the only way would be using a macro.
 
Hi Luke,


thanks for the prompt reply,i saw the video but it seems that the dynamic range offset formula will not work in pivot table that i created as you adviced.Is it so?
 
I'm confused. Are you trying to use the dynamic range to define the range of data to go into the PivotTable, or to define the range you want to print? Perhaps if would be easier if you could post a sample workbook/data?
 
Hi Luke,


To be clear i have uploaded the file.

http://speedy.sh/ntCYr/Samplewrkbk.xls


if you see sheet pivot and if you select 2 vendors from drop down you will see different range of data.I want to print these data as per the vendor selected(in this case only 2 vendors are there,can b many more).and also in the print header the vendor name and location should be mentioned like FISCHER FORMENBAU GMBH,Kolkata.In the last post i cannot understand where to type =C2&" "&D2&" "&E2 .if possible can you give me the macro code to do so.


Many thanks,
 
Code for the header. To install, right click on sheet tab, view code, paste this in. Modify the one line as needed/desired

[pre]
Code: 
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim HeaderString As String
'Define what you want the header to say
'Currently concatenates cells C2, D2, and E2
HeaderString = [C2] & " " & [D2] & " " & [E2]
Me.PageSetup.CenterHeader = HeaderString

End Sub
[/pre]
 
Hi luke,


I cannot see any header when i see the print preview when i add the above code in the sheet.Please see the sheet where i added the code im using xl 2003.

http://speedy.sh/cFdpJ/Samplewrkbk.xls

Please let me know


Thanks,
 
I'm afraid I can't access shared files, so I don't know what's wrong. =(

Is the macro not being triggered when you update the PivotTable?
 
Hi, brijendraydv!


I downloaded the file and in the pivot table update event it has this code:

-----

[pre]
Code: 
HeaderString = [C2] & " " & [D2] & " " & [E2]
Me.PageSetup.CenterHeader = HeaderString
[/pre]
-----


It runs those statements each time you change B1 filter but the problem is that row 2 is blank! That's why you don't see anything, in fact you get there only two spaces.


If you tell me what do you want to have as a title (what are supposed to refer stated cells C2:E2?), maybe I could fix it for you.


Regards!


@Luke M

Hi!

Sorry for the interruption, my dark side's influence.

Regards!
 
@SirJB7

No worries, I was hoping someone else who could download the file would come by and take a look.


@brijendraydv

Glad to hear you got it working. Come back anytime.
 
@Luke M

Hi!

Nice trick that of saying you can't access shared files. It never came up to my mind. I think I'll be using it frequently.

Regards!

PS: and I'm the lazy hahahah
 
You must log in or register to reply here.
Back
Top