Reader’s Questions - Performing two-way look up
Clever Excel formulas March 16th, 2009
Question:
I have - Table 1 and Table 2
How can I bring salary information in table 2 from table 1 for corresponding dept and jobcode?
Thanks
Bob Lee
Our take on this excel problem
You can join Department and Job Code in both table 1 as well as 2 using concatenate function. And then using vlookup function, you can look up salary from table 1 for a particular department and job code in table 2 as shown below:
Step 1: Concatenate column B and column C
Step 2: Vlookup on the new concatenate column just created
Hope this helps.
Share ThisPopularity: 22%
How to copy and paste values in filtered data?
Clever Excel formulas November 11th, 2008
If you copy and paste values from one column to another, when the filter is on and the range is discontinuous, then it won’t work and will paste the data into non-visible cells as well.
Share ThisPopularity: 33%
Save a new worksheet directly in the desired folder
General October 21st, 2008
If you want to create a new workbook in some folder and you are already there, then instead of clicking on excel icon and then making your way to the folder by clicking down through the folders until you reach the desired location where you want to save your file. Simply right click in the desired folder and then select New and choose microsoft office excel worksheet. And just rename it.
Share ThisPopularity: 19%
Freezing Formulas
Clever Excel formulas September 5th, 2008
This article gives you an example of where we may need to keep our formulas intact.
Share ThisPopularity: 29%
Finding external links
Excel Functions, Macros July 15th, 2008
This article shows how to locate cells in active workbook which contain links to external workbooks.
Share ThisPopularity: 33%
Indirect Function
Excel Functions July 2nd, 2008
Sometimes we want to pull out data on one sheet from different worksheets, all of them having the same structure.We can do that using the vlookup or hlookup function as the case may be. However, while selecting the appropriate range we will have to go to each tab and select the range manually. This can be very time consuming especially if there are lots of tabs. Excel’s indirect function can help us get around this.
Share ThisPopularity: 39%
Choosing your own “Save File” location
General June 27th, 2008
If we save some file without giving the path, then it will automatically get saved at the default location. We can change the Default File Location as:
1.Click on the Tools menu
2.Choose Options
3.Click on the General tab
4.Enter a new path in Default File Location and hit OK.
Popularity: 19%
Alphabetic Series on Excel
Excel Functions, Excel shortcuts June 4th, 2008
This article explains how we can have A-Z letters on a column without typing them in manually.
Share ThisPopularity: 37%
How to unlock keyboard??
General April 30th, 2008
This article explains how to deal with a situation when your keyboard gets locked without you even knowing it.
Share ThisPopularity: 82%
Accessing other worksheets in a workbook faster
Excel shortcuts April 29th, 2008
This article shows how to reach other tabs in a workbook quickly when you have lots of them. Apart from simple “CTRL+PageUP/Pagedown” option, it talks about other alternate solutions like “Excel’s activate sheet option” and “Insert hyperlink” which are less time consuming and more efficient ways to navigate comfortably between worksheets.
Share ThisPopularity: 23%
10 ways to open excel faster
General April 29th, 2008
This article describes in detail why excel takes so long to open and how can you get around it.
Share ThisPopularity: 31%
List Worksheet Names
Macros April 14th, 2008
This article shows how to generate a list of all the worksheet names on a worksheet.
Share ThisPopularity: 49%
Naming cells in Excel
Excel shortcuts April 14th, 2008
This article introduces you to “Define Name” feature of excel with which you can give a name to any cell or range of cells and then just use that particular name in other formula’s like vlookup, offset, match etc without having to select it manually every time.
Share ThisPopularity: 21%
Sumif array formula OR countif function
Clever Excel formulas April 14th, 2008
This article shows how to get the count using more than one criteria. Additionally, it talks about the “conditional formatting” feature which highlights the important results and spares you the inconvenience of having to look at everything in detail.
Share ThisPopularity: 45%
Excel’s Images and Screenshots
General April 5th, 2008
This article shows how to take screenshots in general and also gives special methods which are excel specific.
Share ThisPopularity: 32%
How to find or replace asterisk(*) and question mark(?) in MS excel??
Clever Excel formulas March 30th, 2008
Did you ever wonder why can’t you find asterisk and question mark in your spreadsheet.This article explains it all.
Share ThisPopularity: 38%
Run Command shortcuts
Excel shortcuts March 30th, 2008
This article gives you a quick and easy way to access applications like excel, word, notepad, mspaint, internet explorer etc. In addition, it shows how to display “Run” tab on the start menu.
Share ThisPopularity: 23%
Splitting Large Excel files into smaller parts using Winzip
General March 24th, 2008
Did you ever have problems sending excel files of several MBs through your email systems?? Sometimes it rejects it because it exceeds the quota size or takes forever to send…….This article shows how to split huge excel files into smaller parts which can easily be sent.Plus, it explains how to put them back together.
Share ThisPopularity: 18%
How to add “My Recent Documents” to the Start Menu?
Excel shortcuts March 10th, 2008
This article is about how to access your recent documents. Most of you would be familiar with the “My Recent Documents” option on the “Start” Menu but this just shows 15 recently visited files….Which is clearly not enough so in this article we have given alternate options which are more comprehensive.
Share ThisPopularity: 32%
Generating list of contents of any folder
Clever Excel formulas March 5th, 2008
This article shows how to make a list of the folders, or even the files inside a folder on an excel spreadsheet.This is a very useful tool as it is impossible to type in the names manually especially when there are plenty of files.
Share ThisPopularity: 60%
