Excel copy contents from one sheet to another without VBA/Macro

[mad2kx]

I've tried to use the search function but I can only find VBA related entries. I am looking for something without VBA. Maybe a simple formula?

OK I have a sheet with more than 8000 entries from Column A to L In each column there's an entry like name, date, region etc.

Then I have other sheets with Region, let say Region 1, Region 2 etc.

I want to copy the entries from the main sheet belonging to Region 1 to the Sheet Region 1, the entries from Region 2 to Sheet Region 2.

So for testing I created the following Index/Match formula

Please Login or Register  to view this content.

Sheet1 A:L = Area to search for
Admin E1 = Value to look for
Sheet1 F:F = Where to find the value to look for in main sheet

But when I pull this formula down into the next row, it gives me the exact same entry again (like duplication) and it does not continue looking for the next item in the sheet.

What am I overseeing here? Am I using the right formula?

Thanks for your help in advance!

[JLGWhiz]

Formulas cannot copy and paste. You have to use code to do that. However, formulas can put the values of cells from one sheet into another sheet. For example if you want data from sheet 1, row 2 columns A:E to be entered on sheet 2 row 5 columns B:F, then on sheet 2, cell B5 put

Please Login or Register  to view this content.

then drag that across from B5 To F5 and it iwill pupulate the range B5:F5 with values from sheet 1 range A2:E2.

[Gregb11]

It may help if you can attach as sample file (see yellow banner at the top of this page).

[mad2kx]

Thanks for the reply. Please find attached Spreadsheet. Not the same data but similar. Again I am talking about more than 8000 entries.

[Gregb11]

Try this formula as an Array Formula (use Shift+Ctrl+Enter instead of just enter).
=IFERROR(INDEX(Table_2[OrderDate],SMALL(IF(Table_2[Region]="East",ROW(Table_2[Region])-ROW(INDEX(Table_2[Region],1,1))+1),ROW()-1)),"")
This is for Cell A2, then copy it down. I've hard-coded the Region "East" but you can reference the sheet name of some cell in the tab if you want.
After copying this formula across, you'll have to change the first array after "Index".
The attached file has it done.

[mad2kx]

Hi there, I have found a better solution which actually seems to work. I am updating here that I am using the FILTER function.
Thank you again for your help

[Gregb11]

What version are you on? Your profile says V16. The FILTER formula (as far as I know) is not available in V16. That's why it wasn't suggested.