Hi I hope you can help me once again. I have a file where constantly have to fill with expenses. On the left side are the accounts and on top the departments (kind like a pivot table).
I have to fill the file with info from another file pivot table (also account on the left side and departments on top) but on different order. Since not all the accounts on my report are on the original pivot table I use a vlookup to get the account and a hlookup to get the department. Then on the original Pivot table I put just below the last line a number that indicates the column number for the department is that way I get the column number for the vlookup.
Ex. “=vlookup(Account,OrgTable,hlookup(Dpt,OrgTable,x,0),0)” where I put of the Org table
I would like to find an easier and faster way to do this, since it is done frequently. i found a macro over the internet that works for a vlookup but how can I, either add a hlookup to this vlookup or tell the formula to look for the position of the department on the pivot table in order to set the column number for the vlookup.
Sub MatchValue()
Dim rListOne As Range
Dim rListTwo As Range
Dim iColDiff As Integer
On Error Resume Next
Set rListOne = Application.InputBox _
(Prompt:="Select the list WITH values, including the values. Don't include blank cells or headings", _
Title:="OzGrid.com", Type:=8)
If rListOne Is Nothing Then End
Set rListTwo = Application.InputBox _
(Prompt:="Select the list WITHOUT values. Don't include blank cells or headings", _
Title:="OzGrid.com", Type:=8)
If rListTwo Is Nothing Then End
iColDiff = WorksheetFunction.Max(rListOne.Column, rListTwo.Column) _
- WorksheetFunction.Min(rListOne.Column, rListTwo.Column)
rListTwo.Offset(0, 1).FormulaR1C1 = _
"=VLOOKUP(RC[-1]," & rListOne.Address _
(ReferenceStyle:=xlR1C1) & " ,2,FALSE)"
rListTwo.Offset(0, 1) = rListTwo.Offset(0, 1).Value
Set rListOne = Nothing
Set rListTwo = Nothing
On Error GoTo 0
End Sub
MHT
