Using HLookup/Vlookup across multiple worksheets when adding new worksheets continually

[linthi21]

Help! I know I saw the answer somewhere online and cannot find it anywhere.

I want to have a Summary List page in my workbook that uses HLookup to find a code and bring back data from multiple worksheets. The issue I am having is that I am going to be continually adding new worksheets and want the summary page formulas to apply to all newly added worksheets. The newly added worksheets are not consistently named so I don't want to have to update summary page formulas every time a worksheet is added.

I thought I saw someone on a forum say that I could reference a range of worksheets and all sheets in between would be included in the lookup. Then, if I added new sheets, just always make sure I add sheets in between. I believe it used something about the letters a-z to ensure everything was considered that was in between?

Can someone help me? I wish I had bookmarked that post!

 

[Johnny C]

It's not straightforward, but doable. You do a SUMIFS across the sheets using SUMPRODUCT as SUMIFS only does one sheet.

Create a list of all the sheets, I've called it Control below, no blank cells in the list (I've got col B below). Every time you add a sheet, add the sheet name to the list.

You then create a 'dynamic range'. Shift F3 to bring up the name manager, add a new one, call it ListSheets for e.g. and in the refers to put

=OFFSET(Control!$B$2,1,0,COUNTA(Control!$B$3:$B$200),1)

where Control is the name of the sheet with the list, and the names of the sheets are in col B from row 3 down. Amend as needed for your sheet name and the column and number of rows, I've got 200 in there but change that if you need more. That creates a range with all the sheet names, where the range is only the cells with data in.

Then when you want to collect data in the summary, you need a formula like this
=SUMPRODUCT(SUMIFS(INDIRECT("'"&ListSheets&"'!"&CELL("address",F9)),INDIRECT("'"&ListSheets&"'!$Y$2"),$L$1))
where F9 is the cell you want to add up on each sheet and $Y$2 is the cell in each sheet you want to effectively do a VLOOKUP on, and the data to match on is in cell $L$1 in the sheet with the above formula in. Obviously you'll need to change the cell references.

 

[linthi21]

Thanks so much for the info. The issue with this solution is having to add a sheet name to the list every time I add a sheet. I'm making this worksheet for use by others with very low Excel skill sets so modifying formulas is going to be too much

Any other suggestion that would automatically include the new sheets in the lookup function?

 

[Johnny C]

An option would be to use VBA to create the list and insert a button they can press that is assigned to the VBA. They would just need to click a button. I would create 2 lists, one of sheets not to be included and one of sheets to be included. The one of sheets not to be included would still be manual for you to update. The VBA could cycle through all sheets, check if each sheetname was on the exclude list and if not add it to the include list.

Say the excludes are in column D and includes are in col B starting at row 3, the VBA would look something like this

Sub SheetList

Sheets("Control").Range($B$3:$B$500).ClearContents
shtCounter = 0

For each sht in ActiveWorkbook.Sheets
    shtName = sht.name
    on error resume next
    x = worksheetfunctions.match(shtName, Control!$D1:$D100,0)
    if err.number <> 0 then
        On Error Goto 0
        shtCounter = shtCounter + 1
        Sheets("Control").range("$B$"&(shtCounter+2)).Value = shtName
    End iF
    On Error Goto 0
Next sht

End Sub

I haven't debugged that so if that's what you want give it a whirl and post back if you get stuck