Solved

How can I display the argument type of a cell's SUBTOTAL() function

Posted on 2014-04-10
2
270 Views
Last Modified: 2014-04-10
I have a table the bottom row of which contains formulas for SUBTOTALS. Sometimes it may be a Sum, Average, Minimum or Maximum. I would like to have an adjacent cell with text that tells which of those types is currently selected, Is there a way to extract that information from the cell with the formula?
0
Comment
Question by:BobArnett
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 22

Accepted Solution

by:
Ejgil Hedegaard earned 500 total points
ID: 39992908
Using VBA code it is possible to read the formula, find the type code in the Subtotal formula and display that in a cell, either the number, or a text.

I think it would be as good to do it the opposite way, and that don't require macro activation.
Make a list with the subtotal types you want to use, and the corresponding type numbers.
Use Data validation to select the type (name), and use a Vlookup to get the number for the subtotal formula.
The list can be placed on another sheet, and use a range name for the data validation input.
Excel require the validation list to be on the same sheet, but not if it is a range name.
See example.
Subtotal-type-select.xlsx
0
 

Author Closing Comment

by:BobArnett
ID: 39992934
I agree, your second idea would fit the best for what I want to do. Thanks for the quick and helpful reply.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question