1. M

    Shorten Formula

    Please can you help me shorten this formula The desired result is the TOTAL SUM of the values from spreadsheet 'Month (1)" columns (C8:C400)+(F8:F400)+(I8:I400)+(L8:L400)+(08:O400) IF(C8="","",SUMIFS('Month (1)'!C$8:C$400,'Month (1)'!B$8:B$400,C8)) +IF(C8="","",SUMIFS('Month...
  2. sharky12345

    Getting month number from cell

    I have the following formula which counts the month number for a range of cells that contain data; The evaluated cells are formatted like this; The formula works fine, except for January where it seems to be counting every blank cell, (as far as I can see). Can anyone give me an idea of...
  3. R

    Check to see if sheet exists before Power Query consolidation

    Hi there I have about 40 files that I need to append through Power Query. In my model the user selects a month and then the sheet with that month name is consolidated. It works great until someone uploads a file that doesn't have any sheets with that month name. How can I get power query to...
  4. C

    Formula issue - mortgage/installment plan

    Hi, Please see the picture linked to below and open it in a separate tab to zoom it: I am trying to create a formula to use in O2:Z2 (blue cells). I want it to do the following: Look for invoices in C3:N3, if true, then divide it by number of mortgage months in B3...
  5. M

    Unique by month management fee in a pivot table

    Hello everyone, I'm struggling with a problem on a pivot table. I have some data that looks like this <tbody> Date Month Management fee Client Billing amount 01/01/19 January 200 Client 1 500 01/01/19 January 250 Client 2 250 01/02/19 January 200 Client 1 560 01/02/19 January 250...
  6. MFish

    Able to individually pull data out of a table, along with the full amount?

    Hi there, I have this code that's corresponding to a table that pulls the total amount but I'd like to know if there is a way to also make an additional table that you can "edit" or press a button/search bar to look for a person's name/employee # and it pulls just their data? My goal of this...
  7. J

    VBA for Copy/Paste

    Hello! I am struggling with a function to link to a macro that requires me to copy a set of values from one sheet to another, while these values depending on a certain month that I link, and then asking the user a Yes or No question on if they want to replace these stored values for that month...
  8. T

    Managing blank values in dataset

    Hello, I have a dataset corresponding to snowfall sum per day, for a number of years (30+). There are often blanks for certain days. Where there is a blank day in a month, I wish to set the entire month (every day in the month) a value M (for missing) or set the entire month blank, either is...
  9. I

    Excel Date Formula - Production Log

    I am finalizing a production log that I have built...I have a Entered Training Date and a Graduation Date listed. I am trying to calculate the number of days instructed within the current month. I have the formula spelled out that gets me exactly what I was looking for, however, this formula...
  10. G

    Excel Tables - Pasting in New Data

    Hi, I have a range of data (a few thousand rows and a dozen columns) in a workbook that I update each month by pasting over with the latest version of the data that I receive in an .xlsx file in email. The number of columns is unchanged from month to month but the number of rows may change. I...
  11. P

    How to hold value inside a cell like a timestamp

    This spreadsheet is designed to keep track of the number of mistakes made on important paperwork during the course of the week and for the month made by one of the employees in the list. A brief description of the layout of the spreadsheet. In range A3:A51 is a list of all the employees for that...
  12. T

    Excel Table Exceeds Rows - Options for Pivot Tables

    I need to create one pivot table for a report. My data currently exceeds the rows for excel, so I took the data and put it on different tabs. The headers are the exact same, but instead of one tab with ~1M+ rows, I have two tabs with ~500K. How can I connect these two tables to one pivot...
  13. P

    Is there a way to reverse the weekday function

    I have a range (b2:f2) that displays the weekdays of the week(Monday-Friday). In range(b1:f1) I have the dates corresponding with those weekdays of the week. Where I am located the current date is Saturday, the 5th day of October. Which is still considered the current week. So if I were to open...
  14. L

    separate day,month, year from date using fx or text-to-columns

    Hi if I have date like 13/1/2001, I know i can use day(), month() and year() to separate day, year and month. I also know I can use text to columns. Which method has advantage/disadvantage over other. Thank you so much.
  15. W

    Multiple Products by Month over multiple years

    Good day, I have a need to consolidate monthly sales data from multiple years for many products. The sales data is in a spreadsheet for each year. The products sold have had some added and some discontinued over the years, and currently an overall master list does not exist that covers this...
  16. Caleeco

    Match Date to Nearest Month + Match with text field

    <colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>#DAE7F5[/URL] "] B C D E F G H I J </thead><tbody> 1 Year-Month Product Date Year-Month Product Date Defects Desired output 2 201909 A 15/09/2019 201910 A 17/10/2019 13 201909 3 201907 B...
  17. T

    Calculating y,m,d not including blanks

    Greetings, I am trying to calculate the amount of time from a date (m/d/yyyy) to show # years, # month, # day but ignore cell if blank. The formula I am using is IF([@[BEGIN DATE-MIN ]]<>"",DATEDIF([@[BEGIN DATE-MIN ]],TODAY(),"y"), DATEDIF([@[BEGIN DATE-MIN ]],TODAY(),"y")&" year...
  18. C

    VBA - Help!

    Hi, I have the below code which looks for any dates from current year in column B and deletes those rows. Every time I run this code it will be on data from a specific month but could be different years (so always November but could be November 2000 all the way to November 2019). It seems to...
  19. B

    Calculating sales number brackets in a table

    Hello, I'm working on trying to auto-fill a sales table I have by inserting either formulas into certain cells or creating a VBA macro. I'm comfortable working with both so either method to solve this problem would be great. I've recreated the table below since I didn't see a way to attach my...
  20. Z

    need the column number of a value

    I am using an inputbox to have a user enter a month( Jan - Dec) The months are in Columns B:M in row 1 I need to know the column number of the selected month. I've tried match and it seems to be failing me. any ideas?

Some videos you may like

This Week's Hot Topics