Posts by corcelle


    I am trying to deliver a value in column "T" based on the value of 2 other columns (C & H). There are 18 possible combinations for C&H (3 options for C and 6 for H). I tried an imbedded IF AND statement and it worked until I saved it and came back, then I had the #VALUE error.
    Is there another formula/method to solve for this? My basic data set it below:

    If C1= AND H1= Then T1=
    H S DataA H R DataB H B DataC H T DataD H I DataE H M DataF M S DataG M R DataH M B DataI M T DataJ M I DataK M S DataL
    M M DataM L R DataN L B DataO L T DataP L I DataQ L S DataR L M DataS

    Thank you!

    I have a user form that populates a specific fields on a specific worksheet I have built. I have it look for the next or nearest available blank line to put the next entry. The code I am using is:

    The issue I have is that in the other fields in the row I want to have formulas that auto number and auto calculate once data is entered via the user form. However, if I put formulas in the cells to do this, even if I tell it to leave it blank until there is an entry, the user form logic above will think the formula is content and look for the NEXT blank, not the row with the blank fields for the user form data but formulas in other fields. Is there a way to have the user form populate fields in rows that have data in other fields?

    Re: Formula not working in lower version of excel

    You are a lifesaver! That appears to have worked. I have one other similar formula -- can you do a similar change? I appreciate the help -- I'm still learning a lot of these more complex formulas!

    =IF(D$3="January",'Plan - Monthly Program'!$G$10,IF(D$3="February",'Plan - Monthly Program'!$H$10,IF(D$3="March",'Plan - Monthly Program'!$I$10,IF(D$3="April",'Plan - Monthly Program'!$J$10,IF(D$3="May",'Plan - Monthly Program'!$K$10,IF(D$3="June",'Plan - Monthly Program'!$L$10,IF(D$3="July",'Plan - Monthly Program'!$M$10,IF(D$3="August",'Plan - Monthly Program'!$N$10,IF(D$3="September",'Plan - Monthly Program'!$O$10,IF(D$3="October",'Plan - Monthly Program'!$P$10,IF(D$3="November",'Plan - Monthly Program'!$Q$10,IF(D$3="December",'Plan - Monthly Program'!$R$10,IF(D$3="Q1",'Plan - Quarterly Program'!$G$10,IF(D$3="Q2",'Plan - Quarterly Program'!$H$10,IF(D$3="Q3",'Plan - Quarterly Program'!$I$10,IF(D$3="Q4",'Plan - Quarterly Program'!$J$10))))))))))))))))

    I wrote the below formula and it works well on my system in .xlsx file, as it was built, but another user has an older version of excel so I saved it down to .xls for her. Now the formula doesn't work/save . . . when I click on the cell it just says =#Value! now. Any suggestions? Is there a way to rewrite this formula for a lower version of excel? We may have users using a bunch of different versions so I want the most likely option if possible.

    =IF(D$3="January",INDEX('Plan - Monthly Program'!G$20:G$135,MATCH($C5,'Plan - Monthly Program'!$B$20:$B$135,0)),(IF(D$3="February",INDEX('Plan - Monthly Program'!H$20:H$135,MATCH($C5,'Plan - Monthly Program'!$B$20:$B$135,0)),IF(D$3="March",INDEX('Plan - Monthly Program'!I$20:I$135,MATCH($C5,'Plan - Monthly Program'!$B$20:$B$135,0)),IF(D$3="April",INDEX('Plan - Monthly Program'!J$20:J$135,MATCH($C5,'Plan - Monthly Program'!$B$20:$B$135,0)),IF(D$3="May",INDEX('Plan - Monthly Program'!K$20:K$135,MATCH($C5,'Plan - Monthly Program'!$B$20:$B$135,0)),IF(D$3="June",INDEX('Plan - Monthly Program'!L$20:L$135,MATCH($C5,'Plan - Monthly Program'!$B$20:$B$135,0)),IF(D$3="July",INDEX('Plan - Monthly Program'!M$20:M$135,MATCH($C5,'Plan - Monthly Program'!$B$20:$B$135,0)),IF(D$3="August",INDEX('Plan - Monthly Program'!N$20:N$135,MATCH($C5,'Plan - Monthly Program'!$B$20:$B$135,0)),IF(D$3="September",INDEX('Plan - Monthly Program'!O$20:O$135,MATCH($C5,'Plan - Monthly Program'!$B$20:$B$135,0)),IF(D$3="October",INDEX('Plan - Monthly Program'!P$20:P$135,MATCH($C5,'Plan - Monthly Program'!$B$20:$B$135,0)),IF(D$3="November",INDEX('Plan - Monthly Program'!Q$20:Q$135,MATCH($C5,'Plan - Monthly Program'!$B$20:$B$135,0)),IF(D$3="December",INDEX('Plan - Monthly Program'!R$20:R$135,MATCH($C5,'Plan - Monthly Program'!$B$20:$B$135,0)),IF(D$3="Q1",INDEX('Plan - Quarterly Program'!G$20:G$135,MATCH($C5,'Plan - Quarterly Program'!B20:B135,0)),IF(D$3="Q2",INDEX('Plan - Quarterly Program'!H$20:H$135,MATCH($C5,'Plan - Quarterly Program'!$B$20:$B$135,0)),IF(D$3="Q3",INDEX('Plan - Quarterly Program'!I$20:I$135,MATCH($C5,'Plan - Quarterly Program'!$B$20:$B$135,0)),IF(D$3="Q4",INDEX('Plan - Quarterly Program'!J$20:J$135,MATCH($C5,'Plan - Quarterly Program'!$B$20:$B$135,0)))))))))))))))))))

    Re: If "RANGE" then "RANGE" formula?

    OK - what if my reference data is not in two contiguous columns? I realized that my schedule steps are in column B but the dates are in column G, with other information in between. I can't get Vlookup to work with that.

    Quote from NBVC;671053

    Try Vlookup.

    e.g. =VLOOKUP(A2,'Tab 1'!A:B,2,0)

    where Tab 1 is name of your sheet to reference.

    Hi there,

    I have a schedule on one tab that lists steps in one column and corresponding dates in another.
    On the next time, I have an abbreviated schedule with only certain steps from the full schedule . . . I want to right a formula that says if the step on the abbreviated schedule matches the schedule on the first tab, then return the appropriate date.
    For example:
    TAB 1
    Column A: 1000 Steps
    Column B: Scheduled Dates
    Tab 2
    Column A: 450 Steps

    Thank you in advance!

    Re: User Form Macro Issue

    Quote from royUK;654983

    Looks to me as if RowCount will increase as data is added so that isn't the problem. RowCount is based on the CurrentRegions rows count

    Yes, but I wanted to auto-populate column A with sequential numbers (Project #) when data is added. If I add a formula to do this, the user form thinks the row is populated and adds the data for the user form to the next empty row, instead of that row.

    Re: User Form Macro Issue

    Terrific -- it seems to work now, strangely, and your change to RowCount worked as well!
    I have one final issue . . . I want to auto-number the first column with sequential numbers. I was using the ROW() function, however when I put this function in column A for the available rows, the macro starts skips all those rows and puts the entry into the first row without anything in column a. Is there a solution for this?

    I have been slowly learning VBA language and macros, and built my first user form yesterday. I was able to successfully get the form to open via a button, clear entry, close the form, and produce an error message when the form isn't completely filled out. However, the final and most important step involves add the information entered into the user form into the actual excel workbook, and that part is not using.

    I have been using the following with statement, and for some reason it is posting the first entry into my desired field but then skipping a bunch of columns before posting the rest. Any suggestions? I will attach the workbook as well.

    Thank you for your help!

    Re: Pulling highlighted cells into other cells

    My apologies . . . They do not have to be in order, necessarily, but I do want the all values for each score to make the list (in any order) before the next sequential value. In other words, if the score list includes 3 15's, 2 10's, 4 8's, I would want the list to include all of the 15s, not just one of them.

    Re: Pulling highlighted cells into other cells

    Hmmm . . . It's still not pulling all of the highest value before it pulls the next highest value. For example, on Agency Expertise tab there are 2 scores of 15, which is the highest score. It's pulling the first one first, but then it's skipping to the first 12 instead of listing the next 15 in the second slot.

    Re: Pulling highlighted cells into other cells

    This works, except it does not account for multiple of the same value in column D. It's taking the first of the value, but then the second in the list is the first of the next largest number . . . I need the top scores, including ones that are tied.

    Re: Pulling highlighted cells into other cells

    OK -- see attached.

    The Scorecard Tab is meant to be a summary of data from the subsequent 2 tabs.

    On both the Agency Expertise and Agency Organization tabs, I have used Conditional Formatting to highlight the Top 5 values in Column D.

    Ideally, I would like to list on the Scorecard Tab under Top Criteria the value from Column A if Column D is highlighted.

    Make sense?

    Thank you!

    Quote from Mumps;646961

    We need to know where the highlighted cell are located and a few other details. Perhaps you could post a copy of your file with a description of what you want to do.