The post Excel Price Lookup for Date and Product Name appeared first on Contextures Blog.

]]>

Someone asked that question on my Contextures blog last week, on my post that shows how to find the latest price for a specific product in Excel.

Before we tackle the new problem, here is the example from that old post, where we wanted the **LATEST** price. The screen shot below shows a lookup table, with product prices and dates.

In that example, I used two formulas:

- MAX and IF (array-entered with Ctrl+Shift+Enter) to find the latest date for a product

=MAX(IF($A$2:$A$9=A12,$B$2:$B$9)) - Then, SUMIFS to find the product’s price on that date.

**=SUMIFS($C$2:$C$9,$A$2:$A$9,A12,$B$2:$B$9,B12)**

Here’s the result of a lookup for Pens, in that example. The formula ignores all the earlier prices for Pens, and returns the latest one.

The new question had a different twist – there was an on-going list of invoices, and it needed a lookup formula to find the product price **based on the invoice date**.

In the screen shot below, the invoice list is at the left, and the product pricing lookup is on the right.

- For the “BBB” sale on Jan. 10th, we need the Jan 8th price from the lookup table.
- The Mar 15h sale of BBB needs the Mar 15th price.

In my Excel newsletter this week, I posted my solution, and asked if anyone had another way to get the correct prices.

My formula was a long, complicated, array-entered monstrosity. I figured someone could find a simpler solution. Brace yourself – here it is:

