Search results

Hello Gael - What bobhc is saying is that this cannot be done if the column contains merged cells. Suggest you find a workaround. Most of the time, the merged cell look (so dear to your users) can be achieved with Centre Across Selection. - juanito

@KenU: thank you once again and this issue now looks totally fixed! I will explain a little more for the benefit of other readers: once again we ran into a case where INDEX returned an array in the name manager which in actual fact was of a different size than it looked when array-entered...

@KenU and others. I ran into another issue. KenU created a vector in his reworked model which looks exactly what I wanted. However, when I try to plot this vector in a chart, I only get the first value returned, instead for the entire vector. (See uploaded workbook.) If I visualise the...

I checked the formula in C5 and it looks like INDEX is overshooting by 1. Try subtracting 1 from the sum of the two MATCH functions. - juanito

From the dataviz perspective and the dataset, I'm wondering why you choose 100% stacked bar over normal stacked bar, where the overall volume is graphically displayed as well as the proportion between types. Maybe because you changed the dataset in the example file? Or do you still consider...

@juzzvinay: yes, a fully automated approach will be ideal if you can make it work. In my experience and organization, up to now I've found workarounds so that I can use the RDBmerge with a minimum of fuss. YMMV. - juanito

@juzzvinay - I have had similar data-gathering challenges across my organization. By far the simplest solution I have found has been using Ron de Bruin's free merge add-in here. Check it out: in Ron's website there are also lots of other examples of similar processes. Let me know how you get...

@KenU: I have finally had a chance to look at your file. Really interesting and instructive use of vectors and MMULT (and very clearly documented too): this will be extremely useful to me as I develop my model. Thank you! - juanito

Down the pub, ideally

@bobhc: I (an occasional contributor only) have found this when I press the "reply" button... which I have done exactly now to illustrate what I mean. The default is to reproduce the original message (with a fade-out, thank goodness), but @venkatesh in this case (and this is just an example)...

@SirJB7: I can't help you with our esteemed mademoiselle, I'm afraid - I'm sure you understand. But I feel your pain, my intriguing friend! - juanito

Ebb and flow, Narayan - and quality over quantity. I go in surges, myself! - juanito

Best wishes from juanito!

All the best, Somendra and thanks for your recent support: I appreciate big-time. - juanito

You are some interesting guy, SirJB7: kudos to you. *** language alert *** ¡Que no pierdas esa energía tan prodigiosa que tienes! *** end alert *** - juanito

Here's looking at you from juanito too, less and less a lurker now, but always an admirer and appreciater. - juanito

OK then... There's this lady whose phone suddenly starts signalling messages: "Darling, I have bad news. I had a very bad fall at work and hurt my leg seriously. Thank goodness Paula was there and she rushed me to hospital. But diagnosis bad: multiple fractures, infection, risk of necrosis. I...

@SirJB7, @Somendra Misra, @Haseeb A: I have also been experimenting with the behaviour of INDEX() and unearthing erratic, or unexpected, outcomes. I will upload a file with my findings later this week and it would be great if we can continue this shared analysis (*). INDEX() is such an...

Friends - thanks for all your contributions over the last days. For some reason I wasn't getting the advices and have only just read the last few posts (thanks for tagging me, Somendra - that's what got through). Time to see in 2014 now, so I'll catch up tomorrow. Best to all, and Happy New...

Very valuable insights, SirJB7, thank you for sharing them. I would only add that what is critical and non-critical is also a grey area to some extent: enterprise leaders must decide how to allocate resources of course, but effective leaders will also listen. IT is not always sufficiently alert...

Interesting thread! I'd say that in many front-end jobs, advanced Excel skills (and Sajan is correct: there's no real consensus around what that means) are a Big Plus. But then, so are a lot of other things! Knowing your field is pretty necessary. Yet in the long run, "soft" skills - "how" more...

Hi Somendra! I think you can see what SirJB7 is saying in the file he has uploaded. You should also be able see what is still puzzling me: I had tried exactly the same approach, however, because the array formula was displaying different values from what it actually contains, I believed I had...

Ah! Now that is very interesting... and quite mysterious. I think that will cover my needs: thanks very much, SirJB7! But the mystery (and this is what was confusing me) is this: cell M3, for example, is displaying 10. However, as you have shown (and I've verified with F9), in fact it contains...

Gracias, Don JB7: estudiaré a fondo sus indicaciones *** end Spanish version of Chandoo Excel Forum *** We're sort of way off-thread now, but this does raise interesting points about international versions of Excel, especially for people like ourselves, who toggle between languages. ¡Felices...

More on date behaviour. For the date headers in tables issue, I have overcome this difficulty in some of my models using coercion to force the field into a number (multiplying the header field by 1, for example, like we do with booleans to turn TRUE into 1 and FALSE into 0). However, when I...