I've attached an example file so hopefully if I'm explaining anything in words you will be able to have a look at the file
What I would like to do is (not even sure if its possible)
Select a batch number from the drop down list and then have the corresponding data shown in a table below this. there could be upto 10 lines of the same batch number and product amounts.
Waving back at ya ! - haven't had to ask for help in a long time - you lot have taught me so much but I just can't get my head around this one. Anyway sorry to take so long in getting back to you..... had visitors last night so was forced to be sociable !!
Sorry about confusing you - I had to keep deleting data from my test file to get it to right size so probably thats from the original original test file if you see what I mean. The file as it is now is how it will appear
Batches aren't always grouped together I could have a list of 400 items and they'd be all mixed up.
Is this what you mean?
Ta,
Moll
p.s. Thanks for taking the time to look at my problem by the way.
Sheet3 of the attached is a pivot table to pull the unique values from Sheet2!A:A.
Then there's a little code in the worksheet module for Sheet1 so that when you select from E12 the list under "Data to show here" updates. The list draws from "Ref ID" cos I still don't see "Rotn No.". The main thing is "Batches aren't always grouped together I could have a list of 400 items and they'd be all mixed up" means you will need a code loop of some description.
Hope this moves it forward a bit.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$E$12" Then Exit Sub
If Range("E" & Rows.Count).End(xlUp).Row > 16 Then
Range("E17:E" & Range("E" & Rows.Count).End(xlUp).Row).ClearContents
End If
For Each Cell In Sheets("Sheet2").Range("pt_data")
If Cell = Target Then
Range("E" & Rows.Count).End(xlUp).Offset(1) = Cell.Offset(, 1)
End If
Next Cell
End Sub
Sorry if I still confuse you, I tried to amend an old file that had all kinds of stuff in it and on it ) I just should've typed the file again, I means it not as if it had lots of data on it - what a wally eh!!
Thanks for your reply. I know I haven't explained myself fully.
On the last attachment (Moll(1)) What I ideally wanted was to select the batch number from the drop down list and then the table underneath to be populated with the data. i.e. Select batch DEF345 then the 6 columns would be filled with the matching data from the data on Sheet 2
This data (Sheet 2) is also updated everytime the file is run.
Filling "the 6 columns" -- this one does Rotn No./Product Description/Location.
I can't see where you'd be pulling "Allocated/Frozen/Available" from.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$E$12" Then Exit Sub
If Range("E" & Rows.Count).End(xlUp).Row > 16 Then
Range("E17:J" & Range("E" & Rows.Count).End(xlUp).Row).ClearContents
End If
For Each Cell In Sheets("Sheet2").Range("pt_data")
If Cell = Target Then
Range("E" & Rows.Count).End(xlUp).Offset(1) = Cell.Offset(, 1) Range("E" & Rows.Count).End(xlUp).Offset(, 1) = Cell.Offset(, 3)
Range("E" & Rows.Count).End(xlUp).Offset(, 5) = Cell.Offset(, 16)
End If
Next Cell
End Sub
Thank you - you are my hero. I've finally altered your code to suit my file (and learnt so much while doing it too!).
The only thing thats not working (you just knew there was going to be something else didn't you !) is clearing the previous enquiry - it just adds on to the previous enquiry....... does this make sense?
If I enquire on Prod A I would get three rows of results, if I then enquire on Prod B expecting only 1 result, I get the right answer but just added onto the end of the previous enquiry
I can see a line there to clear contents but nothing happens.
I can't get the previous row to delete it just keeps adding the new enquiry onto the end - even though I notice the code has a line in it to delete.
Any ideas - what I might have done?
Thanks very very much
Moll 45
Status
Not open for further replies.
You have insufficient privileges to reply here.
Related Threads
?
?
?
?
?
Tech Support Guy
9.9M posts
859.7K members
Since 1998
A forum community dedicated to tech experts and enthusiasts. Come join the discussion about articles, computer security, Mac, Microsoft, Linux, hardware, networking, gaming, reviews, accessories, and more!