**=INDEX(Products[Price], SMALL(IF(Products[Item]=[@Item], IF(Products[Pdate]<=[@Date], ROW(Products[Pdate]) – ROW(Products[[#Headers],[Pdate]]))), COUNTIFS(Products[Item],[@Item],Products[Pdate], “<=”&[@Date])))**

It works, but it’s very difficult to read and understand. Also, array formulas break easily, if someone presses Enter, instead of Ctrl+Shift+Enter

Fortunately, some smart and creative people read my newsletter, and they sent me their solutions.

Van V and Tim O both suggested using VLOOKUP. You’ll need to add a column at the left of the pricing table, with a formula to combine the product name and date.

It’s not necessary, but I added an underscore between the name and date, as a separator.

**= [@Item] & “_” & [@Pdate]**

Next, sort the price list by product (item) and date – this is crucial to making this method work.

Then, in the Invoice list, use a VLOOKUP formula with an approximate match, to get the correct price.

**=VLOOKUP([@Item] & “_” & [@Date], Products,4,TRUE)**

I left my original formula in column E, for comparison, and add the VLOOKUP in column F.

The VLOOKUP worked perfectly, so it’s a good option, if:

- you can add a column to the pricing table,
- and remember to sort it by Item and Date.

Thanks to Van and Tim for sending their VLOOKUP solutions.

Paul B and Tim O sent their solutions too, and they both used INDEX and MATCH. The setup is similar to the VLOOKUP solution:

- add a column to the pricing table, to combine the item and date. However, it doesn’t need to be on the left – it can be anywhere in the table.
- pricing table must be sorted by item name and date (Tim suggested a macro to do that, so it’s easier).

Here is the formula for the invoice table, to pull the correct price from the pricing table:

**=INDEX(ProductsLU[Price], MATCH([@Item] &[@Date], Products[ItemDate],1))**

Thanks to David P and Leonid K, who also sent formulas, and these **didn’t require any changes to the pricing table**. All three of their formulas are better than the one that I created – shorter and easier to read.

1) David’s first formula uses LOOKUP:

**=LOOKUP(1,1/FREQUENCY(0,1/(1+(Products[Item]=[@Item])*(Products[Pdate]<=[@Date])*Products[Pdate])),Products[Price])**

2) David’s second formula uses ROUND, and is **array-entered** (Ctrl++Shift+Enter):

**=ROUND(MOD(MAX( IF( (Products[Item]=[@Item])* (Products[Pdate]<=[@Date]), Products[Pdate] + Products[Price]/1000000)), 1)* 1000000,5)**

3) Leonid’s formula is an INDEX/MATCH formula:

**=INDEX(Products[Price],MATCH(1,1/((Invoice[@Item]=Products[Item])*(Products[Pdate]<=Invoice[@Date])*Products[Pdate])))**

With so many formula options, which one would you choose for your workbook?

- The VLOOKUP and first INDEX/MATCH formulas are easiest to understand. However, they require changes and maintenance to the pricing table.
- The All-In-One formulas are a bit more complicated, but don’t require any changes to the pricing table
- My original formula works, but it’s the longest, and most complicated. I’m fond of it though, after all the deep thought that went into it!

A couple of people asked how my original formula works, so here is a description of the key pieces.

- The INDEX function will return a specific item in an array (range of cells), and the SMALL function tells it which item to return (row number in the range).
- The SMALL function returns the nth smallest number in an array , and COUNTIFS calculates that “n”.
- The ROW function returns the worksheet row number for each matching item. To get the row within the pricing list, we subtract the row number for the pricing list header.
- To see the row numbers that the SMALL function can choose from:
- In cell E6, select the “array” part of the SMALL function in the formula bar, and press F9
- You will see this result: SMALL({
**FALSE;2;3;FALSE;FALSE;6;FALSE;FALSE**} - For items that match the criteria, the numbers show their positions in the price lookup table. Items that don’t match show as FALSE.

- The COUNTIFS calculates how many prices in the table have the same item, and a price date on or before the invoice date. NOTE: For this to work, the pricing table must be sorted by date
- To see the COUNTIFS result:
- With cell E6 still selected, in the formula bar, select the COUNTIFS part of the formula, and press F9
- The result is 3
- =INDEX(Products[Price],SMALL({FALSE;2;3;FALSE;FALSE;6;FALSE;FALSE},
**3**)) - The 3rd smallest number in the array is 6, so the price from the 6
^{th}row is returned by the INDEX function.

To get the workbook, with my original solution, and the better alternatives, go to the Sample Excel Files page on my Contextures website.

In the Functions section, look for **FN0049 – Product Price Based on Date**. The zipped file is in xlsx format, and does not contain any macros.

_________________

The post Excel Price Lookup for Date and Product Name appeared first on Contextures Blog.

]]>The post 7 Ways to Round in Excel appeared first on Contextures Blog.

]]>

Yes, it certainly looks wrong, but sometimes 2+2 = 5 in Excel.

Or, you could make things even more confusing, and show that 2+3=4.

You might be able to guess why those totals look wrong. Even a simple spreadsheet like this one can have things going on below the surface.

The problem with “incorrect” totals can occur if you use number formatting to make the numbers look rounded. Excel hides the decimals, but they’re still stored as part of the number.

In the first example, the numbers look like 2+2, but the actual numbers are 2.3 and 2.3, which add up to 4.6.

In the second example, the actual numbers are 1.54 and 2.54, which add up to 4.08.

So, be careful if you’re using number formatting that hides the decimals, or someone might question the accuracy of your spreadsheet!

In some cases, you might want to use a function to round the numbers, instead of just hiding the decimals.

To help you get started, I’ve made this short slide show that shows 7 ways to round in Excel.

It shows number formatting, and the problem it can cause.

There are also rounding examples that use the Excel functions ROUND, ROUNDDOWN, ROUNDUP, FLOOR, and CEILING.

To see the details for the rounding examples in the slide show, go to the Excel Rounding Functions page on my Contextures website. There are workbooks to download too, so you can follow the examples.

It also has an MROUND example, and looks at the variations for the FLOOR and CEILING functions.

_____________

The post 7 Ways to Round in Excel appeared first on Contextures Blog.

]]>The post SUMIFS Formula With Empty Criteria Cells appeared first on Contextures Blog.

]]>

Someone emailed to ask how they could ignore one criterion in a SUMIFS formula, if that cell is empty. Here is the original formula:

**=SUMIFS(D$2:D2,B$2:B2,B2,C$2:C2,C2)**

It sums all the values in column D, starting in row 2, and down to the current row, where:

- values in column B match B in the the current row,
- and values in column C match C in the the current row

See more SUMIFS examples on the Sum Cells page of my Contextures website.

How can we change the formula, so it ignores the criterion for C, if the current row has an empty cell in column C?

First, I set up a sample sheet, where I could do a bit of experimenting, and entered the original formula in column E

It’s interesting that SUMIFS returns a zero if there is an empty cell in column C.

For my first solution, I tried using an empty string as the criterion, if C was empty.

=SUMIFS(D$2:D2,B$2:B2,B2,C$2:C2,**IF(C2="","",C2))**

That created totals in the rows with blank cells, but it only added up the other blanks.

Next, I tried using an asterisk wildcard as the criterion, if C was empty.

=SUMIFS(D$2:D2,B$2:B2,B2,C$2:C2,**IF(C2="","*",C2))**

