Membership database autofill

Hello George,

In short, yes, you can do this with either Array Formula, normal formula with some conditions (sorting, etc), or VBA - If you have a large amount of data / have too many sheets, VBA might be your best friend.

Other than that, can you post a small sample workbook with dummy data, showing how many sheets you have, what your data look like, and how you want it to look like?

Here is your attached file with INDIRECT as the main formula, with the help of 2 helper cells and one rule to make them work:

The 2 helper cells are right on the first row, in B1 and C1 of each sheet beside Data, returning the first row that has that category in the Data sheet, and how many rows belong to that category respectively (using simple Match and Countif)

The formula in A3 is

=IF(ROW()-2<=$C$1,INDIRECT("Data!"&SUBSTITUTE(ADDRESS(1,COLUMN()+1,4),1,"")&$B$1+ROW()-3),"")

A simple check abusing ROW(), I simply check if there are still rows to take in, then take it, from the Data sheet.
(My brain isn't working well atm so idk how to well-disect the formula, let me know if you want any particular part of the formula explained instead)

The advantage of using INDIRECT like this is that the performance will be top-notch even in the case your data sheet goes up to ten of thousands of rows or even more.
The downside is that, unfortunately, you have to sort Data sheet by Category.

Alternatively, you can achieve the same results using Array Formula (Require no sorting beforehand, but will struggle if you have a large amount of data) and VBA (simply great for 99.99% of problems in Excel, but, yeah, macro, IT policies, etc)

Weird, I just tried downloading it twice, and both of them work on my end.

Try pasting these formulas in then:

In any sheet from 2 to 4, "Blue", "Green" and "Orange"

B1: =MATCH($A1,Data!A1:A29,0)

B2: =COUNTIF(Data!A2:A29,A1)

A3: =IF(ROW()-2<=$C$1,INDIRECT("Data!"&SUBSTITUTE(ADDRESS(1,COLUMN()+1,4),1,"")&$B$1+ROW()-3),"")

Drag the formula in A3 to C3, then drag it down until you start seeing "Blank"