Using {} in Excel

Question

Hello friends,I hope you are doing well,I would like to know the meaning of the braces in Excel formulas, and when can we use them?Also, if you could guide me on how to automate the extraction of several rows from an Excel table to a range that I define and based on criteria that I set;Thank you in advance;Configuration: Windows XP / Firefox 3.5.13

m@rina · Accepted Answer

Hello,

We use {} to create array formulas. We don't do them by hand; you need to validate with Ctrl+Shift+Enter.
All the explanations here:
http://jacxl.free.fr/cours_xl/cours_xl_jac.html#matricielles

For your second question, you can use advanced filters: Data, Filter => Advanced Filter.
First of all, you need to create your criteria ranges.
Look for example here:
http://www.ybet.be/formation_excell/13-filtre-elabore.php

m@rina
--
- "On the office forum, people ask questions regarding office software..."
- "Really???"

Patrice33740 · Answer

Good evening,  Question 1: Braces { and } around a formula signify that it is an array formula like {=H12:J17}, meaning it is a formula whose results are returned in a matrix of cells (and in some special cases, in a matrix consisting of a single cell).  To write an array formula, you start by selecting the range of cells intended to receive the results, enter the formula (just like for a standard formula) and confirm with Ctrl+Shift+Enter.  Question 2: To extract data (or rows) from a database, refer to these Examples of data lookup  Best regards Patrice -- Nicolas always says: "It's easy when you know the answer!"

tontong · Answer

Hello, Braces are also used to define matrix constants. In this case, we type the braces. The Excel help describes this usage well. We often encounter this notation in charts.

temoin_ch · Answer

Hello,

First of all, thank you everyone, your answers are great;

As for extracting the rows, I don't know in advance the number of rows that meet the criteria, you understand? I set my criteria and I want an output table composed of the rows that meet the criteria and an automatic method because checking line by line is very time-consuming and I repeat this every day;

Thanks in advance;

temoin_ch · Answer

Hello friends, I am still waiting for your reactions !!!!!!  Thank you

Patrice33740 · Answer

Hello,  It seems to me that m@rina's excellent response regarding advanced filters meets your needs!  Why doesn’t it work for you?  Patrice -- Nicolas always says: “It's easy when you know the answer!”

Patrice33740 · Answer

Good evening,

Assuming that:
- the data is on the "Data" sheet
- row 1 contains the field names
- the criteria are on the "Criteria" sheet
- row 1 is identical to that of the "Data" sheet

You just need to place a button on the criteria sheet that executes this code:

Private Sub CommandButton1_Click() Dim rngDonnees As Range Dim rngCritere As Range Set rngDonnees = Worksheets("Data").Range("a1").CurrentRegion Set rngCritere = Worksheets("Criteria").Range("a1").CurrentRegion Worksheets("Data").Activate rngDonnees.AdvancedFilter Action:=xlFilterInPlace, _ CriteriaRange:=rngCritere, Unique:=False End Sub

You can also place a hyperlink on the data sheet that leads to the criteria sheet, and the loop is complete!

Best regards
Patrice.
Nicolas always says: "It's easy when you know the answer!"

Patrice33740 · Answer

Good evening,

A small modification to get the result on another sheet:

- the data is on the "Données" sheet
- row 1 contains the names of the data fields
- the criteria are on the "Critères" sheet
- row 1 is identical to that of the "Données" sheet
- the result is sent to the "Résultat" sheet

Add a button on the criteria sheet that executes this code:

Private Sub CommandButton1_Click() Dim rngDonnees As Range Dim rngCritere As Range Worksheets("Résultat").Cells.Clear Set rngDonnees = Worksheets("Données").Range("a1").CurrentRegion Set rngCritere = Worksheets("Critères").Range("a1").CurrentRegion rngDonnees.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=rngCritere, Unique:=False rngDonnees.Copy Destination:=Worksheets("Résultat").Range("a1") If Worksheets("Données").FilterMode Then Worksheets("Données").ShowAllData Worksheets("Résultat").Activate End Sub

You can put a hyperlink on the data sheet, row 1, after the last used title, which links to the criteria sheet. It will then be copied to the result sheet.

Patrice
--
Nicolas always says: "It's easy when you know the answer!"

Using {} in Excel

8 réponses

Windows spotlight is not working.

Masked destination layer

Samsung j5 unable to answer

Scam of the suvedene highway

File name too long

No sound with ps3 and hdmi

Wake up from sleep mode in windows 10.

Internet connection problem with my ipad

Free box: remote control broken

Suspicious number text message