Vlookup or sumifs

[bpolkusm]

Hi am trying to find the best way to write this formula.

on the administration tab I have Values in A1-A2189 each cell has a value similar to these AT.55 AT.56.
I have 281 tabs named Process1, Process2 and so on 281 times.
On the Process tabs there is a Row Named Traceability Matrix. and This Row 7 is divided in to 12 columns F7-Q7. With in these columns they type the value from Administration tab that corresponds to the process So F7 would have a value of AT.55 G7 AT.56 others may have values or be blank depending on the process.

There is another tab named Index. I am looking for away that if a value is entered on the Process1 with in Columns F7-Q7 those values will land in Row C2, looking like this AT.56,AT.55,


Any help will be greatly appreciated.

 

[AFPathfinder]

So, if I understand correctly, each Row 7 (Traceability Matrix row) of each Process sheet references the Administration sheet. The Process sheets themselves don't have "AT.55 AT.56" hard typed in row 7, they are just referencing the value on the Administration tab? And, you want the data to be concatenated on the Index sheet from the 281 Process sheets?

I don't know about the limitations of references inside a function/formula, but I'd have to believe it would severely affect the performance of Excel while making changes in that workbook. I would suggest one of two options.

1. Tie a VBA macro to a button to where you can select when you want the workbook to update the values on the Index sheet. You'd need to create a macro that performs the manual concatenation and I'd suggest using arrays to keep the execution time down.

2. Brainstorm the whole process and see if you can streamline down to fewer sheets and simpler formulas. It may not be feasible, but I'd see about making one or more large tables out of the Process sheets to give you more options on using Excel's analytical tools.

Option 2 is a lot of work, but can be very worth it in most circumstances. If you think the new solution would cause learning issues with other users, you can build userforms to aid users in navigation and performing tasks on that data while maintaining the integrity of your data. Most of these solutions end up as makeshift databases that would be better served in Access, but the end result is usually more efficient and accurate.

 

[bpolkusm]

Hi,

Vlookup or sumifs may not be the best way in simple terms I would like what ever is typed in F7-Q7 on the Process tab be placed in the field C2 on the Index tab.

 

[AFPathfinder]

Here is a macro example (untested) that concatenates all data found in F7:Q7 on all sheet names that start with "Process" and places that data in cell C2 of the Index sheet. It will run each time the workbook is opened, but you can move the macro to a separate module and hook up to a button if you wish.

Private Sub Workbook_Open()
    Dim i As Integer
    Dim sIndex As String
    Dim sht As Worksheet
    
    For Each sht In ActiveWorkbooks.Worksheets
        If Left(sht.Name, 7) = "Process" Then
            For i = 6 To 17
                sIndex = sIndex & sht.Cells(7, i).Value & ","
            Next i
        End If
    Next sht
    
    Sheets("Index").Activate
    Range("C2").Value = sIndex
    
    MsgBox "Done."
End Sub

When looking at this, there could be an issue with the string length reaching the 32,767 limit of a cell. I'm guessing all that data isn't going into a single cell on the index sheet?