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.
Posts by hmsiegel


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.
ThanksHarlan

If it helps at all, I will attach the spreadsheet.
The two sheets that I am having trouble with are the "Clean and Jerk" sheet and the "Clean" sheet. I have not been able to see any errors in the formulas, but maybe someone else will. 
I've created a workbook with a number of worksheets with charts on them. I've created named ranges for both the xaxis and yaxis 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;767713I'm not a real formula whiz, but with your data I managed to throw something together that works.
In G1 with your data.
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:
[tr][td]
[TABLE="class: grid, width: 500, align: left"]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. 
This should be a relatively simple formula, but I am finding it quite difficult. I am trying to find the max value out of a set of three values, but I want to not include one of them if it is struck through. I am using conditional formatting to do the strike through. Is this possible?

Re: Lookup Grade Calculator
Perfect!!!!
That worked. Thank you very much.

Re: Lookup Grade Calculator
Sure.
The final grade is in cell F11. I would like cell H11 to display the letter grade.
Let me know if you have any other questions.Thanks

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 likeBut 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 addin, 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 thirdparty addin 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 addin. Also, the addin 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
Quote from Smallman;596886Hi
This sounds like a job for a pivot table. Forgive me if this is overly simplistic but the data as you describe it will summarise the infomration well.
Take care
Smallman
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

Re: USD $50 or negotiable for Automation of a COM Add In
Thanks though.

Re: USD $50 or negotiable for Automation of a COM Add In
I will PM you with the link to download the addin.