To learn about other formulas that return ranges, check out this post. OFFSET / MATCH =OFFSET('Linked Picture'!$A$1,MATCH('Linked Picture'!$D$2, XLOOKUP (new function available in Excel 365) =XLOOKUP('Linked Picture'!$D$2,'Linked Picture'!$A$2:$A$11,įind out more about the XLOOKUP function in this article: XLOOKUP function () But any formula which returns a range will work inside the named range. INDEX/MATCH is a formula combination, which can achieve some amazing things picture formulas are just one of those amazing things. The named range applied to the picture does not exist.This usually occurs where the name of the country is not identical to one of the countries in the list. The result of the INDEX/MATCH function produces an error.If you receive the Reference isn’t valid error there are two likely problems: For completeness, change cell D2 into a data validation drop-down list containing all the countries. Change the address in the formula bar to be equal to the named range we created earlier (CountryLookup in our example).Ĭhange the name of the country in cell D2 and watch the image change.Click Home -> Paste (drop-down) -> Linked Picture (alternatively, the Camera Tool is an option).Select a different cell (use E2 if working with the example file).Click Home -> Copy (or Ctrl + C) to copy the cell.Now that the named range has been created, follow the following steps to create a linked the picture. Using a named range as the source for a linked picture Cells A2-A11 contains the names of the countriesĬlick OK to close the New Name dialog box.Cell D2 contains the name of the country to be matched.The formula in the Refers to box is: =INDEX('Linked Picture'!$B$2:$B$11, Rather than a range, the Refers to box needs to contain a formula calculates to a range. In the New Name dialog box, create a new named range called CountryLookup. Next, we’ll create a named range by selecting Formulas -> Define Name from the ribbon. Start by entering one of the country names in a cell (for our example, use cell D2). The example data contains a list of countries in Column A and their relevant flags in Column B.Ĭreate a dynamic named range with INDEX MATCH Now let’s look at each of these steps in turn. Use the named range as the source for a linked picture.Create a dynamic named range using the INDEX MATCH formula combination.If you’re working along with the example file, we’ll begin with the Linked Picture tab.Īs an overview, this method works as follows: Advantages & disadvantages of each optionĬhange image with a named range + INDEX/MATCH + linked picture.Change image with a VBA User-Defined Function.Add the chart fill automatically with a macro.Using a named range as the source for a linked picture.Create a dynamic named range with INDEX MATCH.Change image with a named range + INDEX/MATCH + linked picture.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |