Posts by rbjexcpa

    Re: Conditional Formatting with VBA


    It works! I tweaked the IF statements further and will post the updated spreadsheet to close this thread. However, there is just one more bug in getting it to work in my spreadsheet. Here is the code and it is identical to the one used in the sample (other than the expanded range to cover the actual seating chart). However, I am getting the following error:


    Run-time error '13'


    Type mismatch


    When I go to debug the first line under Select Case Target is highlighted. I'm assuming it is looking for something that hasn't been defined?


    Thanks!


    Rod


    Re: Conditional Formatting with VBA


    Viper,


    Thanks for your efforts.


    I have searched the forums and can't find anything that resolves this. As you mentioned, it not only won't go back to Available, so it must need to be looped along with the shift colors so that it reads from a single column.


    Also, the cell is not firing based on the formula result. I.e. if I change one of the shifts on Building 1, it will change the text but not the background color. Only if I highlight the cell and press enter will it change the background color. Is there any way to make this event happen either on the worksheet level all at once, or each time the formula is updated.


    Thanks,


    Rod

    Re: Conditional Formatting with VBA


    Thanks for all the input so far. Bill - that is a nice seating chart, but I'm looking for something that updates automatically.


    To clarify what I need. I have the code in First Floor which is where the seating chart is. However, the code needs to refer to Building 1 and pull from column F what kind of restriction is placed on the seat. The seat addresses are in column B, and if the seat is available, the code should color the seat based on what restriction is placed on it.


    As for using the first two conditions as conditional formatting, that is how it is currently. However, adding the 6 shift restrictions is where we need the VBA to override the conditional formatting whereever it says Available (yellow on the seating chart), and change it to whichever color corresponds to the restriction.


    Thanks!


    Rod

    Hello,


    I have a seating chart that needs to have multiple colors to highlight certain restrictions. There are a total of 8 colors so VBA must be used.


    I have simplified this to avoid using multiple tabs, but the restrictions are pulled from two separate columns and the text may or may not appear on the chart depending on the restriction.


    I.e. If the seat is available it is shown on the seating chart as "Available". If the seat is not available for desk sharing, it is also shown on the seating chart as "No Desksharing".


    All of the other codes restricting availablility by shift are not shown as text on the seating chart, but as the color restriction - Day, Swing, Grave, Swing or Grave, Day or Swing, and Grave or Day.


    The following code has all the colors, but I don't know what is required to get pull the right codes from the columns in VBA. Any help would be appreciated and serves as a building block for future tweaks.



    Thanks,


    Rod

    Re: Multiple conditions for index match


    The example I provided was too basic for the situation as it involves an index/match lookup on another sheet.


    The real problem was that when I used Index/Match and it didn't find a match (using concatenate for the column headings), it would return #N/A and not continue on to evaluate the next condition in a nested IF/THEN statement. However, I was able to get it to work with some ISERROR tweaks.


    Here is the formula (with references to my actual spreadsheet).. wonder if there is a more elegant way of doing this, i.e searching for a match in multiple columns and returning a different response for each match?


    =IF(ISERROR(CONCATENATE($B2,G$1)=INDEX('Query'!$A$1:$Z$2000,MATCH(CONCATENATE($B2,G$1),'Query'!$A$1:$A$2000,0),3))=TRUE,IF(ISERROR(CONCATENATE($D2,G$1)=INDEX('Query'!$A$1:$Z$2000,MATCH(CONCATENATE($D2,G$1),'Roll Up Query'!$A$1:$A$2000,0),3))=TRUE,"",1),-1)


    Thanks,


    Rod

    Hello,


    I have a situation that I'm stumped with, thought I'd pose it to the board.


    Index Match works great for returning one result based on criteria. However, I am working on a spreadsheet that tracks transfers from one department to another. So, I need to be able to have two results in the same row i.e. to show -1 from the department being transferred out of, and +1 for the department being transferred into.


    However, if I use an IF/THEN, it will find the first condition, then not look for the second condition or vice versa. What am I missing here?


    Example spreadsheet attached.


    Thanks,


    Rod

    Hello,


    Two questions:


    1. What are the VBA codes to do the same thing as the following but for cell contents as opposed to cell entry, i.e. for formulas in the cell instead of an entry.


    2. When using this code, can another sheet be referenced i.e. if the conditions are on another sheet, color the cell depending on a lookup.


    I only know enough VBA to get me in trouble, which is often.


    Thanks!


    Rod


    Re: Multiple Criteria Selection


    It does for that example. But I have the ranges on one tab in a spreadsheet and the results on another tab, with many results to evaluate and one table. So I am unable to put the results in a column H as in the sample in order for the RANK formula to work.


    Thanks!


    Rod

    Re: Multiple Criteria Selection


    I apologize that my first post wasn't as clear as it should. What I am trying to check simultaneously is given that the current result for Homes is 88, and I'll call that Y, what score do I give the result from 1-5.


    First it needs to check to find where Y is greater than the ranges listed in the 5 columns for Home i.e. 0, 75, 125, 150, and 180. Once it determines that Y is greater than the number in column E, it returns the score which is in the column header for column E which is 2.


    I can't really use IF statements because I will go past the 7 nested limit in a hurry, and I can't think of an IF/AND or IF/OR that works for this situation.


    INDEX/MATCH works great if my results were matching up with the numbers in the table, but I'm assigning the score based on the result falling within a range i.e. greater than one number, but less than another.


    I've attached a sample chart as the previous one looks confusing.


    Thanks,


    Rod

    Hello,


    Using the following chart for example:


    A B C D E F
    1 5 4 3 2 1
    2 Home 180 150 125 75 0
    3 Sports 99 77 55 0
    4 Toys 200 150 100 50 0
    5 Books 200 125 85 0
    6 Cars 100 75 50 25 0


    I am looking for a solution that allows me to look at a result from one of these categories, i.e. Home for 88 and it will look up this chart, see if the result is larger than one of the columns then return the score from the column heading. For example Home for 88 would return 2 as it is larger than 75 but smaller than 125.


    The solution needs to take into account situations like with Sports and Books where there are no scores given for a column. For example, a score of 125 for Sports would return a score of 4, since there is nothing in the 5 column.


    I am open to either an Index/Match solution or a VBA IF/THEN/ELSE solution or something else that works without too much debugging for many more rows than the example provided.


    Thanks!


    Rod

    Works like a Charm!


    While we are on the topic of Data Tab Manipulation, any way we can incorporate the changing tab names into a hyperlink? Or, any way that the hyperlinks can be set up so that if the sheet names change they will still reference the same location on the sheet without having to recreate the hyperlink?


    Thanks!


    Rod

    Jindon,


    That works great for grabbing the tab name and it works as long as I run the macro on each sheet. Is there any way to automatically pull the name or run the macro when you press enter on the tab name or to automatically do this for each tab input.


    Or, if that could not be done, running a macro to pull all the tab names in the sheet to a list then I could map out the cells I want to contain the tab names from that list.


    Thanks,


    Rod

    I would like to be able to take the names off the tab names that are input and have them pulled to a cell in in the spreadsheet. I.e. If the tab name is RepName and it is input as Joe Smith.. then I need a formula or some way to grab that tab name so I can use it on the spreadsheet itself.


    If unable to do that.. I know there are some solutions already for automatically naming the tabs from a list of names. The problem is that there are some sheets that I don't want to rename (Template), so can I skip over certain sheets?


    Thanks!


    Rod

    What I am trying to do is to show the decimals if there is a partial number but to show a whole number if it is .9999 or 1.9999 or etc. If I use the Round function i.e Round(AA23,0) for example it will return 1 when it is .6666, 1, and 1.3333.


    Instead I want it to show.. for example..


    .3333
    .6666
    1 (rounded up from .9999)
    1.3333
    1.6666
    2 (rounded up from 1.999)


    Let me know if that clears it up or if there is really no other solution to this problem.


    Thanks!


    Rod

    Hello,


    This is probably a situation better suited for VBA than for formulas. I have a situation where attendance is counted with 3 of some occurrences equal 1 formal occurrence. However, I don't want to total these to .9999 or 1.9999 etc but rather to do a full number once 3 of the occurrences are tallied i.e 1 and 2 respectively, for all numbers, as well as to show the partial totals as .3333 and 1.3333 etc until it reaches .9999 or 1.9999 then show the total as 1 or 2 respectively.


    In the attached spreadsheet, I have populated with several examples. Is there an easier solution to do this rather than to do a long IF statement to handle these decimal numbers?


    Thanks!


    Rod