excel2016

  1. R

    Data extraction from specific rows of multiple workbooks using Power query

    Hello All. I've recently come across the power query feature in excel 2016 where in i got to know that i can extract data from multiple workbooks in a folder with actually having to open all those workbooks. Now my question is, not sure whether it's possible or not, Can power query be used to...
  2. B

    VBA Pivot filtering

    Hello guys I'm trying to do a pivot table using VBA alongside multiple other things, however I'm trying to filter the pivot table using the VBA to have account_number to be equal to 123456, I've done this code, however I don't know how to add the extra line to filter as mentioned above. Set...
  3. U

    VBA Find Text and Add Blank Column

    Hello Gurus, Hope all is well. Could you possibly help be on how to code this? 1. In Sheet1, I need to highlight in yellow the text containing "Target" in column AZ. 2. Separate the data with "Target" text by adding a blank column 3. Add message box noting how many "Target" text are there...
  4. T

    How to use OR with formulas

    Can someone help me please. As you can see in cells B17 and C17 I have a size with a ± value of 0.30 and column D is for the lowest size(110.50 - 0.30) and Column E is for the highest size(110.50 + 0.30). The formula that I am using works perfectly but that is only if there is ± but sometimes...
  5. P

    Generate excel files based on the values of a column

    Hi all, I have an excel file with two sheets 'Data' , 'Template' The 'Data' sheet has data in cells A4:AA6608. In column C, I have sorted the names of companies (but they are not unique). Moreover, I have a 'Template' sheet with header in A1:AA1 I have a button in 'Data' sheet and I want to...
  6. S

    Remember the last thing copied

    Hello ppl! I googled my problem before posting but couldn't find anything helpful so i decided to ask here. Is there an option to remember the last thing i copied and paste it after doing several actions in between ? For example > I have to copy some text and paste in one cell, than i change...
  7. S

    Update Chart SourceData in dynamic range

    Hi all, I am using Excel 2016 and want to update a "Box and Whisker" chart with new "Source Data" in a dynamic range. One workaround is to delete "Chart 1" and create a new "Box and Whisker" chart but how to update existing chart? I have tried below code and others but I can't get it to work...
  8. T

    AutoSaveOn issue with Excel 2013 and 2016

    Sorry if this is in the wrong location, this is the first time I am posting on this forum. My issue is a regarding a "Compile Error: Method or data member not found" that happens when an excel file is opened by some users. These users have either excel 2013 or 2016 installed. The compile error...
  9. C

    Need marco to extend table range to include new data in first column

    Novice in need of guidance! I run a macro that copies all cells with unique text from a separate work sheet and pastes them in the first column of a table. As this table has a load of formulas worked out using this column as a reference, I want the table to automatically extend to include the...
  10. D

    Intelligent autofill based on cell values.

    Hello, I have built a sales report dashboard which is to be updated monthly with around 2000 rows of input. The data I input has the following categories/columns: Date | Sales rep | Product | Quantity | Value | Product category | Brand. Sadly, the company's software does not export the...
  11. D

    Excel 2016 TEXT function ignores formatting

    Hello, everyone, I have a pivot table displaying the total sales of three brands in two columns (one as currency, the other as % of total). The currency column's numbers are formatted as currency, with thousands separator, two decimal places and "лв." after each number. For each of the three...
  12. L

    Make a more complex VLOOKUP formula

    Hi, I have twoexcel documents, "Book1" and "Book2". Book1 is exportedfrom our business system. Book2 is a compilation of forecast from the businesssystem data that were exported. Today Ihave to manually put in the data from Book1 to Book2. I want to automate thatprocess. In Book2 Ihave...
  13. C

    PowerPivot formula to calculate the number of active projects given start and end dates

    I'm new to much of this, so please bear with me. . . I'm trying create a count of active projects by week using projects' start and end dates. I've created a COUNTIFS formula in an Excel worksheet table that works, but I would prefer the calculated column reside in my Data Model/PowerPivot...
  14. F

    Excel2016: XML file. How do I use built in commands, instead of VBA scripts I wrote

    I created an addin that works fine. I wrote a bunch of code and assigned them to buttons. But I would like to add some buttons using built in functions like Add Row, Delete Row, Hide Row. How do I refer to these built in commands? Here is a line of my XML code: <tbody> <button...
  15. Q

    Problem with SendKeys

    I have a small vba code to copy data to another sheet - (data from SampleSheet1 {range(B17:W*) *is obtained from cell AA17} to SampleSheet2 {range(no change from last selected)} Dim Cval As Variant Dim Rng1 As Range Worksheets("SampleSheet1").Activate Cval =...
  16. M

    Barchart (Sales in Billions, and store numbers in thousands)

    Hi! As shown in the picture, I am comparing year end sales numbers (in millions) with stores numbers. Since Sales are in millions, the bar graph displays store numbers are more than sales. How can I display it so that it is easy to compare visually?
  17. T

    Bug: Pivot Tables: Sources: Can't change the source of Pivot-Tables anymore

    Hi, while I'm new to this forum, I'd call myself pretty advanced when it comes to Excel. But since some days I have a problem with Pivot Tables. I'm working with Excel 2016. When I try to change the source of a Pivot Table from one sheet to another, Excel just doesn't do it. It either keeps...
  18. B

    Is there a macro to conditionally copy data to another worksheet?

    At my work I have two databases with the same products. The first database contains all the products with all possible combinations (in this istance the database consists of motor exhausts, which are available in different colors and materials). The second database only contains the base...
  19. V

    Connect Excel 2016 to POSTGRE SQL database

    Hi everyone, I'm trying to connect my Excel workbook to a postgresql database. I've found this link on the internet but it didn't really helped me. https://support.office.com/en-us/article/Connect-to-a-PostgreSQL-database-Power-Query-bf941e52-066f-4911-a41f-2493c39e69e4 In fact, they are...
  20. A

    Excel Formula for an equation of permutation and combination

    I have various options in the C2 cell as A,B,C & D (using list command in data validation). I have various options in cell D2 as 4,5,6. So I want to make formula for cell E2 in such a way that when A and 4 is selected in options it gives value 0.1. If A and 5 is selected, it gives 0.2. If B & 1...

This Week's Hot Topics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top