Posts by hmsiegel

    Attached is a sample of what I am trying to do. The first worksheet is the master (Before1). The second worksheet (Before2) would house the concepts that I am trying to combine. The third worksheet is the result I am looking for. And if there is another way to get a similar result, I would be fine with doing that too.
    Thanks for taking a look.

    I have 2 worksheets. The first is the master worksheet with the majority of the information needed. For example, column A would be the ID number. Column B would contain the "SQL query" that I need. This query would pull from the second worksheet. Column A on the second worksheet would also contain the ID numbers. Row 1 would contain the headers that is the information that I want to pull into column B on the first worksheet. In the cell where row 1 and column A meets, an "x" would be placed to indicate that that value (in the header) needs to be used. There can be multiple values in each row. How can I write a function to do this? Is it even possible in Excel? Is there a better way to lay out the data? Any help is appreciated.
    Thanks


    Harlan

    I've created a workbook with a number of worksheets with charts on them. I've created named ranges for both the x-axis and y-axis for each chart, using the same formula, just changing the worksheet that the formula references. The workbook is to chart my progress in a variety of lifts, FWIW. For reference:


    For the estimated 1 RM column =OFFSET('Back Squat'!$E$2,1,0,SUMPRODUCT(--('Back Squat'!$E:$E<>""))-1,1)
    For the date the lifts were performed on =OFFSET('Back Squat'!$B$2,1,0,COUNTA('Back Squat'!$B:$B)-1,1)


    I have been able to then modify the formula for each chart to use the named ranges instead of the hard coded ranges, EXCEPT on two sheets, and I have no idea why. Everything else is exactly the same. Any idea as to why two of these worksheets would not want to update?

    Re: Find the max value if the value is not struck through


    Quote from skywriter;767713

    I'm not a real formula whiz, but with your data I managed to throw something together that works.
    In G1 with your data.

    Code
    1. =MAX((((B1:F1)<>"x"))*(IF(ISNUMBER((A1:E1)),A1:E1,0)))


    After copying the formula into the cell you must use Control + Shift + Enter to confirm the formula, not the usual Enter.


    Thanks. That works.

    Re: Find the max value if the value is not struck through


    What I have is something like this:
    [TABLE="class: grid, width: 500, align: left"]

    [tr]


    [td]

    A1

    [/td]


    [td]

    B1

    [/td]


    [td]

    C1

    [/td]


    [td]

    D1

    [/td]


    [td]

    E1

    [/td]


    [td]

    F1

    [/td]


    [td]

    G1

    [/td]


    [/tr]


    [tr]


    [td]

    60

    [/td]


    [td][/td]


    [td]

    63

    [/td]


    [td][/td]


    [td]

    67

    [/td]


    [td]

    x

    [/td]


    [td]

    63

    [/td]


    [/tr]


    [/TABLE]







    G1 would be the cell where the MAX value is found. The range is A1, C1, E1. Using conditional formatting =IF(B1="x",TRUE,FALSE).
    I am open to other methods and the structure is not set in stone, although I would prefer horizontal because there is other information.

    I should know this, but am having some trouble. I created a grade calculator for my classes. I would like to assign a letter grade to the point value. I already have columns with the grade letters and minimum and maximum values needed. I can't figure out the formula I need to assign the grade letter based on a lookup between the two values. Would anyone be willing to help me with this?


    Thanks

    Re: Calculate Values for a Women's Weightlifting Bar


    Thanks. That seemed to do it. I didn't know about the CONVERT function. That's much easier than multiplying by 2.2. Probably a little more accurate too.
    I made a slight adjustment to it, which seems to work perfectly.


    =MROUND(CONVERT((('starting weight'*'percentage')-'weight of bar in kg')*1000,"g","lbm"),5)+'weight of bar in LB'

    Re: Calculate Values for a Women's Weightlifting Bar


    No. Here's an example. I have a female athlete with a beginning weight of 42 kg. I want to know what 80% of that weight is in pounds, rounded to the nearest increment that she can load on the bar. Because the bar is 33 lbs (not 45 lbs), it's a little more difficult. 42 kg in lbs is 92 lb. 80% of that is 74 lb. But, because the bar is 33 lbs, and the plates are all in increments of 5, she can only load 73 lbs.
    Next example:
    Beginning weight is 44 kg. 44 kg in pounds is 97 lbs. 80% of 97 lbs is 77 lbs. But the bar can only be loaded with 78 lb. (33 lbs bar + 45 lbs in plates).


    Does that help? Make a little more sense?


    Thanks

    I'm trying to create a spreadsheet that will calculate percentages for weightlifting, in both kg and lb, for both men and women, given the starting weight in kg. I'm sure I'm missing something fairly simple.
    For the kg for both men and women it is easy. And the percentages for the men are pretty easy also. The formula for that looks something like


    Code
    1. =MROUND(('Beg Weight in kg' * 2.2)* 'Percentage', 5)


    But I can't seem to figure out the formula for the women. The issue is that the women use a 33 lb bar (15 kg). And the plates are in multiples of 5. So the value will always end with either a 3 or an 8.


    Any thoughts?


    Thanks

    Re: Opening and Saving a Excel File/ Add In


    Thanks royUK. I've tried setting the links to update automatically, but it still prompts to update them. I've even tried changing the setting under File --> Options --> Advanced --> When Calculating. The setting there isn't taking effect. If I clear the check box, the next time I open the file, the check box is checked. Not sure what is going on.


    I wouldn't mind uploading the files (or sending them by email).


    Thanks

    I have two issues with the workbook that I'm working on. First, a little background: this "program" consists of two workbooks, a business logic workbook, and the user interface. The business logic wb is setup as an add-in, and is hidden. All of the code will be housed there as well as about a half dozen worksheets that are essential for the program. The user interface workbook is just reports that we run, which uses a third-party add-in to connect to a database. I have used a good number of named ranges in this workbook so that updating is easier if need be. I have set up named ranges and constants on the user interface that reference the add-in. Also, the add-in is the workbook that is to be opened first. There is then code to open the user interface.
    The first issue: when opening the program, it seems to take a very long time. Also, I always get a dialog box to update the links. But when I click on update links, the dialog displays "Source is already open." I've tried to make adjustments so that links are automatically updated and the dialog doesn't show, but to no avail. Any ideas?
    The second issue is that the workbook seems to take a very long time to save. Any way to shorten the time? Excel will blink a couple of time, I'll get "(Not Responding") in the title bar, etc. It finally does save, but it takes a while.
    Any and all suggestions are appreciated.
    Thanks
    Harlan

    Hi there,
    I can't figure this one out. I would like to do this without VBA if possible. I'm trying to compare three values, that are in three separate columns and then highlight a cell associated with (but not the actual cell) the highest value.


    For example:


    I have sales values for restaurants in columns. Restaurant A uses columns A, B and C. Restaurant B uses columns D, E and F. And restaurant C uses columns G, H, and I. The first column (A, D, G) for each restaurant is the current year's sales. The second column (B, E, H) is the previous year's sales. And third column (C, F, I) is the difference between the current year and the previous year. What I am trying to do is highlight the current year column for the restaurant that has the greatest change from the previous year.


    This one has me stumped. Any help would be appreciated.


    Thanks

    Re: Summary Page for Ledger



    I don't know why I didn't think of that. It worked perfectly. Thanks

    My fiancee has asked me to help her with this.
    There is a ledger with the following columns, in the following order, left to right:

    • Date
    • Paid To
    • What
    • Amount
    • Payment Type
    • ID#
    • Category 1
    • Category 2
    • Reconciled


    The columns that are important are amount, category 1 and category 2.


    I have already created dependent validation lists for category 1 and category 2 (category 2 is a subcategory of category 1).
    On a separate sheet she would like to see a total for each of the items in category 1 and a break down of what was spent in category 2.


    I've created a register before, using DSUM to get the total, but that doesn't seem to work in this instance.


    Any thoughts or suggestions?


    Thanks