Blogs

Almir's picture

Easier way to handle your formulae: Make them look like a programming code

If you don't know it already, you can write your formulae (the complex ones) in a way similar to one that programmers use to make their code more legible. It is particularly usefull when creating complex nested formulae (formula within formula), as well as the logical ones, like OR, AND and alike. Why? Well, it is very easy to get lost in long formula: Where is the beginning? What is condition? Where to put a parenthesis? Where did I make a mistake? etc.

Look at the following formula and try to figure out what it does:

=IF(DAY(AT$11)=1;INDIRECT("Volumes!"&ADDRESS(ROW();COLUMN());TRUE);AVERAGE(INDIRECT("Volumes!"&ADDRESS(ROW();COLUMN()-(DAY(AS$11)));TRUE):INDIRECT("Volumes!"&ADDRESS(ROW();COLUMN());TRUE)))

Same formula with indentations and explanations within
Almir's picture

Excel "smart list": drop-down list dependent upon user's choice of another drop-down list

When data entry is strictly defined, it is nice to have data validation list in place, so entries are uniform. Furthermore, a nice way to make data entry faster is to have "smart lists", or dependant lists. What does this mean?

When you choose an option from drop-down list in a column, you need only possible options listed in another column, based on the first choice. Let's look at a simple example with dogs and cats.

Example file is attached.

Dependent drop-down lists
Almir's picture

Timesaver Tip: Calculate only selected range of cells

Sometimes, while working on large workbooks with many complex and slow formulas we set calculation to manulal instead of automatic. From time to time, one needs to calculate only a range of cells. However, pressing F9 (Calculate) calculates all cells, and that can often be time-consuming.

To calculate the selected range of cells only (and to save significant amount of time) when calculation is set to manual, select a range of cells and use a simple macro, like this:


Sub CalculateSelection()
Application.screenupdating = False ' speed up execution by preventing screen flickering
Selection.Calculate ' calculating selected cells
Application.screenupdating = True ' reverting to default settings
End Sub

populate adjacent row details in Userform

Hi, Please help I am Creating a simple userform to populate adjacent rows data in userform. A user enters Employee Id in Txt_Box EmployeeId & after clicking "OK", He Gets Populated Userform from the adjacent rows, From which he can know if the employee is valid or not. I have attached my file for reference also pasting my code below... (Please Help Me with it)

Dim ctl As Control
Private Sub cmdcancel_Click()
Unload Me
End Sub

Private Sub cmdclear_Click()
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""

World Excel Championship 2014

Hey Guys!

I have come across this amazing EXCEL Championship being organized by dezyre.com. They are offering an amazing cash prize of Rs. 30,000 ($500). I have enrolled myself for this championship and thought that you might be interested in the championship too.

Regards,
Nikita

Build Excel Formula with certain condition

Hi,

I'm new here. I need some helps with excel. Pls refer to the below image for further details:-

Thanks.

Excel

Automatic email generate

Hi all, need your expert help on this. attached workbook has a coloum for date and expire date. I need a script or something to make the workbook sent automatic emails to 2 or 3 contacts when the date is expired. Can this be done? if yes, how can I trigger it to send mails? (Do i have to open it? or press a button? or excel automatically do this?) and one last thing if there are 2 copies of the work book does both of them sends the emails or only one sends it?

Thanks,
Sanjeeva

Excel/VBA Expert cum Quality auditor

I am interested in freelance work. I have +20 years experience in IT solutions and for the last 5 years I was helping the top management with various MIS reports created in MS Excel. I have also prepared a dynamic Dashbord which provides the financials and non financials details. I am MBA Finance by qualification and also possesses Lead Auditor Certificates in ISO 9000 and SA 8000. very well versed with productivity improvement tools viz. TPM, Poka-Yoke, 5S etc.

Get in touch with me at sanjeev.arora.72@gmail.com in case you require any service.

Deleting Columns

Hi,

I often have to take a whole column in an excel sheet and move its content to another place in the worksheet.

Is there a formula or any other function to activate so that after I transfer the content of the column to its new place, the empty place will be deleted?

For example, I want to take column D, which is between C and E, cut it and paste it in column G and while I do that, the space that exists now between C and E will be deleted.

Is it possible?

Thank you
Raphi Duani

Collect the correct date from 1 of 32 worksheets into 1 cell and change as it progresses through the 32 worksheets when part mov

Hi,

Can anybody possibly help with my problem.
I have a Workbook containing approx 32 worksheets. This is the short version due to max up load and only contains the first 11 sheets.

I apologise if I do not explain this well as I am not an expert in Excel.

The main Worksheet is 'Front_Page' which does all the reporting of the other work sheets.
The Header Titles in the Red cells in Row 6 match the titles of the worksheets starting with 'MATL'.

Generate unique numbers in serial

I am doing bank recon in excel
for every allocation i make between books and the bank statement i am marking a unique serial number for reference ..manually ..
can someone help me generate this automatically based on a click .. using vba code
Appreciate your immediate response

Excel Macro- support

Hello Team,

I am starting this blog/forum for providing any support/help require for excel VBA.

Let me know or post question/queries/issue you do have for excel macro.

Thanks,

Create directories linked to a cel value

I have a tiny query.

I would like to have a script that create's a subfolders in the current folder of the workbook, linked to a value in a cellrange ( A1,A2,A3, .... would create 3 folders )

When I use next Script

Sub MakeDirs()
Dim MyRange As String
MyRange = Range("C1")
Dim vFolderList As Variant, i As Long
vFolderList = Range("I6:I" & Cells(Rows.Count, "I").End(xlUp).Row).Value
On Error Resume Next
For i = 1 To UBound(vFolderList, 1)
MkDir MyRange & vFolderList(i, 1) 'amend the directory as required (it must exist)
Next
End Sub

