Search results

@Lymm I wouldn't mind the Excel 2003 version, thanks, John

Hi, rlburden You can enter text and press alt where you want to break the line. Or if you are looking to apply this format repetitively guess some VBA is required, something like: Sub ShowText2Lines() Dim Text2Show As String Dim Text1stLine As String Dim Text2ndLine As String...

Hi shaman yes you are right, I forgot to mention this. But this code does makes the spreadsheet cumbersome to use so it best suits a situation of minimal data entry. For instance in my roster after you have set up week 1 for ease of data entry you then want to copy week 1 to week. 2. So I...

Hello Chaos I have the same issue with a roster that relies on validation which can easily be wrecked by copy/cut/paste drag and fill. I got this code from somewhere a few years back which you copy into the VBA Module ThisWorkbook to work. It's pretty tight I find and only allows manual entry...

Hello This is a very common problem for me, I am not sure if one of my macros or add ins that turns off my Drag and Drop. I suspect it's a macro I use to turn off copy/cut/paste functionality to protect a roster that relies on data validation a lot. So I have this simple macro that you can...

Thanks Narayan I appreciate the explanation and get it (sort of). It obscure and you have to marvel at this one as I think it's left field. cheers

@Chandoo sorry I still don't get it but I think what you could be saying is this..... the validation formula initially entered characters into spaces but only where those spaces have been created by substitution of 1,2,3,4 so all other characters are rejected - somehow the spaces are then...

Hui the silence is deafening after this one. No one wants to admit to being an accountant! :) I like your dynamic data formula for Data buried in this answer - that is an important concept and worthy of it's own post one day I think.

This is why I love this site that's an amazing solution Chandoo. But could you explain this one just a little more?

Yes its hard to figure what the issue is and why arrays are part of it - but maybe it's very simple? Save the workbook first in case but it sounds like you could just copy your formulas that are referencing the source info correctly down the page from row 1 to what ewver it needs to be to ref...

Deb this site might be worth checking out as it has a focus on Excel & Outlook working together and automating possibilities which might be what you want. But then again maybe you want something dead simple and intend to just fill it in yourself manually, it's hard to tell. If this is the case...

Thanks Hui I was just saying to asa if there is one habit I need to adopt it is in using this Object Model as I code - here is the link for 2003 for anyone else stuck on this version (like me). http://msdn.microsoft.com/en-us/library/aa272268(v=office.11).aspx

Thanks asa, being a male I must admit I don't 'read the instructions' until I am forced to kicking and screaming! I better improve on this attitude as I am wasting too much time on not getting variables declared correctly then it compounds further down the code as you do other things trial and...

ps Luke I just made the changes you suggested and the the interesting thing is vWB does have to be declared as Variant (I tried string which didn't work it just = False) for the routine to work. I think the reason is the Getopenfilename result needs Variant as the result can be FALSE so I am...

Hello Luke thanks very much for this, that really answered all my questions, I really appreciate this. I guess the fact Getopenfilename is a string not an Object was the start of my confusion. I assumed the opposite but now I look at help it says it very clearly. Thanks again Luke thanks for...

Hi everyone. I have written some code below that works after much trial and error but it worries me that I don't understand why. This code is to allow the user to select the right .xls file to open from a defined folder, first test if the chosen file is already open and then either open it or...

And just to add on to this if you create 2 empty sheets and name them say start and end and use these as your sheet references then your formula will be dynamic so long as all other sheets and new sheets added are between these 2 empty "marker" sheets. John

Narayan no problems, and I didn't want to seem to be correcting you as I have observed your very broad knowledge as you have answered a heap of wide ranging problems, so great stuff and keep it up. I only know about this little issue because I have a lot of situations where a dump of data from...

Hi Narayan & Lo Baan On the issue if cf being based on cells in other worksheets, you can do this using range names. A bit like validation which only works for cell references in the same sheet unless you use a range name pointing to a range in another sheet. In the cf when entering your...

Hello again Ridgleye No problems at all, feel free and perhaps others will have input to help you. Rostering is a thankless, tedious, never ending job and it can be Excel to the rescue!

Narayan yes you are right that is the right sequence '0,1,4,5,6,9,10, I figured Fred was just out a bit for some reason but the approach was spot on as I had to put it into a worksheet to figure out how it worked.

Ridgleye found this on YouTube by accident which should interest you. http://www.youtube.com/watch?v=Ava2KXXBktU

Fred and Luke that's a really impressive approach, the penny just dropped for me on why you are using MOD, well done!

Hey Hui I have been following this one with interest as I have a practical use for it. Chandoo's formula returns the highest repeat name no matter the order - your variation is returning the name used first although not the highest repeating. fred fred bill bill bill bill fred Hui fred (3...

One more thing. Protection. I hardly ever worried about protection using Excel in mining companies which I worked in for years and years - users were Excel savy enough to not need it and didn't clobber formulas etc. But with this Roster and the staff not being Excel proficient at all, range...