That created totals in the rows with blank cells, but it didn’t include the values from blank cells. Apparently the wildcard doesn’t recognize those.

Finally, I decided to repeat the first criteria range and its criterion, if C was empty:

=SUMIFS(D$2:D2,B$2:B2,B2, **IF(C2="",B$2:B2,C$2:C2), IF(C2="",B2,C2))**

And that worked! As you can see in the screen shot below, it summed all the previous items that met the column B criterion, and also included the rows where C is empty.

Well, that was fun – even if it’s not artsy enough to post on Instagram!

There are probably other ways to solve this SUMIFS problem, so if you’d use something different, let me know in the comments.

___________________________

The post SUMIFS Formula With Empty Criteria Cells appeared first on Contextures Blog.

]]>The post Pivot Table Fill Colour Disappears appeared first on Contextures Blog.

]]>

In Microsoft’s Excel World Champ data visualization contest, there were 14 country finalists. Each finalist submitted a data visualization file, and everyone was invited to vote for their favourites. You can see the 14 submissions on the Tech Community blog.

The voting ended yesterday, and they announced the winner and the two runners up today. I don’t see names or countries listed with the submissions, but here’s a screen shot of the one with the most votes. Maybe that’s the overall winner, but other factors were included too, I think.

I wasn’t familiar with the contest winner — Ghazanfar Abidi, from Canada – but Google helped me find his website.

And that’s where I saw his latest blog post, where he showed a problem with pivot table fill colour. I’d never noticed this problem before.

Here are the steps to recreate the problem:

- Use the Ribbon command to add fill colour to a pivot table cell
- Select another cell in the pivot table, and press F4 (or Ctrl+Y) to repeat that command. In the screen shot below, cells D5 and A8 were coloured with the F4 shortcut
- Refresh the pivot table

Surprise! The cells that were formatted with the Ribbon command are still coloured, but the cell where I used the shortcut lost their fill colour!

I wanted to see if other types of cell formatting disappeared to, so I applied bold, italic, underline, font colour and font size:

- with the Ribbon commands in column C
- with F4 in column D
- with the Format Painter in column E – I copied the “F4” formatting from column D

Then, I refreshed the pivot table, and the only formatting that disappeared was the fill colour in cell D5, where the F4 shortcut had been used.

Surprisingly, the fill colour was still in E5, even though it was a copy of the D5 formatting.

So, the moral of the story, if there is one, is to use the Fill Colour or Format Painter Ribbon commands to apply fill colour, if you’re formatting a pivot table cell.

Don’t use the keyboard shortcuts F4 or Ctrl+Y to apply colour to a pivot table cell.

P.S. I added the Repeat command to the Ribbon, and tested that – the fill colour didn’t stick with that method either.

Or, if you like practical jokes, you can use the awesome revenge formatting trick that Ghazanfar Abidi showed in his blog post. But you’ll have to figure out how he did it!

__________________

The post Pivot Table Fill Colour Disappears appeared first on Contextures Blog.

]]>The post Combine VLOOKUP and MATCH for Flexible Formula appeared first on Contextures Blog.

]]>

There are a couple of key benefits when you combine VLOOKUP and MATCH:

- It makes the formula flexible – use one formula to pull data from different columns in the lookup table.
- It can prevent problems if new columns are added in the lookup table, or if the lookup columns are rearranged.

This video shows how to combine VLOOKUP and MATCH, and there is another example, below the video.

In this example, we’ll use a VLOOKUP formula to get the order details from a lookup table, based on the order ID number. Here’s the lookup table, which is named tblOrders.

To get the details for a specific order, you could create 3 VLOOKUP formulas, with a different column number typed in each formula:

- Region: =VLOOKUP($B6,tblOrdersALL,
**2**,0) - OrderDate: =VLOOKUP($B6,tblOrdersALL,
**3**,0) - OrderAmt: =VLOOKUP($B6,tblOrdersALL,
**4**,0)

That works, but it’s not very efficient — you have 3 formulas to maintain, instead of just 1 formula. And nobody has time for that!

Instead of figuring out which column the data is in, and then typing that column number in the VLOOKUP formula, let the MATCH function do the work for you. The MATCH function finds the position of an item in a list, and returns that position number.

For example, if the MATCH function looks for “Region”, in the lookup table heading cells, the result is 2.

**=MATCH(C5,Orders_ALL!$A$1:$D$1,0)**

Cell C5 has the heading “Region”, and that is a relative reference. Later, if the formula is copied to the right, it will refer to the headings in D5 and E5.

