1. K

    Query Editor - Issue with coverting date format when year changes when date format is Day DD MMM e.g Sat 28 Dec At the switch over to 2020 it just shows as an error using the following #"Changed Type" =Table.TransformColumnTypes(Data0,{{"Column1", type date}, Is there an way to handle this?
  2. D

    Subtracting times, negative result

    Hi, I'm sure this is quite simple but I'm not getting it. I want to subtract to time values where the result (in this case) will be negative: Time A is entered manually as e.g. 13:30 Time B is a result of a calculation and shows as 16:00 The result should be -2:30?? I've tried different ways but...
  3. S

    ComboBox selected record’s all columns visible?

    Hi all, After DropButton Clicking, it shows all CoboBox’s records with columns visible, but after selecting a record from list, CoboBox shows first column’s value only, rest columns are hidden. Is there a way to show selected record’s all columns? Thank you in advance
  4. H

    Getting Unique Values from List without VBA

    Hi, I'm using Excel (Microsoft 365 on a Mac) I'm using the formula =IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"") B1 = Fruit, B2 etc = Apple, Orange, Apple, Banana, Banana, Apple, Pineapple, Peach D1 = Unique, D2 has the above formula I copied the example from...
  5. E

    Finding cash sales for specific cashier question

    Hello and thank you in advance for the help. How can I accomplish this with a formula. Here's the situation. In my spreadsheet column A shows the cashier ID and column C is supposed to show the cash sales for the cashier. Over to the right on the same spreadsheet column J shows the different...
  6. A

    Show results based on 3 Data Validation list

    Hi community, I have big table of monthly spending, i wanna know how can i show results based on Data Validation list choice, 1-List shows Models ( E:E ) 2-List shows Department ( B:B ) 3-List shows Month I need formula to return matching all 3 list, can anybody help me please?
  7. masud8956

    Listing holidays separately and finding values in aggregate

    Hello experts! I don't know if it is too much to ask. I have a situation like the following: A2:A10 shows the timeline of a project. C2:C10 lists all working days between A2 and A10 excluding weekends (marked in red)and public holidays. C2=A2 but for C3 I have used...
  8. A

    Data Validation

    Dear Excel Experts, In first row from Cell C1 to M1 there are name of persons and below 4 rows have specific numbers. I have set in column A all first row name by using data validation list criteria. I want a formula in column B. if a particular name select in cell A2 then at cell B2 shows all 4...
  9. Darth_Williams

    Returning a Value From A Drop Down List

    Good morning all, I have a dependant drop down list working at the moment where the first list shows suppliers and the second shows the items that supplier has. I am trying to work it so that once an item has been selected the value will automatically populate in another column. I thought a...
  10. R

    INDEX/MATCH Lookup Assistance

    Hello, I amjust beginning to learn VLookup, but have found that I will need to useINDEX/MATCH for one of the lookups I need to perform, as the key will not be inthe far left column. I am struggling tofigure out how to accomplish what I need, as I have not yet used...
  11. B

    how to do this sumproduct calculation?

    I'm guessing this can be solved with a sumproduct but I really don't know for sure. <tbody> 0-5KM 50% $5 5-10KM 15% $10 10-15KM 15% $15 15-20KM 10% $20 20-30KM 5% $25 30-40KM 5% $25 </tbody> Here's a table which shows how much we charge (col C) for various distances. Col B shows...
  12. B

    time format

    I'm trying to format cells in hours:minutes. I am putting in 112 to show 112 hours and 0 minutes. I change the format to custom "hh:mm" but when I do that then it shows up as 0:00 and shows; 4/21/1900 12:00:00 AM up above how do I have it show just 112:00?
  13. G


    Hi all, I have a large spreadsheet pulling results from multiple tabs into one pivot table to track the status of various things. Within this, there are several different rules for several different manufacturers. I have a current formula in cell N2 which is...
  14. P

    copy/paste 16 digit number

    When pasting numbers Excel converts any digits after 15 digits to "0" so 1234567812345678 becomes 1234567812345670 I need to cut and paste from an email. Is there a way to paste so the 16th digit shows correctly (without going manual) Perhaps with a auto prefix of comma? Thanks
  15. H

    Formula to extract characters from text string

    Hi mrexcel forum, Could you please suggest a formula to help me with this issue? Issue A: I will like to extract out the numbers immediately before and after the dot. This is the volume information. Issue B: I will like to extract the 3 characters immediately after the Volume information...
  16. danhendo888

    Text Formula to show commas only when > 999

    Suppose I have two numbers 8170.5999 618.111 Is there a formula that shows them as: 8,170 618 That is, show a comma when over 999 I used TEXT with "$0,000" but this shows the comma even for numbers below 1,000
  17. N

    If Statement Help

    Hello, I have one cell which contains 4 different words and want to do an if statement which shows when the word comes up then leave a blank cell. Please reply with if statement. Chris
  18. L

    Index and match with multiple criteria

    I have three data tables (see below). The first one (A1:H12) shows the employee name and whether they are in work on a given day The second one (J1:P12) shows what job role they are working on a given day The third one (R1:X12) shows what line they are working on What I...
  19. S

    Data Validation

    I have been trying to research this but can not get anything to work. I am trying to use a data validation list that shows 1 thing but puts another thing in the cell. For instance I would like to see A in the validation list but when I click A the cell value shows 111. Hope this makes sense...
  20. C

    Add a column to calculate remaining budget

    I have a PivotTable that shows expenses across departments. Each department has a set budget for the entire year. In addition to the grand total that shows how much each department has spent, I would like a column that shows how much of their budget is remaining. For example, the DA department...

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
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 "".
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