Is it possible to mimic excel rows in a different

问题:

So in 'Sheet 1' I have 2000 rows of information which I add a new column to every day, I want to have only a subset of the rows of 'Sheet 1' in 'Sheet 2' which update themselves as I put new columns into 'Sheet 1'. Is this possible to do using inbuilt excel functions?

回答1:

If you want to select a set of particular rows and you know their row numbers, put their row numbers (1,5,8,11,12 ...) in a separate 1 column range and name this range e.g. "RowNumbers"

Then use following normal non-array formula which you can drag downwards and rightwards.

= IF(INDEX(RowNumbers,ROW(A1)+1)>0, INDEX(NamedRange,INDEX(RowNumbers,ROW(A1)+1),COLUMN(B1)+1),"")

You will probably need to adjust the +1 parts, depending on whether your values start at row 1 or 2 etc.

Basically the Excel INDEX formula does what you need - copies the value from another sheet or range by given row and column numbers.

---

Otherwise you can use following array formula (Ctrl+Shift+Enter) to select filtered values from columns based on multiple criteria:

 = IFERROR(INDEX(NamedRange,
   SMALL(IF((INDEX(NamedRange,,1,1)=1)*(INDEX(NamedRange,,2,1)="A"),
   ROW(NamedRange)-MIN(ROW(NamedRange))+1,""),
   ROWS(C1:$C$1)),3,1),"")

Here is an example data sheet (Sheet1)

Here is the filtered data output sheet. You should enter the array formula in the first row and drag downwards to fill expected output range. In this example I select only rowns that have values 1 and A ind filter1 and filter2 columns.