To add the MATCH function to the VLOOKUP formula, just replace the typed column number

**=VLOOKUP($B6,tblOrdersALL,2,0) **

with the MATCH formula:

**=VLOOKUP($B6,tblOrdersALL,MATCH(C5,Orders_ALL!$A$1:$D$1,0),0) **

Now, instead of needing a different formula in each column, you can copy the formula across, and use the same formula in all the columns. In each column, the MATCH function’s first argument (C5) will change, and refer to the heading cell in that column.

For this technique to work correctly, the headings on the VLOOKUP sheet have match the lookup table headings exactly.

- In my sample file, the VLOOKUP heading cells are linked to the lookup table heading cells.
- If you can’t use links, copy and paste the headings from the lookup table, to be sure they’re exactly the same.

Go to the VLOOKUP page on my Contextures website, to get the sample workbook. In the Download section on that page, click the link to Sample File #1.

The zipped file is in xlsx format, and it does not contain any macros.

_____________________

_________________________________

The post Combine VLOOKUP and MATCH for Flexible Formula appeared first on Contextures Blog.

]]>The post Take the Excel Name Fix Challenge appeared first on Contextures Blog.

]]>

Here the challenge that I posted in the newsletter —

If people enter their names in a sign-up form, you might end up with a mixture of upper and lower case letters. You can see an example in the screen shot below.

This week’s challenge is to clean up that list. Using Excel formulas, make these changes to the list:

- Show the corrected name in column B, with only the first letter of each name in upper case. For example, Fred Jones instead of FRED JONES.
- Add an X in column C, to mark the names that were fixed.
- In cell E1, show the number of names that were fixed.

For inspiration, there are videos and links on my Functions page.

Several people sent their solutions to me:

- a few said the problem was very easy to solve
- others had a bit of trouble, and had to do some research to find a function that would solve the problem.
- a couple knew the simple ways to fix the problem, and looked for different formula solutions

There were a couple of unexpected twists too:

- one person noticed an extra space in a name, and wanted to fix that too
- another person asked if there was a way to show the number of changes in each name

I compiled the solutions into the Excel Name Fix Solutions workbook.

To take the challenge, download the completed Name Case Solution workbook. The zipped file is in xlsx format, and does not contain any macros.

- The problem is on the
**NameList**sheet. - Don’t look at the other sheets, until you’re ready to see the solutions to this challenge.

Let me know if you come up with any other formulas that solve the problem. And don’t read the comments below, if you don’t want to see a solution!

___________________

The post Take the Excel Name Fix Challenge appeared first on Contextures Blog.

]]>The post Excel Freeze All Worksheets Macro appeared first on Contextures Blog.

]]>

When I hear “Frozen”, I think of the Disney movie, and the song, “Let It Go!” pops into my head. If you’re not familiar with that song, lucky you! My granddaughter loved that movie, and had a birthday cake with Elsa on it, a couple of years ago (see the picture at the end of this post). Anyway, in that movie, **everything** got frozen.

In an Excel worksheet, the cells(s) that you have selected will control what gets frozen. Here’s a chart, with all the options that I can think of. If I missed something, please let me know.

Selection | Frozen Area |
---|---|

Cell A1 | All cells above and to the left of center |

Other cell in column A | All rows above that cell |

Other cell in row 1 | All columns to the left of that cell |

Other cell | All cells above and to the left of the selected cell |

Row 1 | All cells above and to the left of center |

Any other row | All rows above that row |

Column A | All cells above and to the left of center |

Any other column | All columns to the left of that column |

If you just want to freeze one sheet, you can do it manually.

- First, select a cell, row or column, below and to the right of the area that you want frozen. See details in the chart above.
- On the Excel Ribbon, click the View tab
- Click the Freeze Panes command
- Click Freeze Panes, to freeze at the selected location – OR, choose a command to freeze the first row or first column

If you have lots of sheets in a workbook, and want to freeze all of them at the same spot, you can use this Freeze All Worksheets macro.

- Before you run the macro, select the cell(s) to use as the freeze location (se the chart above).
- When the macro runs, you’ll see a confirmation message, asking if you want to freeze at the current selection.
- Click Yes, to go ahead, and click No to cancel the macro.

Copy this macro into a regular code module, then select cell(s) on any worksheet, and run the macro to freeze all the sheets in the active workbook.

