formula

  1. L

    Making row1 as Header for all the queries

    Hi Experts, I have 45 queries data loaded into power query, I need to promote row 1 as header and change column name to “Data” for all the 45queries Instead of doing each query individually, Can I have M language script to do the above at one go? My data sources: It is an excel, with 45 tabs, I...
  2. L

    Nested or formula combinations

    Hi Guys, Columns B, C, D, and E contain a list look-up (Yes or No) Earlier today, you guys helped me that if column B and/ or E has "Yes", to return certain text (Thank you!) I placed the updated functionality in column F. Then I wanted the user to identify the scope of work based on the...
  3. F

    Calculated measure in Pivot Table

    Hi! I´ve been trying to get a measure in my pivot table to get the % of reject orders of a partner / Total order order of partner, and that it can change if I use a slicer to change it by day, month, etc. This is an example of the data table which I use: And this is the pivot table which I...
  4. J

    Calculate cells edited in last 7 days

    Hello, To give a bit of background: I'm one of the editors on a website currently hiring writers. We're creating a spreadsheet to track the content the writers create and when they created it. I want to create a count for the number of blog posts they've written in the last 7 days, if that's...
  5. D

    SUMIF with multiple conditions + Wildcard in Name

    Hi All, May you help me with below? Name in Column "H" will be the lookup value vs "A" and "D". Other condition is in the table:
  6. K

    My table formula won't grow with the table

    I have a table that I have saved as a Template. When I add the template to a new workbook the first calculated row works but nothing after that. I can add data and the table will grow but the formula won't follow it. Is it because it's a template? Why is this happening? I can't use xl2bb because...
  7. Major Aly

    Calculating Two Different Ranges of Working Hours Between Two Dates

    Hello, I've run into dilemma which I though I would overcome easily but ended in getting different results. So I've been using the formula provided by Barry Houdini here: Formula...
  8. S

    Convert a 12 month date to a single cell

    Hello, I have in cell A2 Date in format 18-Nov-2020 UTC I Need format 18.11.2020 in cell B2 I created this formula, but it probably won't be the right way: =(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,"Nov","11"),"-",".")," UTC","")) Dates change, so Jan = 1, Feb = 2, Mar = 3, Apr = 4, May = 5, Jun =...
  9. R

    Macro to get data in the below format

    I have data in the below format - Col A1 Col A2 Col A3 Col A4 Col A5 Col A6 Col A7 Col A8 Col A9 Col A10 Col A11 Col A12 Col A13 Col A14 Col A15 Col A16 Col A17 Col A18 A B C 1,2,3,4,5,6,7,8 D E F G H I J K L M N O P Q I need it to be this way - Col A1 Col A2 Col A3 Col A4 Col A5...
  10. S

    How To Assign A Value Based On A Number Range In Excel

    Hi guys, I have a set of data as shown below which has from and to range and a corresponding value- Input Data Range From Range To Value 0.00 46.35 3.8707 46.35 72.17 2.4149 72.17 458.61 2.4149 458.61 486.38 4.2 486.38 631.56 10.1 I want to compare from and to range...
  11. V

    Highlight visable cells that are different in a pivot table.

    Hi all, Can I please get your help. What is the formula to achieve the following on a pivot table. Highlight cells which are not the same value by ignoring only blanks cells and cells with a single value. I only need to highlight cells where the number appears more than 2 times and when its...
  12. M

    Combine non-blank items in two ranges (unknown number of values) into a new list

    Need help creating a combined list from values in two ranges. All non-blank items in list 1 should be included before adding the non-blank items in list 2. The headings between and after these lists should not be included in the resulting list. The challenge I'm having is that I don't know which...
  13. S

    VBA Vlookup code in excel

    Hi Everyone, i need support to write a excel vba code on vlookup of all sheets with different name from first sheet name"Sheet2" and put the information accordingly. number of sheets can vary. number of rows in every sheet can also vary. master sheet"sheet2" cloumns detail can also vary. all...
  14. R

    Need Help On A Formula

    Hello Experts, Again, i am seeking your help on a formula which breaking my head. I almost wrote the formula but it is not working as expected. Initially what i did is, In column A (from cell A2) i just used the Length formula to get the length of the product from column B (from cell B2)...
  15. J

    Count Cells with Specific Text in Multiple Columns without Double Counting

    I'm trying to write a formula that will count the number of rows that contain "Yes" or "No" in multiple columns. However, if more than one column for the same row contains "Yes" or "No" I only want the row to be counted once. Here's what I've tried: =SUM(ISNUMBER(MATCH(Data!$AQ$3:$AQ$1000...
  16. A

    Give back the most/least profitable product

    Hey Guys/Girls, I need some help with getting back the most profitable product in a single cell and a least profitable in another. I had a working formula but it didn't worked with empty spaces. I have a summary sheet and a second data sheet where i store all the information. On the summary...
  17. S

    Next 5 April from any given date in Excel

    Hi All I'm trying to return the next 05 April from any given date, with the cell A11 holding the date. I was using this: =IF(MONTH(A11+1)=4,A11+1,DATE(YEAR(A11)+1,4,5)) Which works fine unless the date I'm using is a month less than 4 (eg jan,feb,march,April) as it will then return to me the 5...
  18. hayleyg123

    Vlookup between two dates, fix ignoring 1st and last of the month

    Hey, newbie here ☺️ I have a sheet where I want to return the job value (Column H) to a certain column depending on it's expected order date (Column T) So I have this for my formula... =IF(AND(T8>$W$2,T8<$W$1),H8,"") However, if the expected order date is on the 1st or last day of the month...
  19. S

    How to get the date and insert as text in the cell

    Hi everybody. I have a formula: =SUBSTITUTE(SUBSTITUTE(B4;"No";"Nein");"Yes";"Ja") in the cell but I can't figure out how to get the date and insert as text in the cell. In cell B4, the date is in the following format: dd.mm.yyyy In cell C4 must be like text because then, we copy this data via...
  20. S

    How to parse data from a cell

    Hello. What formula would I use to parse the following. JC-RO-06-L367-E201-J166-MK5C40-M14XT The number could and can be different, but the section I'm looking for the characters following the MK. So in this example I would want the formula to return 5C40. Another example...

Watch MrExcel Video

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