Microsoft Excel

130K

Solutions

195

Articles & Videos

37K

Contributors

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Share tech news, updates, or what's on your mind.

Sign up to Post

I need help with formulas or macro(s) to pull information from one sheet (the "CF" sheet) and populate another sheet ("Buy Sell Sums" sheet).  I think it's too complex for Sumifs.   My CF sheet is dynamic and the number of rows can change from day to day.  In a nutshell for the upcoming trading day I need to sum buys, sells, interest (if any), maturities (if any) and transfers (if any) for each account.  I've filled in the values with formulas in the Buy Sell Sums but need a way to automate this daily so I can click a macro and re-populate it.  The tricky part would seem to be the accounts since the row it falls in can always change from day to day depending on the level of activity.
Cash_Flow-06-26-17-PM-Test.xlsm
0
Technology Partners: We Want Your Opinion!
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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
0
I have a shared workbook that has buttons which perform different commands (save, print, create new files, flag results, etc.) based on VBA macros.  A fresh new workbook is created every day from a master workbook.  After the workbook is created, lab results are entered throughout the day by three people who share this workbook.  Over a 24 hour period, the buttons on the worksheet will sometimes change size and shape, and sometimes they will not trigger the macro when clicked on.  Most days, there are no problems with the buttons.  The buttons are form controls, not Active X.

Screenshots of the worksheets with normal and misshapen buttons are attached.  Also attached is the master workbook with the macros that are attached to the buttons.

What can cause the buttons to change shape and size and lose functionality?
Worksheet-with-good-buttons.JPG
Worksheet-with-bad-buttons.JPG
Shared_Master-AB3-and-Q1-Log.xls
0
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")

Open in new window

0
a                        b                      c                 d                    e                        f                      g                 h
1  start_dates      end_dates      code        6/5/2016      6/12/2016      6/19/2016      6/26/2016      7/3/2016
2  6/12/2016          6/19/2016           house                                    x                  
3  6/19/2016          6/26/2016             flat                                                                x            
4  6/19/2016           1/1/2017            car                                                                x                       x                   x

                 a                            b                         c
1      week_beginning           code                  sales
2      6/5/2016                   bike                     100
3      6/5/2016                   house                  75
4      6/5/2016                   car                        64
5      6/5/2016                     flat                       350
6      6/19/2016                   car                       0
7      7/3/2016                      bike                    65

So basically there are promotions that last for varying lengths. The x's indicate the start and end dates for those promotions. I need to pull through the sales for the correct 'code', for the specific dates. I am not sure how to go about this so I would be hugely grateful for any help!!

So for example, I want to know all of the sales for 'car' on 6/19/2016, 6/26/2016 and 7/3/2016. So I need a formula that will find the specific 'code', 'date' and if there is an x pull through the …
0
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.
0
I have this solution

need to filter after  it  query de data

for exmple

with number 803 it found 7 records

but i need select just 3 sets  of that

to move to a new sheet call ToSee
29040080a--1-.xlsm
0
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
0
After searching EE, I did not find anything.  Trying to do:

I am using the following definations.

Set Wb1 = Workbooks.Open("C:\_Excel\CompareSheets\Book1.xls") 'Target file (gets the red)
Set Wb2 = Workbooks.Open("C:\_Excel\CompareSheets\Book3.xls") 'Master file
Set Ws1 = Wb1.Worksheets(1)
Set Ws2 = Wb2.Worksheets(1)
Ws1LR = Ws1.Cells(Rows.Count, "A").End(xlUp).Row
Ws1LC = Ws1.Cells(1, Columns.Count).End(xlToLeft).Column
Set ColRng = Ws1.Range("A2", Ws1.Cells(Ws1LR, Ws1LC))

Open in new window


Then based on this ColRng

For Each c In ColRng
    celladdress = c.Address
    If c <> Ws2.Range(celladdress) Then
        c.Interior.ColorIndex = 3
    Else
        c.Interior.ColorIndex = 0
    End If
Next c

Open in new window


So as a result, some cells in a row will have red cells and others not.

My goal is, before, it starts checking the next row down, to check the current row .  Is there a way to check the row?
If there are no cells in this row that got highlighted red. Delete the entire row, shifting them up.  Or after the process runs, go back and start removing rows that have no highlighted cells in it?  Thus the only thing left would be rows that have a least one red highlighted cell in it.  The code that highlights the cells red is below.  Please advise and thanks.

Sub CompareTwoSheets()
Dim Wb1 As Workbook, Wb2 As Workbook
Dim Ws1 As Worksheet, Ws2 As Worksheet
Dim Ws1LR As Long, Ws1LC As Long
Dim c As Range, ColRng As Range
With Application
'    

Open in new window

0
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
0
Enroll in June's Course of the Month
LVL 8
Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

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 &lt;&gt; objWorksheet2.Range(cell.Address).Value Then
        cell.Interior.ColorIndex = 3
    Else
        cell.Interior.ColorIndex = 0
    End If
Next

Set objExcel = Nothing

Open in new window

0
Set ws = Worksheets("PartsData")  Means ?
0
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.
0
I have an odd issue where duplicate records are showing in Excel through an ODBC query. When I run the same query in SQL or looking at a web browser the records are not duplicated. Any ideas? I tried opening the spreadsheet on different machines and also created a new spreadsheet
er.png
0
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)));
 …
0
C2=12/31/16
C3=and
C4=12/31/15
C5=X

If C5=X
DECEMBER 31, 2016 and DECEMBER 31, 2015
BUT iF C5=is not checked (X'd)
then
December 31, 2016 and December 31, 2015
NOTE: REGARDLESS OF THE SELECTION, C3 IS PRINTED EXACTLY AS ENTERED (This is because sometimes the report needs to read DECEMBER 31, 2016 and DECEMBER 31, 2016.)
EE-Beginning-and-Ending-Date.xlsx
0
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
0
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"

Sample worksheet which contains the formulas above is attached
0
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
0
PeopleSoft Has Never Been Easier
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

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)
  
}

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
0
Hi,

I am having difficulty setting up what should be a simple formula. I think the issue is that I am trying to copy a date as I believe that IF does not recognize dates

What I am trying to do is:  If F9 = “1”, I want to copy a date from A9 into E9 so I can then count how many time that each month appears in E9 for a yearly report. I have this formula already. F9 will either be empty or will be a 1 which is calculated using a formula.

I can add an additional coulum to copy the date and wrap it in a date value function  and then hide that column if necessary. It would be a bit messy but I just want something to work.

I have also tried
=IF(ISNUMBER(SEARCH

Any help would be greatly appreciated.

Thanks
Dot
0
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:

2017-06-25b.png
However, the Format Cells box in CF does not include an Alignment tab:

2017-06-25a.png
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
0
I would like to have the value in GTotal formatted as currency with two decimal places.
Do I get this done with a command in the procedure? Or some other way.

Gtotal = 95000.34
MsgBox ("The total sales amount for sales is $" & GTotal)
0
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
0
Need to sort Data By column

when create the HOR sheet

here my data file
testdatain.xlsm
0

Microsoft Excel

130K

Solutions

195

Articles & Videos

37K

Contributors

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.