Hi everyone,
I've resisted joining the office forums trying to figure things out for myself.
But I think this is an easy one.
I have sheet one which contains unique valuse in a colum
I have sheet2 which contains multiple valuses of each occurance ( which match the sheet1 colum) in one colum.



Eg sheet1 col B contains :
eggs
apples
oranges

Eg sheet2 colum B contains
eggs
eggs
eggs
oranges
oranges
oranges
apples
apples
apples

So the sheet2 colum has about 9000 entries whilst sheet one has about 900 unique values corresponding to values in sheet2.

I want to be able to click on a value in sheet one and after that click it will search sheet two and go to the first occurrence of that value in sheet2. I don't want to use buttons, but i need to simply click on the value in the cell and it will take me to that value in sheet2.
any help would be fantastic,
mike

What identifies the first record? A date? A serial ID? Anything?

Would it suffice to just show pertinent information from that first record on the first sheet or do you need to actually go to it?

Hi Gebobs, all the records in sheet one are uniqe and in one colum. There are 900 records and on the second sheet there are thousamds. Just need a quick way if some looks at sheet one they click on the cell and it earches sheet two for that reocord which has other information in another colum next to it. But all the records in col b on sheet one have an exact recors in sheet two. Just thast in sheet to there are many recors of the same information if that make sense.
eg
sheet one Col B
test
test1
test2
test3
test4

sheet 2 colum b has these records but many multiples of them

Yes, I understand that it is a one-to-many and the key is the unique values on the first sheet. What I don't understand is what identifies that first record on the second sheet. Is there nothing special about it other than its position? Is it first because its oldest or newest?

I understand you want to click a record and then go to the first record on the second sheet that matches it. What I'm trying to figure out is if there's an alternative solution to that. That will require code for sure. But its possible we may accomplish what you want in some way that does not require code.

Using your example, you have the record 'eggs' on the first sheet. On the second sheet there are multiple records of eggs. Why? Because these are inventory bins. Each bin has lots of information such as capacity, quantity on hand, etc. And each bin has a date that the eggs were laid and for apples when they were picked, etc. This is important since we want to maintain FIFO.

So the second sheet (in my completely made up fantasy world) has a structure of:

Column A: Item (eggs, oranges, apples)
Column B: Date picked, laid, harvested, etc.
Column C: Capacity of the bin
Column D: Current on hand quantity of the bin

The data are ordered by Item then Date so the first record in the sheet for each item is the oldest one. So you say, hey, it would be nice to just go click eggs on the first sheet and have it take me to the first record on the second sheet so I know what bin to go to to get the eggs that have been sitting on the shelf the longest.

Then I say, how about we just have another field on the first page that gives the bin number of the first record because that's what's really important? It's easily done and doesn't need any coding.

You say, that's great, but can we also get the date and the quantity on hand too.

I say sure, no problem, in fact, we can get any information you want in that record just so long as we know which record you want i.e. the oldest record. Why don't you head over to the pub for a beer and I'll meet you there in 10 minutes after I finish this?

You say, that's nice, but I'd rather do the clicking thing.

I say, whatever you want. Give me a couple days.

Hi Gebos.
To answer a few items here. There is nothing special about the data or position.
The second sheet does have colums next to colum B or other information. What identifies the record on the second sheet is the exact same text as in the first sheet.
eg apples on the first sheet and many apples on the second sheet. But besides apples on the second sheet there is other information. But the important thing here is to ignore that, just so long as when i click on the first sheet say in the apples cell it will do a search for apples on the second sheet. thats all. I'll try to insert an image to show. ok i uploaded, but who knows where it went. If you see the pic, if i click on oranges in sheet one, i want it then go to sheet two to the first occurrence of oranges it finds which is sheet2 B14

Attached Images

excel example.png (37.7 KB, 17 views)

an example

Attached Images

excel example2.PNG (46.6 KB, 17 views)

I get that part. I have gotten it from the first. You don't have to repeat it.It's obvious that you either didn't read or didn't comprehend my post.

The question, if it hasn't been made abundantly clear after three exchanges, is why you want to see that first record.

If you are hell bent on the click routine, post it in the VBA forum. That's what you need. I'm going do bet that it can't be done.

When I say: "What I don't understand is what identifies that first record on the second sheet", I know that the connection is to apples to apples and eggs to eggs. What I don't know is what is the secondary key...i.e. what makes the first record...well.. the first record.

If you don't understand my questions, and it is clear that you don't up to now, then I probably can't help. Sorry.

Forget these games. This is getting nowhere. Attach a sample of your actual sheets.

Thanks for your help gebos, i don't think your attitude is helping here. I'm not going to post the original excel doc as it has sensitive data in it. Mate, whatever you think on games - where did that come from mate. Please don't respond any further, and i hope there is someone else out there that can assist.

I explained to you in no uncertain terms how I understood your question and yet you persisted in explaining it again like I didn't. Twice.

And I tried to break it down for you, quite courteously and with what I thought was a bit of humor, and you obviously either didn't understand or didn't even read it.

Good luck!

I'm sorry if I've offended you, but it's frustrating to try and solve a problem with descriptions only. Truly, post #4 was an honest effort to delve into the problem. I invested a lot of time in that post. Please read it once more and see if it makes sense.

Then, let's put the past behind and see if we can't solve this. I honestly didn't mean for you to take my "games" comment personally and see how it came off that way. In retrospect, I should have said it differently. But the "game" isn't limited to you. Many people post here without attaching their work. It so much harder to work on a problem solely by description.

Solved though i use a command button with the embedded macro :
ok so i searched other forum and came up with answer.
Below is the code attached to a command button,

Sub FindCellContent()
Dim Sh As Worksheet, foundCell As Range

'Search for CellContent in all Visible Worksheets
For Each Sh In ActiveWorkbook.Worksheets

If Sh.Visible = xlSheetVisible Then
'If you want to ignore the active sheet unmark the below line
'If ActiveSheet.Name <> Sh.Name Then
Set foundCell = Sh.Cells.Find(What:=ActiveCell, _
After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext)

If Not foundCell Is Nothing Then
If foundCell.Address(External:=True) <> _
ActiveCell.Address(External:=True) Then _
Application.Goto foundCell: Exit For
End If

'/If you want to ignore the active sheet
'End If
End If
Next
If foundCell Is Nothing Then _
MsgBox ActiveCell & " not found in this workbook"

End Sub