Formula help

New Contributor

I have 3 very large data sets that I am actually working with but have scaled down for this example. Is there a way I can get the names from all of the tables to autofill into the given column (A, B, C etc) in the totals tab with the conditions that the adjacent box says Yes and it fits the timestamp week. I want this to not leave gaps or have to write a million long IF chain as this is a large data set. Macros are also not an option as Macros do not work well with shared docs which this is. Attached is a very small template of what I am working with. 

5 Replies


Here is a quick solution / approach

Here is the starting formula for the total sheet
=IF(AND('Table 1'!B2="Yes",'Table 1'!C2=A2),'Table 1'!A2,"")
...and copy down


Hope I could help you with these information / approach file.



I know I don't know anything (Socrates)



On large data sets Power Query could be a solution if you consider such option for your version of Excel.

Not a viable option as the large data sets would have massive gaps in it using that function.
If I may recommend, as Mr Baklan already recommended, for large data sets Power Query could be a solution.