Sub FreezeAllSheets() 'www.contextures.com Dim wsA As Worksheet Dim ws As Worksheet Dim wbA As Workbook Dim strSel As String Dim lRsp As Long On Error GoTo errHandler Set wbA = ActiveWorkbook Set wsA = ActiveSheet strSel = Selection.Address lRsp = MsgBox("Freeze all sheets at current selection?", _ vbQuestion + vbYesNo + vbDefaultButton1, "Freeze Sheets?") If lRsp = vbYes Then Application.ScreenUpdating = False For Each ws In wbA.Worksheets ws.Activate Range(strSel).Select ActiveWindow.FreezePanes = True Next ws wsA.Activate Else 'do nothing End If exitHandler: Application.ScreenUpdating = True Exit Sub errHandler: MsgBox "Could not freeze all sheets" Resume exitHandler End Sub

To get the sample workbook with the Freeze All Worksheets macro, go to the Excel Worksheet Freeze and Zoom Macros page on my Contextures website.

You can test the macro in that workbook, before adding it to your own files. Remember to enable macros when you open the workbook.

The sample workbook also has two other macros:

- Unfreeze All Worksheets
- Zoom All Worksheets (enter the zoom level that you want(

Here’s my granddaughter’s Frozen birthday cake. Today is her birthday, and she chose a plain chocolate cake for this year’s celebration. She must be growing up!

____________________

The post Excel Freeze All Worksheets Macro appeared first on Contextures Blog.

]]>The post Fix Excel Conditional Formatting Duplicate Rules appeared first on Contextures Blog.

]]>

Watch this video to see how the duplicated rules are created, and an easy way to fix the problem. There are written instructions below the video.

If you need to do this frequently, go to my website to get a macro that removes the duplicate rules. It’s designed for data in in an Excel named table.

In this example, there is a small table, with 2 simple conditional formatting rules:

- In column E, highlight prices that are greater than 500
- In columns A:E, add a blue top border if the date in column A is different from the date above

To see how a new rule is automatically created, delete row 10 in this table. Then, go to Conditional Formatting > Manage Rules, to see the results.

- The original rule has changed, and excludes the deleted row.
- There is a new rule that applies to row 10 only, and it has a #REF! error

The new rule was created because the Top Border rule has a formula that refers to another row. It compares the date in the current row, to the date in the row above:

**=$A2<>$A3**

When you delete a row, the row below that loses its reference to that row. If we had the same formula on the worksheet, you’d see a #REF! error.

The same #REF! error occurs in the conditional formatting formula, so Excel creates a new rule for that formula.

If you frequently delete and insert rows, you could end up with many duplicated rules. In a big workbook, that could potentially slow down your workbook’s calculation speed.

And, you might not even know about those extra rules, unless you go into the Manage Rules dialog box for some reason.

I’ve written about this problem before, and there are a few ways to prevent the problem from occurring. Now that I’ve found an easy way to fix the problem, I don’t worry about prevention – I just clean things up when necessary.

Here are the easy steps to remove the conditional formatting duplicate rules:

- Except for the first row, select all the rows with the same conditional formatting rules
- On the Excel Ribbon’s Home tab, click Conditional Formatting
- Click Clear Rules, then click Clear Rules from Selected Cells
- Select the first row, and on the Excel Ribbon’s Home tab, click the Format Painter
- Drag the Format Painter over all the cells where the conditional formatting rules should be applied, including the first row

To confirm that the duplicate rules were removed, go back to the Manage Rules window. Only the two original rules should be listed.

To get the sample file that I used in this example, go to the Conditional Formatting page on my Contextures website. The details are there, as well as the macro code. Scroll down a bit to see the link to download the file – it is in xlsx format, so the macro isn’t in that file.

________________

The post Fix Excel Conditional Formatting Duplicate Rules appeared first on Contextures Blog.

]]>The post Excel RANK IF Formula Example appeared first on Contextures Blog.

]]>

The goal of the Rank If formula in this example is to rank each day’s sales, compared to other days in the same week. If we just use Excel’s RANK function, it would compare each day to all the other days in the list.

In the screen shot below, you can se the first few rows of the sample data. The sales records for two weeks are visible.

- In week 1, Jan 2nd and Jan 4th have the highest sales, so they should be tied at rank of 1.
- In week 2, Jan 10th has the highest sales, so it should have a rank of 1 for that week.

To manually rank the items largest to smallest for each week, we could count how many items are larger than each item.

- In week 1, there are zero items with amounts larger than the Jan 2nd sales.
- The rank for Jan 2nd is 1.
- Using that information, we have a way to calculate the rank — the number of items larger, plus 1

