Search results

I appreciate your support, Somendra! - Juanito

Hi Somendra - I really appreciate your analysis and support. I was hoping to find a formula-based solution as I'm sure you understand, but your VBA approach is very interesting and I will further analyse over the next days. Very best wishes and Season's Greetings, - Juanito

There isn't a name... I tried to do it but the INDEX function didn't return the discrete vectors as I hoped. It's where I got stuck. That's why I'm wondering if the forum can help me find a workaround. I want to multiply "_d_oee" by each column of "oee_mth_array", to obtain each month's OEE...

OK here it is. To explain a little: Almost everything is being processed not in the grid but in the name manager I have a vector of values in name "_d_oee". Each value has a date associated with it held in vector of identical length "_d_dates" I have a 2D array of booleans, number of rows same...

OK, I will upload something... might take a little while. Thanks for following this! - Juanito

Somendra - I don't see the difference between what you're suggesting and my example above: =INDEX(d,,col_vec) Where col_vec is a vector of 1 to 5 Could you illustrate a little more? Thanks! - Juanito

Hi Somendra - Thanks for looking at this. Yes, my formula INDEX(d,,3) works fine. (It's in the name manager, which is the same as array-entered.) But I can't get it to work when I pass it a vector as you say of {1,2,3,4,5} as the column argument. I believe this is a limitation of INDEX...

I have an 2D array - in the name manager, not the grid. My model uses INDEX to return discrete columns of this array. So if the array's name is "d", then to return the third column I have: INDEX(d,,3) (This is using the awesome power of omitting the row argument in INDEX - you get them all!)...

Have a look at this, too! (Great spreadsheet site BTW) http://www.hichert.com/en/shop/promotional-items/159 Merry XL-mas to all from juanito

Thanks Narayan - great solution - Juanito

Hi - I have a vector that I'm feeding into a chart. Let's say it's {1,3,4}. Because of the way the chart's set up I need to transform the vector into {1,0,0,3,0,0,4,0,0} - so I'm inserting two zero values between each vector element. Any ideas how to do this? Thanks, Juanito

Amazing, Luke: thanks! - Juanito

I have a table of values with headings along the top and down the left-hand side. Months and soccer teams respectively, for instance. I need a formula which returns both headings of the largest value. Thought this would be easy-ish, but I'm having a bad day... Any Forum help will be much...

Interesting stuff. Thanks for your explanations, Narayan: I think it's because of the 2D array. Michal: my formula will only work on a 1D array (vector). You should follow Narayan's way - he's a ninja after all! Alternatively, use Luke's UDF (if you know how to include it in your file) -...

Nice formula Narayan - I hadn't noticed that the array is in 2D. One question - do you actually need to use the MAX function or will it work without it? - Juanito

Try this: =ROW(INDEX(array,MATCH(name,array,0))) (Same for COLUMN) - Juanito

Instead of using an additional column you can also highlight duplicates using conditional formatting - formulas same as the ones already shown - Juanito

Yes, you can do that. Choose the text box, go to the formula bar, type = and then choose the source cell. Then select the text box, go to Size and Properties and make sure it's locked. Protect the worksheet and the text box is now uneditable. I believe there's a 256 character limit on the...

Hi - Yes that works - the issue is that the sheet is part of a large reporting deck, so I can't just print off with print by selection? I want to be able to print the whole thing in one go. Even odder, is that if I print to pdf the sheet looks fine... wow. - Juanito

Hi - I have a simple report with commentary below in a text box which is print-enabled. I tell Excel to print the whole sheet, center horizontally, sizing to one sheet landscape and so on - all pretty elementary. Problem is, the report isn't centered horizontally - it's forced to the left...

I add my congratulations! - Juanito

Hi Shaun - suggest you give an example of exactly what you want to do... I'm lost like Narayan. @Narayan: Shaun's dates are DD/YY - Juanito

Hi Deepak - With 31 elements your columns will always be thin becuase there are so many of them, unless you stretch the chart a long way sideways but then you'll probably have trouble visualizing it properly. Normally, when you're looking at a time series like in this case, a line chart is a...

If I understand your problem correctly, then you need to select the data series, go to format data series (right-clicking for example) and reduce the gap width. Reducing the gap width increases the column width. Hope that helps. If we're not addressing your issue correctly then I suggest you...

Hi bobhc - not sure if it applies to you personally, but given how many participants on Chandoo are from the Indian subcontinent it's probably worth pointing out that Spanish flamenco music is directly descended from Indian music via the Gypsies! If you don't use TEXT like in your direct join...