Recent content by mrstaggart

While the replacement of the repeated INDEX & MATCH formulas still took forever for all 4,000 rows, all following updates to the same formulas takes much less time. So, while I love the INDEX & MATCH formulas, with their relative references, I will be more careful about inefficiency in the...

I've asked this question before and didn't follow forum rules and search even through my own posts to find an answer. I will withdraw this in deference to previously posted recommendations. Sept 2012 - Thread68-1693959

I have to add rounding to one VLOOKUP in a complicated formula in a price-determination worksheet and copy it to 4000 non-consecutive rows. When I do this, it takes approx. 25 seconds per cell, estimating 24 hours for all. Is there a better way? I can't use search-and-replace because of the...

@SkipVought: I'll look at the Structured Table functions to see if it will work with this. @Firefytr: I have and the Pivot table doesn't meet the need - this is a model of client activity and the details are important. Actually, this data becomes a pivot table on another tab to summarize when...

Thank you for the quick reply. This file is a model to take all client revenue order data and then summarize it without using subtotals because the summary or the details are used by others; subtotal functions won't work in the other views/summaries/user processes. So, I filter, paste, then...

If I use SUMIF or CountIF formulas then paste to the 20 cells in a filtered list, it takes 5 minutes (I've timed it with a stopwatch) and one of my dual CPUs is pegged the whole time. I have turned off auto-calculation and it makes no difference. As far as I can tell, it seems to be taking...

I was hoping to avoid the unprotect/reprotect option (I do it elsewhere in the form). Alas. Thank you so much for thinking through my issue - I really appreciate it.

Good questions - sorry I missed them before: drop down control is Data Validation. Blank is literaly blank - the validation looks at range AA1:AA5 which read: AA1 AA2 Dairy AA3 Fruit AA4 Meat AA5 Grain

I am making a form for the sales team. I have a drop down to select a product. With a macro attached to a button, the macro filters an item list based on the selected product. (Similar to this thread as a start point:http://www.tek-tips.com/viewthread.cfm?qid=1632093) The form is locked, but...

That is a great solution! Another I've used is the MID formula based on the extracted number above. Like this: Data in A2 11111, 22222, 33333, 44444, 55555 =LEFT(A2, 5) you get ---->11111 =MID($A$2, SEARCH(A3, $A$2, 1)+LEN(A4)+2, 5) you get ---->22222 where A2 is where...

This is awesome. It offsets by one cell, but I'll adjust it. Thanks for sharing your massive expertise!

We have a file created in Excel 2003 and now edited in 2007 in which the creators put all text in Comments attached to each cell and NOT in the cell itself as text. How, oh, how, can I export these comments? (2500 rows of data's worth >sigh<) I have tried writing a macro to copy them out and...

Thanks. This is very helpful. The CountIF is exactly what I needed. I can't quite get the SUMPRODUCT to work. if I enter it with ...(Status="D") I get "#NAME" error. If I enter...("Status"="D") I get 0. BTW, I forgot to mention I'm on Excel 2007, but that shouldn't make a difference.

I think this is close to what was in thread "thread68-1595108", but not quite. I have a work page from which 3 different people are assigned orders to fill and, as they complete them, they enter the text result of its status - "D" for done, "M" for missing or "H" for hold. I want to summarize...

That will get you the location of the text, but to get the actual text, you need to use MID: MID( A1, SEARCH("(", A1, 1)+1, SEARCH(")", A1, 1)-SEARCH("(", A1, 1)-1 ) Where your data is in A1 (text to search). SEARCH("(", A1, 1)+1 MID will give you the text starting at the...