1. L

    ifs() not in office 365

    Hi I am reading that ifs() is in excel 2016. I have office 356 and I thought it is updated version of 2016. Thank you.
  2. willow1985

    Specific Countif formula (matching cell then counting all occurrences in column)

    Hello, I am looking for a specific countif formula. Basically in column E I have names of locations: E Blue Red Blue Green In Cell H10 I have the word "Blue" In Cell H9 I want a formula to look in Column E, find how many match Cell H10 and return the number, in the above case: 2 I thought...
  3. S

    How to prevent a table from sorting (and that's it)

    I have a simple Excel table that I need to prevent from resorting. I still need to be able to filter and enter data on it. I thought I could unlock cells and then protect the worksheet and just uncheck sorting from the protect worksheet menu, but that seems to cause Excel to be unresponsive...
  4. R

    Pointing to appropriate cells via formula with variables

    Full Disclosure: I posted a version of this on one of the other Excel Forums last night, but I haven't received an answer. I'm hoping you guys can help What I'm trying to do is create a product title for many different products based on type, color, and whether it has a charm or connector or...
  5. R

    Rank and Round Together

    Is it possible in the same formula to Rank and Round together? I have the following formula but it is returning #N/A. I assume this is because the whole reference is not rounded which leads me to believe this is not possible to do in one step. However, I thought someone may know of a workaround...
  6. A

    How to Round Numbers

    In my code, I run into values of a variable (tdur) representing duration (in hours) . These are some examples: 0.53 1.53 2.11 1.06 1.32 2.64 0.26 I would like to round these to the nearest 30 minutes. So, with these example: 0.5 1.5 2.0 1.0 1.5 3.0 0.5 I thought it was more simple than it is.
  7. L


    Hi I was doing cells.clear then I thought of doing cells.delete. To my surprise, when I click on a button to run the code below, everything got deleted including the button. So I left with a clean sheet. What I thought would happen is that the who sheet will get deleted and also why the control...
  8. A

    Listview Control Missing

    I wish to give a workbook to members of our group. It contains a listview control, but it appears that control is Missing (even though it was in the xlsm file I gave them). I thought adding a Reference would fix this, but it appears not. Is there a solution for this? Thanks,
  9. DataBlake

    VBA using rows.count from another sheet

    Sub Macro1() ' ' Macro1 Macro ' ' Sheets("Unknown").Select Dim lastRow As Long lastRow = Range("A" & Rows.Count).End(xlUp).Row Sheets("Sheet1").Select Rows("2:2").Select Range("2:2").AutoFill Destination:=Range("2:2" & lastRow) End Sub is what i have, but when i ran it "Sheet1" autofilled...
  10. T

    Combine Macros

    Hi I thought I was doing it the right way but not because it throwing it out. Im trying to combine macros into one so they all run one after the other. I thought that you created a macro called combined and then just input the macro names you want to run? Tried inputting...
  11. G

    If Statement with multiple logic / lookup

    =IF(B3={"Test 1","Test 2"},$D$3,"N/A") Hello all, I'm trying to get the If statement to pull data from D3, if B3 has either "Test 1" or "Test 2" I thought I could use the squiggley's but that doesn't work. Thanks
  12. T

    Indirect function with array

    Can someone please explain what this formula is doing? =SUM(COUNTIF(INDIRECT({"a1","b","c1"}),apples)) I thought apples was a named range in the workbook but I can't find it. Thanks
  13. S

    How to design this spreadsheet?

    I need to design a spreadsheet, almost a monthly planner to yearly to show Key meetings per month by month /date Attendees (same 4 people) Pre work Post work What’s the best way to design this? I’ve thought of various designs but they don’t include all info I need. Any help on...
  14. K

    Formula gives an error but works in the worksheet

    Hello All, I am trying to using this line in some vba code: Range("AF2").Formula = "=IF('RawData Old1'!A2=0,"Base Bid", "Alternate - " & 'RawData Old1'!A2)" This is the exact formula that is currently working on the worksheet. Why is the code stopping on the word Base? I thought that might...
  15. E

    Splash Form, the open another form

    I have a simple splash form that closes after .05 seconds. At the end of the splash form closing, I want to open an other form. Private Sub UserForm_Activate() Application.OnTime Now + TimeValue("00:00:04"), "KillTheForm" Form("UserForm1").Select End Sub I thought it would work the...
  16. B

    Average formula query

    Hi I want a cell to display the overall average of all cells which are either A or B. Column A = cells will be either A, B, C, D etc.. Column B = values I thought maybe it will be but not sure.. =AVERAGEIFS(B1:B100,A1:A100,"A",A1:A100,"B")
  17. J

    Fitting 2 Columns / 20,000 cells on fewer pages as possible? (Printout)

    Hi, looking for some ideas on best way to achieve this I have data in: Columns A + B (Header A1 and B1) down to around row 20000 (max characters per cell is 15) and i need to compress the data so its printable with row 1 repeating. I thought of: viewing in page break preview copying everying...
  18. M

    Extracting substrings from strings using regex.

    Can anyone give me the VBA to extract a time from a string, it'd always appear in the format ##:## but not always in the same place otherwise I'd have just used split to extract it. I did consider using split with a colon separator and then just joining 2 digits from left and right of the two...
  19. D

    vlookup basics

    Hello I am trying to do a vlookup from an assessment grid. Being a newbie, had a look on google and managed to get a formula and it pulled out the data. I thought great!!! problem solved. Carried the formula down and I could see data which I thought was great.... then realised it's not pulling...
  20. M

    [Challenge]How to get the tab name to use in formula

    Hello all, I want to use indirect formula to look up certain range in different sheets, is there a way to extract the sheet's name to use in the formula? it maybe easier than I thought but I haven't found any way to do that, thank you
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

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