(cell C1 is empty)

Auto paste special on lookup formulas if value is found

Hello

I am stuck with an assignment that I have spent a lot of time trying to come up with a conventional solution and I am at a point where I need advise from the esteemed sages on this forum.

Output File name: "NAOPIP Review" with multiple line items (~2k).

How to download/identify mscal.ocx file in office 2010

Hi

Can anyone please help me to identify/download Microsoft's ActiveX Calendar Control file (mscal.ocx) for office 2010 in my laptop.

Because one of my program coding done by office 2003, so i have to run same program in office 2010. please help to identify for above file.

Please...Please..
regrds,

The authorize person only allowed to view authorize data in master report by using password provided.

On monthly basis, we have to send a lot of monthly report to our clients. From master monthly report, we have to manual separate the master report to sub report and send to other batch of clients.

Master report and sub report is same layout. The different is recipient due to confidential data.

Our new proposal is tend to send the master report for all recipients. Our ideal master report is contains with macro, password and other formula. The authorize person only allowed to view authorize data in master report by using password provided.

Example –

Need to extract data in a weird arrangement

I have to convert data to some complex arrangement to perform some important calculations...see the image

data arrangment2.jpg

Fed up of seeing excel opening screen

As i open excel 2010 workbook, it opens a small window in yellow colour , which means that excel is starting.

I am fed up of seeing this screen.

For my few workbooks, I want my own screen to display as Excel starts up rather than excel start up window.

How can I do this.

Any help would be appreciable.

Thanks in Advance...

EXCEL VBA/MACROS PROGRAMME TRAINING REQUIRED.

Sir's

I am Vinod from Hyderabad, i am seriously looking for EXCEL VBA / MACROS programming training from. Kindly advice the trainers in Hyderabad city. Kindly send your valuable reply to my mail ID: gvkkna@gmail.com.

I am waiting for your's reply.

Matter is urgent.

Regards

VINOD

Show Country Flags as Markers on X Y Chart

If you want to display the country flags as markers on X Y Scatter Chart. Snapshot below:

Steps
1 Download the country flags and save them in a folder
2 Make sure you name the flags as labels or series name
3 Run below macro

Sub custom_markers()
Dim srs As Series
Dim cht As Chart
Dim mapfolder As String

' make sure you save the maps with series name

What to Do When MS Excel Stops Responding or Crashes

 At times, while opening MS Excel, you find that it crashes, freezes, hangs, or stops working. Moreover, sometimes you get the following error message:

Excel is not responding.

 

This happens when your Excel sheet gets corrupt. The corruption in Excel sheet occurs due to various reasons. However, mentioned below are possible resolutions to get this problem fixed:

increment value of a row in a sheet based on the value from another sheet

Hi guys,

Below is my request:
I currently have a sheet named "A" with columns as: Name, Status. Status column have many statuses like: started, in progress, testing

I want to code the macro in such a way that it creates a new sheet with all the names from sheet A in a column and another column "status". Now, rows of "status" column should show the count if any of the status from sheet A matches.

For example, for Name John, the status are: In Progress, testing. So the result should be:
Name Status
John 2

I appreciate your help on this.

help me plz

i have sale item userform
when i fill the sale form it creates automatic new sheet of party name but problem is that repeat entry many time why ? plz solve this problem some syntax error i think .

image 2 sheet why created i an need this sheet this sheet is not created.

help me

thanks in advace

image 2

Get all Folder Names in a Folder

Names of all the folders in a folder Including Sub folder

If you want to get the names of all the folders stored/created in a directory/folder ( Including Sub folders).Try below code-

Sub folder_names_including_subfolder()
Application.ScreenUpdating = False
Dim fldpath
Dim fso As Object, j As Long, folder1 As Object
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Choose the folder"
.Show
End With
On Error Resume Next
fldpath = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
If fldpath = False Then
MsgBox "Folder Not Selected"

Get File Names in a Folder

Names of all the files in a folder Excluding Sub folder
If you want to get the names of all the files stored in a folder and excluding the files stored in a sub folder.Try below code-

Option Explicit
Sub file_names_in_folder_without_including_files_in_subfolder()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim fldpath
Dim fld As Object, fil As Object, fso As Object, j As Long
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Choose the folder"
.Show
End With
On Error Resume Next

help me plz in creating sheets

i want to created sheets according to my textbox value
only one sheet of one name is created if i entered again same name then error
help me plz

error

help me plz

i want to created sheets accordind to my textbox value
only one sheet of one name is created if i entered again same name then error
help me plz

error

Excel 2007 Password Recovery

Use PDS Excel 2007 password recovery software which recover your whole excel file password within minutes.

excel password recovery

Analyise the given data set using pivot and excel functions

Hi,

I am providing the link which has sample data

https://www.dropbox.com/sh/tovdou4mt5hgrfe/KD878psFt2

The total score the candidate can score is 30
cutoff scr is 25

1. I wanted to create three buckets

bucket 22, bucket >=24 and bucket 25

2. How do I create "Biggest contributor" performed by the candidate in all the parameters?

3. Which parameter a candidate has just missed with the cutoff score.?

4. Can you also help me with the charts and graphs to show the improvement areas in all the parameters?

Please advise

Thakur

excel student rank calculation

i am creating a student mark list.bt rank calculation is wrong .plz correct it

name mark1 mark2 total result rank
ram 55 65 120 pass 2
ravi 99 34 133 fail norank
raj 34 75 109 pass 3

my rank calculation formlas is =IF(H11="pass",RANK(G11,G$11:G$13,0),"norank")

plz solve my bplm

Syndicate content