Results for "data function"

Hello,

thank you very much for your attention

Regards,

Ferdinan

Hello,

Realy they are the same.

Use the one that seems the easiest to program.

I'm going to try both methods and see which is the most efficient?

Thank you very much for your help.

Regards,

Ferdinan

Hello,

I have a file in which I have many functions and the file goes a little slow.

Which of these methods is the most efficient, so that it goes faster?

Regards,

Ferdinan

Hello,

The best for you is to create a Name with this Formula

Select the Cells "E2" and copy the Formula (Select it in the Formula Bar)

In the ribbon Open the Name Manager

Add a new name (By exemple SUBSTFRM) and in the ReferTo area put the Formula

Send Ok

Now you can use "= SUBSTFRM" instead of the original function.

Then in VBA you can write :

Range("E2:E10000").Formula = "=SUBSTFRM"

That's enough !

See the exemple...

Good afternoon,

Please, can you help me with this problem?

In the attachment, in column E I use a search function (= INDEX (Sheet2! $ E $ 2: $ E $ 32; MAX (INDEX ((Sheet2! $ A $ 2: $ A $ 32 = C2) * (Sheet2! $ D $ 2: $ D $ 32 = D2) * ROW (Sheet2! $ D $ 2: $ D $ 32) -FILA (Sheet2! $ D $ 1);))).

I would like to load this function in that column E with a macro.

How should the programming of the macro be?

Greetings,

Ferdinan

Hello and welcome,

try this, select the data and execute this macro.

Sub createcolumnchartwithselection()

    Set ch = ActiveSheet.Shapes.AddChart2(366, xlColumnClustered)
    ActiveSheet.ChartObjects(ch.Name).Activate
    ActiveChart.ChartArea.Select
    ActiveChart.ChartStyle = 201
End Sub

Hi there, i am new to vb excel and i need help.

What would be the macro for making excel create time series chart if two columns are selected where the left column is dates and right column is data

I guess u can specify which folder to load files from at startup via the:

Advanced>General section of the Options dialog.

Also, any files stored here...

C:\Documents and Settings\user\Application Data\Microsoft\Excel\XLSTART

...will also be opened at startup.

My mistake

Now I saved as an XLAM file.

Does the file need to be opened to use the function in other files?

Hi,

The only way to do this is to save your file as XLAM, or please tell why you can't achieve this.

Hello everyone!

I wrote a custom function and I want it to work on any Excel file that I open.

How can this be done?

From an inquiry so far I understood that you can save as an XLMA file only that I could not

Thanks to the respondents

Good news.

If everything is ok, close the thread by clicking

Excactly what I needed, thank you very much !

Hello,

You may use Power Query.

1. Format your input table as Table (Ctrl+T when focus is on a cell from the table)
2. Then, position the cursor on the Excel table, Select Data > Get & Transform Data > From Table/Range.
   Excel opens the Power Query Editor with your data displayed in a preview pane.
3. In the Power Query editor, Select "ContactType" column, then Transform > Pivot Column.
   In the Pivot Column dialog box, in the Values Column list, select Value.
   Select Advanced options, and then select an Aggregate Value Function. In this case, select Don't aggregate
4. OK

Load the data in the sheet.

That's all.

PS : If you add, or change data in your input table, go to the result table and hit Data > Refresh

What I suggest is :

• Build a new tab called "Tables" in which we will find the valid terms depending to the choices made in tab "Navigation". Theses choices are determined by a formula at each step as for instance :

=sort(unique(query(Data!A:G,"select G where A='" & Navigation!A2 & "' AND B='" & Navigation!B2 & "' AND C='" & Navigation!C2 & "' AND D='" & Navigation!D2 & "' AND E='" & Navigation!E2 & "' AND F='" & Navigation!F2 & "' ",0)))

• Build a new tab called "Navigation" in which you can find data validation in cells row#2
• If a change of one choice has been done, I erase all the choices made on the right by this script

function onEdit(event){
  var feuille = event.source.getActiveSheet();
  var cellule = event.source.getActiveRange();
  if (feuille.getName()=='Navigation' && cellule.getRow()==2){

    feuille.getRange(2,cellule.getColumn()+1,1,7).clearContent()
  }
}

in A6 of "Navigation"

=IFERROR(sort(unique(query(Data!A:H,"select H where A='" & Navigation!A2 & "' AND B='" & Navigation!B2 & "' AND C='" & Navigation!C2 & "' AND D='" & Navigation!D2 & "' AND E='" & Navigation!E2 & "' AND F='" & Navigation!F2 & "' AND G='" & Navigation!G2 & "' ",0))))

Hope that is clear and fit to your request. Feel free to give me your feedback.

ok, now I will try to understand your project ! (may be this evening or tomorrow morning)

No problem Scilla, welcome in sheets-pratique.

However, access to your spreadsheet is denied. Please share as follows https://www.sheets-pratique.com/fr/cours/partage (you may be able to find help in English on the web).

I think I get what you're aiming for with the macro. So, when you open the file and select a cell like A7, you want the macro to grab certain cells from that row (like A7, C7, E7, G7) and paste them into a new workbook, right?