EXCEL - Need a bit of help...Anyone handy with it?

[ben_p]

Hi Guys.

This is a pretty basic sheet but I need to know if the following is possible. I need to search the columns A & B (If it can only search A then that’s not a huge problem) there will be addresses such as "Victoria House" in the column A then they can edit a key tag for column B.....As we hold around 100 keys per office I would need them to search (I cant put them in alphabetical order as its already mixed up and I have to manually resort them each month lol.

I put a mock box where I would want it to go but need to know if I can code/link it to search?

I know I can press CTRL + F but I’m not sure the staff could manage that. lol

Cheers

 

[ben_p]

Anyone (you bunch of c***s)

 

[FlameR]

VLOOKUP?

So if someone enters for example 'Victoria House' (has to be exactly as it's written in the table) then it will return the value in the Key tag column.

Failing that, just do a macro! When you enter the text into a box, click the macro button it will automatically copy the text, CTRL F, paste text and search

 

[Sym0n]

Could you not just use auto filters Ben and cut out the typing totally? Even your 'tard staff should be able to deal with a drop down box and picking what they want.

 

[ben_p]

showz me how to do that?

 

[SC03OTT]

Highlight all the columns with data, then click on the sort & filter button (top right) and select filter. It seems to be quite different from Office 97 though...it might still even be a bit overly complex. There are quite a lot of options in the drop down you get.

 

[ben_p]

Sorry for being a thicko, but how does this let me search? it just seems to let me order then alphabeticaly

 

[SC03OTT]

If you click on the drop down list, then select filters, then equals, you can type what you want into the search box and it will find it. Like I said, it seems to be very over complex from what it used to be like. Maybe not ideal.

 

[Adam]

Just stick with what you do best.....

Spending money on cleanyourcar.com lol

 

[ben_p]

 

[Lagerlout]

f**k excel tbh.

I can just about manage a spreadsheet to keep track of the tea/coffee/condiments kitty lol

 

[ChrisPearson]

not exacltly sure what you need to do but wouldnt access be better?

 

[Adam]

not exacltly sure what you need to do but wouldnt access be better?

Yes it would, but I have told him just to make do with using the auto filter feature and selecting which property he wants.

 

[JoedotwasT5phy]

I'm confused.

 

[ben_p]

where can I get access?

If someone makes this Ill pay them £100

 

[bmit]

try this?? You will need to add the text below as a macro. and then assign it to a button to click. It will return the location of what you search for... Now where can i collect my 100 bucks!

Sub FindAll()

Dim strWhat As String
Dim rngFind As Range
Dim strBuf As String
Dim strFirstFind As String
Dim intCount As Integer

strWhat = InputBox("Enter text to find in Sheet1")
If strWhat <> "" Then
With Sheet1.Range("A:IV")
Set rngFind = .Find(What:=strWhat, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not rngFind Is Nothing Then
strFirstFind = rngFind.Address
Do
intCount = intCount + 1
strBuf = strBuf & rngFind.Address & vbLf
Set rngFind = .FindNext(rngFind)
Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstFind
strBuf = strBuf & vbLf & intCount & " occurances of '" & strWhat & "' found."
Else
strBuf = "No occurances of '" & strWhat & "' found."
End If
End With
MsgBox strBuf, vbInformation, "Find All"
End If

End Sub


Or a much easier solution.....

put this without "" in the cell you want to display the result in "=VLOOKUP(H8,A:C,2,FALSE)"

Then in cell H8 (or whatever you want it to be, just make sure it corresponds with above) enter what you want it to search for. The cell with the formula will now show you whatever the corresponding tag number is in column B.

You wanna be locking the cell with the formula in so no r****d deletes it. it will show #N/A if there is no matches found.

 

[iain187]

Step 1: pick the cell where you want to enter the address and go to the data tab.
click on data validation, then data validation again.
from allow choose 'list' and then in source click that little button and highlight all your addresses.

step 2
write a vlookup formula -
=IF(G4<>"",VLOOKUP(G4,A:C,2,FALSE), "")

if(G4<>"" -> that means if the cell with your address isn't empty (because if it's empty it comes up with N/A which is untidy)

VLOOKUP(G4 -> do a lookup on what is in that cell
,A:C -> compare with whats in A:C
,2 -> retrieve what is in column 2 of A:C
,FALSE i think this means exact match which you want because of the validation you created...

step 3, select an address and see how you get on

 

[Steve 182]

You could do it best in access but a pivot table or the vlookup would do the job.

When do you need it for as I am local an I could come an do it if you haven't solved it already??

 

[ChrisPearson]

where can I get access?

If someone makes this Ill pay them £100

Ill do you one in access tomorrow

 

[ben_p]

Ill do you one in access tomorrow

The 100 pound was a joke btw

 

[ben_p]

OHHH Arse! I just thought, If one member of staff edits it on one desk in that office... then I wont change on the others in that office

 

[ChrisPearson]

lol

why not, if its saved into a public folder it will.

 

[ben_p]

ohhhhh