+ Reply to Thread
Results 1 to 3 of 3

Data from table, color is condition

  1. #1
    Registered User
    Join Date
    05-03-2022
    Location
    Hannover, Germany
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    34

    Data from table, color is condition

    Hello,

    I have no idea how to continue and need your help yet again. Was given the following task: The user enters data in a confusing table. The desired output is a table were the data for the current week is shown. An example is attached. I managed to implement three functions with VBA: GetCellColor(cell), wich returns a unique number per cell color, SumCellsByColor, and CountCellsByColor, which each get a range and a cell reference and sum or count the cells. I am sorry that I can't upload a .xlsm file, but that is forbidden in the environment I'm in - I will copy the code below. If somebody would be willing to help, he or she would have to copy the code into a module.

    Until now, you would do the task like this: Check if date in current week is in confusing table -> if yes: check the row in which the date is-> check the column where Day 1 is written -> sum up all entrys of the respective colors.

    Could you tell me if there is an easy way to do the task with excel formulas, or do I have to look into a more complicated VBA-Function? I would be extremely thankful!


    The functions are:

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by Ijakor; 06-22-2022 at 07:59 AM.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Data from table, color is condition

    Quote Originally Posted by Ijakor View Post
    Could you tell me if there is an easy way to do the task with excel formulas
    There are no built-in Excel functions that return the fill color of cells, so VBA is required.

    or do I have to look into a more complicated VBA-Function?
    It seems you have a solution for this. What else do you need to do that your current VBA does not accomplish?

    If we upload a .xlsm file, can you receive it?
    How about a .xlsb file?
    How about a .zip file? Would you be able to extract a .xlsm file from a .zip file?
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    05-03-2022
    Location
    Hannover, Germany
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    34

    Re: Data from table, color is condition

    Thank you for your answer. I am sorry if the question was unclear - Yeah, my functions work as they were intended. Basically, I have difficulties finding a formula that manages to "transfer" the data from the, for a lack of a better word, confusing table to the ordinary table. It is basically a problem of "formatting" existing data. The output I thought of is colored yellow. No, our firewall protects against all sorts of office documents that contain macros. I could try and upload a .xlsm from home, if that is better for you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Formula to get the data in another table with two condition
    By shiva_reshs in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-15-2020, 10:28 AM
  2. Transferring data to a new table when a certain condition is met
    By joepekimo in forum Excel Programming / VBA / Macros
    Replies: 28
    Last Post: 04-15-2017, 10:52 AM
  3. [SOLVED] Look up value from data table that matching more than one condition...
    By tonisjoseph in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-19-2015, 08:31 PM
  4. Replies: 3
    Last Post: 01-08-2014, 03:56 PM
  5. [SOLVED] SerialTableWith a condition from data table
    By turist in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-13-2013, 11:04 AM
  6. [SOLVED] help with filling a table with data from another table on a true condition
    By derrickh13 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-14-2013, 04:44 PM
  7. Highlight data with some color if condition fails
    By jesika in forum Access Tables & Databases
    Replies: 2
    Last Post: 04-08-2009, 07:35 AM

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