Nick's picture

Nick's Day By Day Excel Experts Blog

Help Adding the +/- button to the first item in a Group

I am trying to create a simple drop down or expandable field that allows me to simply click a +/- in the top cell of a group to expand that group so I can enter data...then close that group and open a new group. Very similar to just creating the group with the plus and minus on the left of the worksheet....but I want a button in the top cell of each group. Is that possible?

Thanks so much.


Getting MODE if certain criteria is met

In my data I have a list of sales (amounts) by cities. How can I get the mode for the sales only where the city = chicago? I have sorted the list by city and written the mode formula based on my sort to get only Chicago sales (rows 10:20). This works as long as the list stays sorted because the mode range is specific to to the sort results.


Looking for differences in similar data sets

Hi There.

I am analysing a sales order book on a week to week basis. The Sales order reference remains the same but order values can change daily based on shipped/cancelled and amended orders. Can anybody suggest the best way to reconcile these differences in excel please?

Vishesh's picture

Export spreadsheet data to Access

Following macro can be used in Excel VBA to export Excel data into Access.

Sub TestMacro()

Call ExcelToAccessTransferSpreadsheet("G:\ExcelExperts\ExcelAccessTest.mdb", "DBTestTbl", "G:\ExcelExperts\Test.xlsm", "Sheet1", "A1:C8")

End Sub

Sub ExcelToAccessTransferSpreadsheet(strDBPath As String, strDBTableName As String, strExcelFilePath As String, strSheet As String, strRange As String, Optional blnClearTableBfrUpload As Boolean = True, Optional blnDropTableBfrUpload As Boolean = False)

'Should have access on the system
'Creates a new table in Access if not found

Mail Merge Help

Dear Sir,
I have an excel sheet with email ids of people and also the email ids of their reporting managers.
Now I have to create multiple letters, which I am contemplating to do with Mail merge and create the messages, but when I send out the output I want the mails to be sent with CC to the reporting manager. But in the Word the email is only sent to one id and no field to capture the email id for CC.
I understand it can be achieved with macro.
I tried to record and do but then failed.
Some one could help me on this would really be grateful.

excel help

We have a friendly league where everyone drafts 9 teams and picks one. The person with the most wins at the end of the season wins. I have made a manual entry excel sheet to enter wins from each college football team and nfl team. I was wondering if there is a way for the win totals to update automatically. Thanks
see attachment

Truncating text files to extract only email addresses

Hello does anyone know a formula to extract just the email addresses from files with records as follows:;;Athens;Attiki;Greece;20/07/2015;Nao;Sim;;;;1430833559;;Salvador;Bahia;Brazil;20/07/2015;Nao;Sim;;;;3143249011

I have a total of 25,000 records for this extraction so a truncation solution would be appreciated!

The text required for each of the records above is:


Excel cannot open .xls file

Got a new customers pc here. she had a virus last week (one of the ucash ones) . she then ""spent all day trying to fix it following instructions on various forums""
She has no idea what she actually did. the virus isnt there anymore, but now nearly nothing works on the pc. sys restore doesnt work and every single document is giving me the error ""the open office xml file xxxx.docx cannot be opened because there are problems with its contents"" details: ""the file is corrupt and cannot be opened"". this also happens with xls, xlsx, docx, doc, csv

User friendly differential equation solver

This is kind of hard to explain.

I am creating a numerical solver that gets input from students in the form of an equation. An example being x^2 + y^2. I'd like to take this input and somehow put it into VBA as is.

This equation is going to be in a for each loop, where the values of x and y are changing with each iteration.

For i = 1 To n
m = x^2 + y^2 'Equation input from students
y1 = y + m * dx
y = y1
x = x + dx
Next i

VBA Macro to send Email to added recipients


I have a macro file, where as per my selection (Customer Name Selection), I;m able to send email to the specified customer. Like - Once I select Xerox Co. from the filter and click on Send E-mail button, all the fields of Xerox Co gets copied to new workbook and I'm able to send email to the recipient mentioned in the VBA code.

