+ Reply to Thread
Results 1 to 5 of 5

Only apply formula for visible cells isn't working

  1. #1
    Registered User
    Join Date
    02-17-2016
    Location
    Vienna, Austria
    MS-Off Ver
    2013
    Posts
    10

    Question Only apply formula for visible cells isn't working

    Hi guys,

    been trying to fix my problem for the last couple of days but weren't able to do so...
    My worksheet looks like the following:

    Please Login or Register  to view this content.
    I'm using a macro which calls other macros depending on a data validation list selection. The macro looks like the following:
    Please Login or Register  to view this content.
    I'm counting all 'X' in row 7 beginning from column D to the last column (which is AF in my case). All columns without an 'X' are hidden.
    It works as indented but a formula in my sheet gives me headache. It's this one:

    Please Login or Register  to view this content.
    I'm calculation the percentage of occuring 'X' and 'O' with it. The problem starts when I use the macro to hide some columns (which aren't marked with 'X') as the percentage stays the same -> it's still calculating it for all existing columns.
    What I wanted to achieve is a changing percentage based on the amount of VISIBLE columns. I tried to use sumproduct and subtotal but always ended up with #ref! or #value! errors, such as this one:

    Please Login or Register  to view this content.
    Hope you can guide me to the right direction
    Thanks in advance.

    Cheers,
    Mike
    Last edited by cyborg31; 02-17-2016 at 10:09 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    01-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    650

    Re: Only apply formula for visible cells isn't working

    I'm not really following what you're trying to accomplish. if you hide everything that doesn't have an X in it and then you want the % of visible cells with an X in it, isn't that always going to be 100%?

    also, based on your sample data if there is anything in the cell it's an X, won't that also always calculate 100%
    Hope I could help - if your post has been solved don't forget to mark it as such.

    Clicking the * below is a great way to say thanks!

    "Drowning in 10 feet of water isnít any different than drowning in a million feet. And if you can swim, it doesnít matter how deep the ocean is. At some level, once you realize youíre in water thatís too deep to stand, you have to have a very different approach," - Joi Ito

  3. #3
    Registered User
    Join Date
    02-17-2016
    Location
    Vienna, Austria
    MS-Off Ver
    2013
    Posts
    10

    Re: Only apply formula for visible cells isn't working

    I already thought it may sound really complex. I therefore attached a sample which hopefully answers all open questions.
    Attached Files Attached Files
    Last edited by cyborg31; 02-17-2016 at 10:35 AM.

  4. #4
    Valued Forum Contributor
    Join Date
    01-10-2012
    Location
    Texas
    MS-Off Ver
    Excel 2010
    Posts
    650

    Re: Only apply formula for visible cells isn't working

    interesting... i'd have expected to be able to use subtotal to help you out, but apparently subtotal does not like embedded formulas... how do you feel about changing X's to 1's? can make it look like an X with formatting if that's important to you... not sure what O's represent, but i guess those would have to be 1's as well for the solution i have in mind.

    another thought is to use the same macros you're calling when a shop is selected to calculate those %'s... something like:

    Please Login or Register  to view this content.
    Last edited by simarui; 02-17-2016 at 03:33 PM. Reason: added vba option

  5. #5
    Registered User
    Join Date
    02-17-2016
    Location
    Vienna, Austria
    MS-Off Ver
    2013
    Posts
    10

    Re: Only apply formula for visible cells isn't working

    Hi simarui,

    thanks a lot for your answer. I tried the macro and as I understand it should put the result into the 'B' column with 'Range("B" & rng.Row) = ItemCount / VisibleCols'.
    Do I have to do anything else to see the result in there as the cells are empty?

    About your idea of changing them to 1's, that is okay as long as they are formatted to look like an 'X' later on

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1