You may be a super hacker, but your users aren’t. All the things you take for granted in a quick query that joins a few tables is far beyond anything they’ll ever try to take on for themselve. Some of them are pretty good getting around in Excel, but only one in a hundred will touch something like Microsoft Access. So they come to you for every little thing.
So, in the interests of “faking” database functionality on the Excel side of things, here is a little macro code for looking up data in another worksheet and copying it over into where you’re working. It looks at the cell to the left of your current cell, then finds a row that matches it in a worksheet named “Data”. The row of data is then copied back into the current cell and the 9 cells to the right, but if data is already there it does NOT overwrite it. The scenario here is that someone sends a list of part numbers or customer numbers in an email and your manger types and power users can now have way to quickly append the usual bits of information that they need to really work with it. Note that in this case, individual users are assumed to be maintaining their own Data worksheets.
Sub DataPull() ' ' DataPull Macro ' Copy text from the "Data" sheet based on the cell to the left of the currently active cell. ' ' Keyboard Shortcut: Ctrl+d ' On Error GoTo DataPullError Dim Find As String Dim Cur As String Dim x As Long Dim y As Long If ActiveCell.Column = "1" Then MsgBox "Data macro looks up based on the cell to the left of the current cell.", vbExclamation, "Data Pull macro" Exit Sub End If Find = ActiveCell.Previous Do x = x + 1 Cur = Worksheets("Data").Range("A" & x).Value If Cur = Find Then For y = 0 To 9 Cur = Worksheets("Data").Range(Chr(66 + y) & x).Value If ActiveCell.Next(1, y) = "" Then ActiveCell.Next(1, y) = Cur Next y End If Loop Until Cur = "" Exit Sub DataPullError: MsgBox Err.Description, vbCritical, "Data Pull macro" End Sub
Note: I tried using VLOOKUP for something like this, but performance appeared to be awful for the size of the datasets I was working with. The “dumb” just-copy-some-data-in-here-and-don’t-worry-about-staying-in-sync approach is used here to eliminate the possibility of any lame slow-downs or program hangs that can be caused by a large number of VLOOKUP’s.