Now what I want is - instead of having the email id mentioned in the vba codes. I'll like to have a column of email id of all the customer in column B for each customer name (,

Thanks for your help in advance


How to filter multi column data and then appear filter data in seperate sheet

I have excel sheet that have more than 100 column. I want to make easy interface for user, my requirement is below.

detail of sheet are like

1- Column A to H are fixed and I want to filter the remaining column like I to Z.

2- When I filter the column I want the result that it will show only column that I filter and starting column A to H.

3- user interface is like drop down search of column I to Z.

Need macro to remove item and reset back to zero

I need help creating a macro that will remove a stock item from one tab and reset to zero so the macro can be run multiple times. The crux I am facing is being able to use this as a counter but in a negative sense. On the 'PRODUCTION'tab, I need to have the capability of removing stock item "TANK SIDE" from 'DS TANK' tab when a serial number is entered on the 'PRODUCTION' tab. The 'DS TANK' and 'PS TANK' tabs our inventory tabs and need the ability to add and subtract parts as they are produced and taken for assembly. I have attached a file for your perusal.

Adding value (5,2,1 year) to a cell value based on another cell value (DOB)

Hello All,
I have multiple excell worksheets with list of columns. Column C is Birth Year,column D is date of Approved, I want in Column C to calculate based on this criteria:

If Column C is <35 years add 5 years to Column D
If Column C is 35-45 years add 2 years to Column D
If Column C is >45 years add 1 years to Column D.
Please See my attached file!!
I used IF funciton but the outcome is not what i want
My formula:

Nick's picture

XLA routines: EE_PivotRemoveTotals

EE_PivotRemoveTotals is a simple routine that removes the totals from a pivot
Sub EE_PivotRemoveTotals(Optional pt As PivotTable)
'  for updates on this routine
    If pt Is Nothing Then
        If ActiveSheet.PivotTables.Count = 0 Then Exit Sub
        Set pt = ActiveSheet.PivotTables(1)
    End If
    pt.RowGrand = False
    pt.ColumnGrand = False
End Sub

VBA to paste Excel Range into Powerpoint (it should not be a picture and excel linked table)

I would need a help to copy an excel range as editable table in a powerpoint. When i tried, It was pasted as a picture or as a excel linked table which were annoying.

and i would need to paste it as an editable one (similar to a powerpoint table). please post your reply soon.


Nick's picture

XLA routines: EE_SortCols

EE_SortCols sorts columns by a predefined range of headers...
Nick's picture

VBA Developer Jobs in the UK

Live VBA Developer Jobs in the UK

{"what":" VBA developer","where":"","results_per_page":"20","country":"gb","widget":"job-feed"}
Nick's picture

Excel Developer Jobs in the UK

Live Excel Developer Jobs in the UK

{"what":" excel developer","where":"","results_per_page":"20","country":"gb","widget":"job-feed"}
Nick's picture

Excel VBA Jobs in the UK

Excel VBA Jobs in the UK

{"what":" excel vba","where":"","results_per_page":"20","country":"gb","widget":"job-feed"}
Nick's picture

Excel VBA Jobs in the UK

Nick's picture

Excel Jobs in Cardiff

Live Excel Jobs in Cardiff

Nick's picture

Excel Jobs in Dundee

Live Excel Jobs in Dundee

Nick's picture

Excel Jobs in Perth

Live Excel Jobs in Perth 

{"what":"Excel","where":"Perth ","results_per_page":"20","country":"gb","widget":"job-feed"}
Nick's picture

Excel Jobs in Inverness

Live Excel Jobs in Inverness

Nick's picture

Excel Jobs in Aberdeen

Live Excel Jobs in Aberdeen

Nick's picture

Excel Jobs in Edinburgh

Live Excel Jobs in Edinburgh

Nick's picture

Excel Jobs in Glasgow

Live Excel Jobs in Glasgow

Nick's picture

Excel Jobs in Belfast

Live Excel Jobs in Belfast

Nick's picture

Excel Jobs in Leeds

Live Excel Jobs in Leeds

Syndicate content