Indirect Function

INDIRECT is pretty cool for grabbing a value from a cell. Can INDIRECT point to a multi-cell range and be used in a VLOOKUP or SUMIF function?

You can build an INDIRECT function that points to a range. The range might be used as the lookup table in a VLOOKUP or as a range in SUMIF or COUNTIF.

In Figure, the formula pulls data from the worksheets specified in row 4. The second argument in the SUMIF function looks for records that match a certain date from column A.

Note: Because each worksheet might have a different number of records, I chose to have each range extend to 300. This is a number that is sufficiently larger than the number of transactions on any sheet. The formula in cell B5 is:

=SUMIF(INDIRECT(B$4&"!A2:A300"), $A5, INDIRECT(B$4&"!C2:C300"))

Figure 36. Each INDIRECT points to a rectangular range on the other worksheet.

Summary: You can use INDIRECT to grab data from a multi-cell range.

Comments

Formatting In Excel - helps you find meaning in the spreadsheet

Formatting In Excel - helps you find meaning in the spreadsheet Spreadsheets are often seen as boring and pure tools of utility, but that doesn't mean that we can't bring some style and formatting to our spreadsheets Formatting helps your user find meaning in the spreadsheet without going through each and every individual cell. Cells with formatting will draw the viewer's attention to the important cells. In Excel, formatting worksheet data is easy. You can use several fast and simple ways to create professional-looking worksheets that display your data effectively. For example, you can use document themes for a uniform look throughout all of your Excel spreadsheets, styles to apply predefined formats, and other manual formatting features to highlight important data. Formatting a Data Raw Data Using Font, Number tabs as shown in image to do a simple formatting Formatting a Data with help of formatting tools in Excel As shown in image , we have Prod...

20 Power BI Dax Measures

Power bi 20 dax measures 20 DAX (Data Analysis Expressions) measures in Power BI with examples: Total Sales: scss Copy code Total Sales = SUM (Sales[Amount]) Average Sales Price: scss Copy code Avg Sales Price = AVERAGE (Sales[Amount]) Total Units Sold: mathematica Copy code Total Units Sold = SUM ( Sales [ Quantity ] ) Total Customers: scss Copy code Total Customers = COUNTROWS (Customer) Total Products: mathematica Copy code Total Products = COUNTROWS ( Product ) Maximum Sales Amount: scss Copy code Max Sales Amount = MAX (Sales[Amount]) Minimum Sales Amount: scss Copy code Min Sales Amount = MIN (Sales[Amount]) Sales Growth Percentage: mathematica Copy code Sales Growth % = ( Total Sales - [ Total Sales Last Year ] ) / [ Total Sales Last Year ] Total Profit: scss Copy code Total Profit = SUM (Sales[Profit]) Total Orders: scss Copy code Total Orders = COUNTROWS (Orders) Total Customers with Sales: css Copy code Total Customers with Sales = COUNTROWS( FILTER ...

Solutions for Data Science

Search This Blog