Let's assume that you are using Excel table for recording sales of some products by several agents. From time time you need to check how your business is doing.
The table is getting larger and larger. I set its size arbitrarily to 1000 rows, but this is up to the user needs. Here is just its small fragment:
Normally you use the VLOOKUP function to find things in a table or a range by row. E.g., look up a price of a mower part by the part number The function works fine for a specific table in a given worksheet.
But, let's say, you have a workbook with related several tables located in different worksheets. You might need to check all worksheets to find a match for your lookup value. In such a case you would need to use the VLOOKUP repeatedly in each of the worksheets. Much more efficient would be using this UDF (User Defined Function) I've created to make the task easier:
Function VLOOKUPinWBK(lookup_value As Variant, table_array As Range, _
col_index_num As Integer, Optional range_lookup As Boolean)
'This UDF looks in ALL worksheets in your workbook and finds the
' first match for lookup_value.
Dim wSh As Worksheet
Dim wks As String
Dim vLooked
On Error Resume Next
For Each wSh In ActiveWorkbook.Worksheets
With wSh
wks = wSh.Name
Set table_array = .Range(table_array.Address)
vLooked = WorksheetFunction.VLookup(lookup_value, table_array, _
col_index_num, range_lookup)
End With
If Not IsEmpty(vLooked) Then vLooked = vLooked: Exit For
Next wSh
Set table_array = Nothing
VLOOKUPinWBK = vLooked & " (found in '" & wks & "')"
End Function
What are Custom Lists used for? They are used mainly for SORTING or FILLING any regular Excel lists and tables in a specific user-defined order. Using the lists may save you a lot of time when frequent sorting or filling your lists is needed when working with Excel data.
Excel provides couple of built-in lists by default, as you can see below:
There are also many "hidden" custom-like lists you can built and use easily as needed. Below are some examples of such lists that Excel accepts and readily auto fills for you:
The tools Excel provides for its users allow creating the abundance of solutions for entering, analysing, presenting and storing of datasets. However, this usually requires more or less in-depth knowledge of the software. Sometimes you may need to create a specific solution to a complex computational task, but your experience isn't sufficient to do that. In such situations you may need to look for help. I could be able to provide some no-obligation advice or assistance in solving your problems related to organizing your data and analysing them according to your needs. You could probably save some time, improve your design, etc..
Define your specific problem or your custom design needs and let me know about them using the Contact box located at the right side of this page. I may be able to provide some assistance in design details and analysis of data or actually design a spreadsheet for you.
Here are just couple of examples, fragments of worksheet designs, I've created some time ago for my own needs.
This is an excellent helper for data entry in Excel. When we create worksheets with tables of data or databases, in many cases there are text entries, expressions or phrases that we have to enter repeatedly many times. In such situations we can create lists of entries, like people names, phone numbers, cities, days of the week, months, whatever is included in tables, and then create drop-down lists based on the entries. Later, instead of typing the entries one by one in our table we just select the entries from the drop-down list.
Here is a simple example, based on the list of fruits. Follow the steps below to create the list.
