Solved: INDIRECT OR INDEX OR WHAT? I'M LOST

[agnesz]

Hi there -
I have a business overview report that I'd like to make as dynamic as possible. Attached is the actual report. It will be run weekly by different people. Depending on who runs it they will need to be able to get the data looked up from another report that they would have already run. This Business Review Report is supposed to vlookup values from what they have run. The caveat is that I don't know what these people will save their file as or what the tab names are going to be. I do know that the first 3 columns on left will always be constant. How can I get this thing to lookup the needed values from a document. I was thinking using indirect and having the people fill in their file name and the tab names, which is what the indirect would pick up, but I know that formula gets weird with large sets of data.
I hope this makes some sense to someone.
Please help!

[mdmackillop]

Can you also post a typical source file?

[agnesz]

Good call... I'll add the attachment on another post with the same name... I am allowed to only upload one file at a time.
thanks so very much

[mdmackillop]

You can add it in this thread, and you can zip files together.

[agnesz]

here's the lookup file
thanks again so much!

[mdmackillop]

Assumes source copied into same folder as report

Option Explicit
Sub Macro1()
Dim wsReport As Worksheet
Dim wbSource As Workbook
Dim Shts As Long
Dim ShName As String
Dim wbName As String
Dim i As Long
Set wsReport = ActiveSheet
Application.Dialogs(xlDialogOpen).Show ActiveWorkbook.Path
Set wbSource = ActiveWorkbook
wbName = wbSource.Name
Shts = wbSource.Sheets.Count
With wsReport
.Activate
For i = 2 To Shts
.Range("D2:I12").Copy .Cells(2, 4 + (6 * (i - 2)))
ShName = wbSource.Sheets(i).Name
.Cells(2, 5 + (6 * (i - 2))) = ShName
.Cells(4, 4 + (6 * (i - 2))) = Split(ShName)(0)
.Cells(7, 4 + (6 * (i - 2))).FormulaR1C1 = "=VLOOKUP(RC2,'[" & wbName & "]" & ShName & "'!R14C7:R700C18,7,FALSE)"
.Cells(7, 5 + (6 * (i - 2))).FormulaR1C1 = "=VLOOKUP(RC2,'[" & wbName & "]" & ShName & "'!R14C7:R700C18,8,FALSE)"
.Cells(7, 6 + (6 * (i - 2))).FormulaR1C1 = "=VLOOKUP(RC2,'[" & wbName & "]" & ShName & "'!R14C7:R700C18,9,FALSE)"
With .Cells(7, 4 + (6 * (i - 2))).Resize(6, 3)
.FillDown
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With
Next
End With
End Sub

[agnesz]

This is wonderful! I added a little MsgBox asking the user to select file prior to the dialog box opening up. Quick question though, I can't seem to adjust the code for it to work on more rows...I adjusted I12 to I200 and it doesn't work???

[mdmackillop]

This should make it dynamic regarding the number of rows. It uses Column C to define the last row


Sub Macro2()
Dim wsReport As Worksheet
Dim wbSource As Workbook
Dim Shts As Long
Dim ShName As String
Dim wbName As String
Dim i As Long
Dim Rws As Long
Set wsReport = ActiveSheet
Application.Dialogs(xlDialogOpen).Show ActiveWorkbook.Path
Set wbSource = ActiveWorkbook
wbName = wbSource.Name
Shts = wbSource.Sheets.Count
With wsReport
Rws = .Cells(Rows.Count, 3).End(xlUp).Row
.Activate
For i = 2 To Shts
.Range("D2:I" & Rws).Copy .Cells(2, 4 + (6 * (i - 2)))
ShName = wbSource.Sheets(i).Name
.Cells(2, 5 + (6 * (i - 2))) = ShName
.Cells(4, 4 + (6 * (i - 2))) = Split(ShName)(0)
.Cells(7, 4 + (6 * (i - 2))).FormulaR1C1 = "=VLOOKUP(RC2,'[" & wbName & "]" & ShName & "'!R14C7:R700C18,7,FALSE)"
.Cells(7, 5 + (6 * (i - 2))).FormulaR1C1 = "=VLOOKUP(RC2,'[" & wbName & "]" & ShName & "'!R14C7:R700C18,8,FALSE)"
.Cells(7, 6 + (6 * (i - 2))).FormulaR1C1 = "=VLOOKUP(RC2,'[" & wbName & "]" & ShName & "'!R14C7:R700C18,9,FALSE)"
With .Cells(7, 4 + (6 * (i - 2))).Resize(Rws - 6, 3)
.FillDown
.Borders(xlInsideHorizontal).LineStyle = xlNone
End With
Next
End With
End Sub

[agnesz]

perfect! thanks so much

[agnesz]

Hello again!
I seem to have a problem with the macro again. I edited it to match my new report and in the interim uploaded excel to 2007. In addition, the lookup report has hidden sheets that I would like the macro to ignore. The sheets will ALWAYS be in the same position, i.e. the first, and the last two will ALWAYS be hidden, so the macro shouldn't consider them in the formulas.
Can you help me again?