Count contiguous values

Leave a Reply

How to add vba code to your comment:
[vb 1="vbnet" language=","]
your code
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.

How to upload a file
Upload file

List all hyperlinks in worksheet

Leave a Reply

How to add vba code to your comment:
[vb 1="vbnet" language=","]
your code
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.

How to upload a file
Upload file

Add new items to a drop down list automatically

5 Responses to “Add new items to a drop down list automatically”

  1. Ola.S says:

    Place the curser in cell A8 (your attached spreadsheet).
    Right click and select from the menu: "Pick_From Drop-Down List..." - works only for text.
    Rarely used, even by the pro's.
    //Ola.S

    http://www.pcreview.co.uk/threads/pick-from-drop-down-list-gives-empty-or-erroneous-result.4026822/

    • Oscar says:

      Ola.S

      I am not sure I understand.

      Right click and select from the menu: "Pick_From Drop-Down List..." - works only for text.
      To be honest, I didn't know you could. That drop down list is not the same thing as a data validation rule - drop down list. Try it yourself, numbers work fine in my first example.

      The drop down list is automatically copied to the next cell below when the table grows. Example, select the last cell in the table, cell C7 and press TAB key. A new row is inserted and cell A8 has a drop down list.

      There are other ways to insert new table rows. Right click on a cell and hover over "Insert". Click "Insert Table rows above" or "Insert Table rows below"

      Thank you for commenting.

  2. mma173 says:

    Thanks' Oscar for sharing this trick.

    I wonder what are the advantages of using tables?

    • Oscar says:

      mma173,

      - Sort and filter
      - Sum by adding a row for total
      - By entering a formula in one cell in a table column, you can create a calculated column in which that formula is instantly applied to all other cells in that table column.
      - Structured references
      - Easy to reference a table or table column
      - Formatting
      - Easy to insert or delete table columns or rows

  3. Oscar says:

    I got a question by email:

    Is it possible to make a drop down list autocomplete if you have hundreds names instead of scrolling down into drop down list?

    Yes, almost. It works only if the values in the drop down list are sorted.

    Example

    1. Type E in the cell
    2. Click "Drop down list" arrow
    3. You are now on letter E in the drop down list
    4. Select a value

Leave a Reply

How to add vba code to your comment:
[vb 1="vbnet" language=","]
your code
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.

How to upload a file
Upload file

Quotient, Mod and Int functions

2 Responses to “Quotient, Mod and Int functions”

  1. kidd says:

    With INT formula you will get same result. =INT(B2/C2)

Leave a Reply

How to add vba code to your comment:
[vb 1="vbnet" language=","]
your code
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.

How to upload a file
Upload file

Permutations with and without repetition

One Response to “Permutations with and without repetition”

  1. Terry says:

    Thank-you for your Combinations formula -- Is there a way to adapt that, or use another function to distribute the resulting combinations "more equitably" over "xx" rows (of dates)? So what am I trying to accomplish? There are six tennis players(players A, B, C, D, E, and F) that share a 15 week contract(actually it's a 32 week contract) to play doubles tennis. I want to set up a schedule to distribute the resulting 15 combinations over a 15 week period that distributes evenly "when" the players play. Using the Combinations function, Player A, "plays" for the first 10 weeks, then is "off" for the next 5 weeks; Player B plays the first six weeks, and then is off for 4 weeks, then plays 4 weeks, then is off the last week; etc. Is there a way to distribute the resulting Combinations such that no player is "off" for more than one or two weeks at a time?
    Any thoughts are appreciated.

Leave a Reply

How to add vba code to your comment:
[vb 1="vbnet" language=","]
your code
[/vb]

How to add a picture to your comment:
Upload picture to postimage.org
Add picture link to comment.

How to upload a file
Upload file