Posts by Webbers

    Re: Fill formulas down columns based on used cells in adjacent column


    Okay, I was able to figure it out (wooohooo!) I changed this line of the code:


    HTML
    For i = 2 To last


    so now it displays as:


    HTML
    For i = 3 To last


    Now it starts the process in row 3, rather than row 2. Thanks for all your help with this, it is greatly appreciated!

    Re: Fill formulas down columns based on used cells in adjacent column


    Okay, I figured out part of the problem, row 2 is headers, and the formulas being copied BEGINS in row 3. I do not want anything being added to H1 - J2 regardless of what is listed/not listed in G1-G2. Everything must begin will row 3 since that is where the data starts.

    Re: Fill formulas down columns based on used cells in adjacent column


    That almost worked completely perfect. There is just one small problem. It is basica I am sure, but since I am still trying to learn, I have no clue where it is. Your code populates all cells with data in Columns H - J exactly as I wanted (thank you so much for that). However in addition, it also places the formula =IF(VLOOKUP(RC[-5],dump,6,FALSE)="","",VLOOKUP(RC[-5],dump,6,FALSE)) in cell J2, which is the header for that column (listed as "Description").


    Other than it overwriting the hear, it is awesome. If you could show me why this is so I can adjust it, it will be perfect. Thanks so much! I am so excited about this!

    I have written a macro, but I realized I "need a better widget"! I need this macro, which copies a formula into cells H2, I2, and J2 (3 different formulas), to copy and paste those same formulas into all the rows below that have active data. Obviously based on the contents of cell G for each row will determine if cells H - J will need the formulas copied in. If there are rows where cell G is blank, then no formulas will be copied into cells H- J. But of course the number of rows with "live data" changes each time this report is run, so a range is just ineffective.


    I have been resreaching thism but have yet to find something that I can edit to meet my needs. Thanks in advance for all your help! below is the code of the macro:


    Re: Multiple If Statement Revsising


    Quote from Wigi

    =IF($N2="",IF(ISERROR(VLOOKUP($B2,MMP_Staff!$B$2:$C$1198,2,FALSE)),"Missing Resource","Complete"),IF($N2="Not listed on MMP_Staff worksheet","Add Resource","Resolved"))


    Wigi


    Wigi----


    This is beautiful! It worked perfectly! Thanks so much for your guidence. In my attempts, I had thought I could or had to incorporate it within the existing formula rather than an additional IF statement at the end. Thanks so much for your help! I appreciate it! :rock:

    I have painstaking got this fromula to work as I have needed to add new criteria to it. I was thrilled with being able to do it all on my own. Then I realized I need to add a new criteria, and well, I have been fighting with potential modifications, and loing this battle (miserably).


    My current formula that works perfectly is:


    =IF($N2="",IF(ISERROR(VLOOKUP($B2,MMP_Staff!$B$2:$C$1198,2,FALSE)),"Missing Resource","Complete"),"Resolved")


    What I need to factor in is an adidtion variable that if cell $N2 contains the exact text of "Not listed on MMP_Staff worksheet" then the result will display as "Add Resource". Currently these items show as "Resolved" because of my latest modification.


    Now this particular text is not part of a larger string, it will be the only text that appears in cell N2.

    Good Morning All!


    I have a slight problem I am hoping you can assist me with (as usual). I am using Office 2003. I have a spreadsheet with about 5000 rows of data, and about 30 columns. As with all spreadsheets the accuracy of this data is essential. So I have integrated conditional formatting into many of these columns to account for "contigency factors" which will help me avoid mistakes, and when there are some, find them easily. Now, much of this data (text, alighment, background color, etc...) has been formatted for a specific purpose, and not every row of data is the same. In addition, as we receive changes from Editorial, we make those changes, and then change the text color to dark green to indicate and Editorial change. Now, I want to be able to copy and past the conditional formatting I created from another spreadsheet (my master template) into this one. However, I don't want to lose the indiviual cell/row formatting that has been created. Is this even possible? Normally I use edit > paste special > format, but I will lose all the individual formatting which that process. Thanks so much everyone, it is greatly appreciated.

    Re: If Statement With Except Criteria


    Quote from Derk

    Would _ETE_ be the entire code? if so, try
    [bf]=IF(OR($X4="",$X4="_ETE_"),FALSE,(ISBLANK($W4)))[/bf]
    or if it is just part of a code string then try
    [bf]=IF(OR($X4="",ISNUMBER(FIND("_ETE_",$X4))),FALSE,(ISBLANK($W4)))[/bf]


    Derk---


    No, "_ETE_" is only a portion of the code. I used your 2nd formula and entered it as an array like you stated and it worked perfectly! Thanks so much. I did think of incorporating the FIND into my formula, but had not thought of using ISNUMBER also. Thanks so very much!

    I have a conditional format set up with an IF statement, and it works fine. But I have realized that I need to add an additional criteria.


    Column W contains either an x or is a blank cell
    Column X contains a unique code to each entry consisting of both letters and numbers



    =IF($X4="","",(ISBLANK($W4)))


    The formula I currently have is designed to turn the cell (in Column W) red if there is a code in Column X, but not an x in Column W. Now what I have determined is that eventually all cells will receive a unique code in Column X, but I do not want Column W to be highlighted in red if the code contains the code of "_ETE_". Now that would be those 5 characters all togather like that. I am sure this is something very simple I am overlooking, but I am just not sure how to approach it. Thanks in advance for all your time and help! :)

    Re: Sumproduct With 2 Criteria & Wildcard


    Quote from ByTheCringe2

    Try:


    =SUMPRODUCT(('[eTE Status - TX HSP Math 2009.xls]Project'!$B$2:$B$248=D2)*(LEFT('[eTE Status - TX HSP Math 2009.xls]Project'!$A$2:$A$248,3)="978"))


    Cringe---


    That worked perfectly! I see the differences in our formulas. And honestly, I never would have figured that one out! Thanks again Cringe, you rock! :cool:

    Re: Sumproduct With 2 Criteria & Wildcard


    Arthur---


    I am sorry about that. D2 contains a formula, that the result is the Grade. In this specific case, the grade is G1. This is also how the grade is formatted within the other spreadsheet (which is in column B). Sorry about the confusion.

    I have spent the last 2 1/2 hours trying to figure this one out on my own. I have attempted multiple possiblities from the forum, however without success.


    Okay, I need a count based on 2 criteria. My data source is a different workbook.


    My last attempt on a formula is:


    =SUMPRODUCT(('[eTE Status - TX HSP Math 2009.xls]Project'!$B$2:$B$248=D2)*('[eTE Status - TX HSP Math 2009.xls]Project'!$A$2:$A$248="978*"))


    Column A - ISBN #
    This will ALWAYS begin with 978 or will contain N/A. There are no blank cells in this column
    Column B - Grade


    In the formula above, cell D2 represents the Grade criteria. And in the second part of the criteria I was trying to create a wildcard so all numbers beginning with 978. Excel "accepts" this formula with giving me an error message, but yields 0 as a result rather than 34. I just don't understand what I am missing. Thanks so much!

    Re: Formatting Nested If Statement


    Quote from ByTheCringe2

    Try:


    =IF($F8="X",IF(D8="","",IF(D8=D7,L7+1,1)),"")


    Cringe----


    That doesn't quite work. It gives a result of 1 for EVERY cell that has an X in column F. Column C (the Title) should determine whether the result would be a 1 (new Title), or the next consecutive number for a repeated title.

    Okay, I have spent the last 2 hours trying to figure out why this formula will not work. I am guessing it is probably something simple like a "format" issue. Okay here is my formula, and the data it represents.


    Column F is The Primary Tool. This cell will have X if it applies, or be blank if it does not. the formula is based on whether or not there is an X in this field.
    Column D is the Tab Title. This cell can be empty or contain a title. If the cell is Blank, the result should be blank, if the title is the same title as the cell above it, the result should be the next consecutive number. And if D is neither blank nor the same title as above it, it should display 1.
    Column N is where a numerical value for each title is displayed.


    =IF($F8="X",IF(D8="","",IF(D8=D7,L7,L7+1,(IF(D7="",1),""))))


    Thanks so much!

    Re: Concatenate With If Criteria


    I see the difference in where that bracket was placed. Thanks Ger, it works like a charm! Cringe, thank you also for responding to my "cry for help"! This works perfectly, and based on some of the spreadsheets that other provide me, this is definately something that I will use often, and now I finally know how to get from point A to point B to get the results I want. As always, Oz has come through for me again! :thumbcoo:

    Re: Concatenate With If Criteria


    Ger,


    I don't mind "a small mess"! And that does work.... for the most part. The beginning of the formula references Cell A4 with an IF Statement. Now, using your formula, when that number is a 1 or 2, rather than a 3, the result is "FALSE". I initially added that portion to my original formula so the cell would show as blank. Is there an additional adjustment we can make to fix this? Thanks again!

    I created a concatenate on a co-worker's spreadsheet so I could utilize the data, and it works fine. But then I realized when reviewing the results, that there are MANY instances where not all of the fields that the concatenation pulls from contain data. I verified the source, and found out that any of these fields may or may not contain data. Now I need to account for this within my formula.


    The cells being concatenated are in columns G-K, my first formula (in cell L4) is:


    =IF($A4=3,CONCATENATE(G4,"; ",H4,"; ",I4,"; ",J4,"; ",K4),"")


    Now I am using a "; " to separate between each cell's text. I need to adjust this formula to account for the fact that any of those cells MAY not contain data. And if it does not, I do not want the separator ("; " to show), because otherwise, I get entries that display like this:


    up to 10.; ; 1.NS.1.1


    If necessary, I can easily place my separator in a specific cell (M1 would be good) and access it from there rather than the way I initially wrote the formula. Thanks in advance!

    Re: Multiple Concatenate Based On Len


    Perfect Andy! Thanks so much! Thanks to both Andy and Brian! I know nothing about using the "&" and "REPT" so I would have (wait I already did!) have some major problems getting this resolved on my own! Thanks again so much, y'all rock!

    Re: Multiple Concatenate Based On Len


    Andy----


    That one didn't work 100% either. Where there was no data in column X, then the code in cell $R$1 did not display at all at the end of the result. That appears to be the only proble with the formula. I have tested it with multiple variables.