I am hoping that it is possible to reference a date string in the worksheet tab name in a formula.

Production worksheet tab name format:
Planning_DD-MMM-YY

Number of production worksheet tabs per workbook:
3

Deadline lookup table:
Deadline_Dates tab

Formula I am looking for to determine if a WO is completed on time or not:
Based on date in production worksheet tab (ie., Planning_12-Oct-17), the formula would check the 'Deadline_Dates' lookup table for the deadline listed for 12-Oct-17 (in this case 05-Apr_17) and determine if the deadline has been met by comparing it against the date listed in Col G (Date_WO_Completed), and then populate Col H as follows:

• If date in Col G is < or = to deadline date, then Col H = 'Yes'

• If date in Col G is > than deadline date, then Col H = 'No'

• If Col G is blank, then Col H is blank.

I have included a sample spreadsheet with the 3 production tabs and the desired results listed in red.

Thanks,
Andrea
Index_Match_Date_Deadline_Worksheet.xlsx

I have the following function in a google spreadsheet and need to adapt it so that it does not show duplicate values.

=IMPORTRANGE("tracker!A3:A1000")

Select all Open in new window

Hello,

I am attempting to go through an excel sheet with about 8000 job titles. All of these job titles have been typed in differently, for example CEO, C.E.O, Chief Executive Officer, etc. I want to go through and make everything have the same format. How do I make it so when it sees C.E.O, it types Chief Executive Officer? I will then be able to change the code myself to the other job titles. I just need a starting off point.

Hello,
i have an excel spreadsheet 2013:


Here are my conditions:
Cells: A1, B1, C1 ,D1 and E1

if A1 <> '' , clear B1, C1, D1 and E1
if B1 <> '' , clear A1, C1, D1 and E1
if C1 <> '' , clear A1, B1, D1 and E1
if D1 <> '' , clear A1, B1, C1 and E1
if E1 <> '' , clear A1, B1, C1 and D1


how to write formula to do the above task.

Thanks

Work around - close errors and open files from recent list within excel

The template for excel is corrupt

How do I delete the normal.dot version for excel ?

Tried office repair - same

I found the following code on internet and appears to do exactly what I need to do, however there is a bad line, Ln12.  How would I rewrite this line, so the code works correctly?  Please advise and thanks.  I would like the cells that are different highlighted in each sheet tab, assuming there is only one sheet tab per workbook.

'This code will compare two excel sheets.
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook1 = objExcel.Workbooks.Open("C:\Documents and Settings\mohan.kakarla\Desktop\Docs\1.xls")
Set objWorkbook2 = objExcel.Workbooks.Open("C:\Documents and Settings\mohan.kakarla\Desktop\Docs\2.xls")

Set objWorksheet1 = objWorkbook1.Worksheets(1)

Set objWorksheet2 = objWorkbook2.Worksheets(1)

For Each cell In objWorksheet1.UsedRange
    If cell.Value <> objWorksheet2.Range(cell.Address).Value Then
        cell.Interior.ColorIndex = 3
    Else
        cell.Interior.ColorIndex = 0
    End If
Next

Set objExcel = Nothing

Select all Open in new window

Hello experts, we're having an odd issue.  Woring in Excel a couple of users are constantly presented with "Not enough system resources to display correctly" followed by both monitors flickering black then back on, then a video card crashed error.  I've updated the Video driver to the latest, I've gone into excel and checked ignore DDE, same issue removed check for Igfnore DDE same issue.  Not sure what else to try.

Anyone have an ideas?  Thanks in advance.