The COUNTIFS function lets us count based on multiple criteria, so we’ll use that to create an Excel Rank IF formula.

For our criteria, we need to count:

- other sales with the same week number
- sales larger than the current row

Then, after COUNTIFS gives us that count, we’ll add 1 to get the rank.

To see more COUNTIFS examples, go to the the Count Functions page on my Contextures website.

Here’s the Rank If formula that I used for this example:

**=COUNTIFS([Wk], [@Wk], [Sales], “>”&[@Sales])+1**

The first criterion in the formula checks for other sales with the same week number:

**=COUNTIFS([Wk], [@Wk] **

The second criterion find items with a larger amount in the Sales column.

**[Sales],”>”&[@Sales])**

Then, 1 is added to that number, to get the ranking.

**+1**

To check the ranking in week 1, look at the sales for Jan 3rd — 237.

- There are 2 dates with a larger sales in week 1 — Jan 2nd and Jan 4th
- Add 1 to that number, and Jan 3rd has a rank of 3

To download the sample file, go to the and RANK Function page on my Contextures website. The file is in xlsx format, and does not contain macros. It contains other RANK examples too.

_____________________

_______________

The post Excel RANK IF Formula Example appeared first on Contextures Blog.

]]>The post Popup List of Excel Sheets appeared first on Contextures Blog.

]]>

Long ago, the talented Dave Peterson created a menu bar combo box that listed all the sheets in the active workbook. Select a sheet from the drop down list, and instantly go to that sheet.

That add-in was designed for Excel 2003, but it still works in the newer versions – it appears on the Add-ins tab. You can download it on the Sheet Selector for Excel 2003 page.

When the Office interfaced changed in 2007, Ron de Bruin created a new version of Dave’s Sheet Selector. It adds a command on the Home tab of the Ribbon. Get Ron’s version on the Excel Navigator List page of my website.

Click the Select a sheet command, and a popup list appears, with all the sheet names. Then, click one to go to that sheet.

If there are 16 or more sheets in the workbook, the Activate window opens instead. That’s the list that appears when you right-click on the sheet scrolling buttons at the bottom left of the Excel window.

In the Activate window, click a sheet name, then click the OK button, to go to that sheet.

So that takes 3 clicks (Select a Sheet, sheet name, OK), and who has time for that?

To save some time and reduce clicks, I decided to create a new version of the Sheet Selector. My popup list of Excel sheets is a modeless UserForm, with a listbox for the sheet names.

The add-in command appears on the View tab. It’s a split button, so you can click on the top part, or click the arrow, and choose a command from the drop down list.

When you click the Show List command (at the top of the button or in the drop down), the UserForm appears. The UserForm is modeless, so you can leave it open while you work in Excel.

If the sheet names are long, use the scroll bar at the bottom, to read the hidden part of the name.

If you’d rather see the sheet names in alphabetical order, click the A-Z button at the bottom.

And if you switch to a different workbook, click the Update button or the A-Z button, to see an updated list of sheets.

While the Sheet Lister is open, you can keep working in Excel. For example, copy something on one sheet, then click the Sheet Lister to go to the destination sheet, and paste it there.

And unlike some macros that clear out your Undo stack, the Sheet Lister doesn’t do that. That’s because it doesn’t make any changes to the worksheets – it just selects a sheet for you.

The modeless UserForms don’t play well with the newer versions of Excel, with the multiple windows. They only appear in front of the workbook that was active when you opened the UserForm.

Jan Karel Pieterse has screen shots that show the problem with modeless UserForms, and has some code that fixes the problem. So, if you’re using my Sheet Lister, and are having trouble with the Excel windows, you can add his code to the Sheet Lister workbook.

For now, I’m leaving it as is. I have two monitors, and keep the popup list of Excel sheets at the left edge of the monitor that’s to my right. I’m always able to see it that way, even if it’s partially covered by one of the open workbooks.

To get a copy of my Sheet Lister add-in, go to the Excel Sheet Lister Add-in page on my Contextures website. The zipped file is in xlam format, and contains macros.

**NOTE**: When you’re installing an add-in, follow the instructions here, to unblock the file. Otherwise, it might not appear on the Excel Ribbon, due to a Microsoft security update.

The code is not locked, so you can see how it works, and even adjust the size of the Userform and listbox, if you’d like to.

__________________

The post Popup List of Excel Sheets appeared first on Contextures Blog.

]]>