public HttpResponseMessage ER_GenerateWBLWorksheet2()
    {

        Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbook xlWorkBook;
        Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;

        xlWorkBook = xlApp.Workbooks.Add(misValue);
        xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        if (xlWorkSheet == null)
        {
            Console.WriteLine("Worksheet could not be created. Check that your office installation and project references are correct.");
        }

        xlWorkSheet.Cells[1, 1] = "Account Number";
        xlWorkSheet.Cells[1, 2] = "Amount";
        xlWorkSheet.Cells[1, 3] = "Code123";
        xlWorkSheet.Cells[1, 4] = "Date";
        xlWorkSheet.Cells[1, 5] = "Audit";
        xlWorkSheet.Cells[1, 6] = "ID";
        xlWorkSheet.Cells[1, 7] = "Customer Name";
        xlWorkSheet.Cells[1, 8] = "Payment Source";
        Microsoft.Office.Interop.Excel.Range range = xlWorkSheet.get_Range("A2", "A2");//add this drop down list to all rows of first column
        Microsoft.Office.Interop.Excel.DropDowns xlDropDowns;//code to add drop down list
        Microsoft.Office.Interop.Excel.DropDown xlDropDown;
        xlDropDowns = ((Microsoft.Office.Iterop.Excel.DropDowns)(xlWorkBook.ActiveSheet.DropDowns(Type.Missing)));
 …

C2=12/31/16
C3=and
C4=12/31/15
            
=PROPER(TEXT($C$2,"MMMM D, YYYY")&" "&LOWER($C$3)&" "&PROPER(TEXT($C$4,"MMMM D, YYYY")))      
      
Returns:
December 31, 2016 And December 31, 2015      Why is the "a" in and being capitalized although I specified LOWER       

Sample file attached with code
EE-Beginning-and-Ending-Date-2.xlsx

C2=12/31/16
C3=and
C4=12/31/16

=PROPER(TEXT($C$2,"MMMM D, YYYY")&" and "&TEXT($C$4,"MMMM D, YYYY"))
Returns
December 31, 2016 And December 31, 2015 (I DO NOT want to capitalize the "a" in the separator "and")

So, it tried it another was
=PROPER(TEXT($C$2,"MMMM D, YYYY")&" "&LOWER($C$3)&" "&PROPER(TEXT($C$4,"MMMM D, YYYY")))
Which Returns
December 31, 2016 And December 31, 2015 (The exact same thing!)

What am I missing here? Sample snippet file is attached
EE-Beginning-and-Ending-Date.xlsx

Hi Everyone - thanks in advance for taking a look and helping.

I'm trying to build a UDF in VBA that handles cohort math for digital media biz.

It takes as input several 1d arrays, builds a 3d array, then sums along two dimensions of that built array to output a 1d array.

Here's what I wrote in R, which I need to translate into VBA.

 

maus <- function(
  nreach,
  conversion,
  dispersion,
  dl,
  seasonal,
  sl,
  retention
) {
  
  
  n <- length(nreach)
  
  mau.array <- array(rep(0, n * n * n), dim = c(n, n, n))
  
  for (i in seq(n)) {
    for (j in seq(n)) {
      for (k in seq(n)) {
        if (!(i <= (k - j + 1))) {
          next
        }
        mau.array[i, j, k] <-
          nreach[i] * 
          conversion[i] *
          (
            (seasonal[((i + j) %% ds) + 1] * dispersion[j]) /
              sum(
                c(seasonal[(((max(i, j) - 1) %% ds) + 1) : ds], rep(seasonal, floor(dl / ds)))[1 : dl] *
                dispersion[1 : dl]
              )
          ) *
          retention[k - i - j + 2]
      }
    }
  }

maus <- colSums(colSums(mau.array))
return(maus)
  
}

Select all Open in new window

Also attached is a sample workbook where I've tried to start this effort so you can see how I would use the function in Excel.  

I don't know VBA so would appreciate help translating the function I wrote in R into Excel VBA.  

Thank you in advance I really appreciate the help.
Cohort_Math.xlsm

Hello,

Is there a way, in Excel, to use Conditional Formatting (CF) to set the cell alignment?

For example, the regular Format Cells box includes a tab for Alignment:


However, the Format Cells box in CF does not include an Alignment tab:


I've looked around to see if that option is maybe buried in one of the other tabs but with no success.

If modifying the Alignment is not possible in CF, can it be done with VBA code or is there another workaround?

Thanks

I have a macro workbook (Macro.xlsm) and a second (already open) workbook (Student.xlsx), with information on "Sheet 1", row 1 that I want to copy to all of the other sheets in the second workbook, in row one.  I found some code online (see below), which works if I have the source sheet in the macro, but I need to have the source to be in the second workbook. Can you please suggest the changes needed?  I have been unsuccessful at numerous attempts to alter this code.

Also, wondered if you can advise how I may copy Row 1 to all sheets except Sheet1 and Sheet2 (I planned on deleting the row on Sheet2 later, but proper coding would save the extra step to delete this row).  Thank you all in advance for your kind assistance.

‘Insert one row on all tabs, except SHEET1, then
‘COPY the first row from SHEET1 to Row 1  in all tabs, except Sheet1

    Dim ws As Worksheet, Source As Worksheet
    Set Source = ThisWorkbook.Sheets("Sheet1")        
    Application.ScreenUpdating = False

    For Each ws In ThisWorkbook.Worksheets
   
        If ws.Name <> Source.Name Then
            ws.Rows("1:1").Insert Shift:=xlDown
            Source.Rows("1:1").Copy
            ws.Rows("1:1").Insert Shift:=xlDown
           
        End If
    Next ws

    Application.CutCopyMode = False
    Application.ScreenUpdating = True