# Off Topic > The Water Cooler >  >  Call in the Cavalry - 2014

## JBeaucaire

Any Pivot Table assistance for this OP?

http://www.excelforum.com/excel-gene...orksheets.html

----------


## :) Sixthsense :)

OP just want to check whether the time in the cell is not within 2 hours from the current time and want to highlight it with CF.  I don't know what I am doing wrong with this thread  :Confused: 

http://www.excelforum.com/excel-form...n-2-hours.html

----------


## JBeaucaire

Getting on a plane and can't pursue this, should be a simple INDEX/MATCH or INDIRECT....

http://www.excelforum.com/excel-gene...-switches.html

----------


## Izandol

Poster here may benefit from someone with more tolerance than I: http://www.excelforum.com/excel-char...ml#post3528729
Thank you.  :Smilie:

----------


## benishiryo

anyone knows of any limitation of Named Range when doing 3D referencing for COUNTIF?  tried to search for it but couldn't find any related article
http://www.excelforum.com/excel-form...t-working.html

this doesn't work:
=SUM(COUNTIFS(A_Series,B2, B_Series,B3, C_Series,B4))

but somehow, changing any 1 of the Named Range to the formula works.  for eg.:
=SUM(COUNTIFS(INDIRECT("'"&{"Sheet1";"Sheet2"}&"'!A:A"),B2, B_Series,B3, C_Series,B4))
or:
=SUM(COUNTIFS(A_Series,B2, INDIRECT("'"&{"Sheet1";"Sheet2"}&"'!B:B"),B3, C_Series,B4))

----------


## ChemistB

Can't wrap my head around how to solve this one simply.

http://www.excelforum.com/excel-form...lculation.html

----------


## daffodil11

I'm not sure what I stepped in here. A lack of coffee might be all that stands in my way. Or it could be a lot worse.


https://www.excelforum.com/showthread.php?t=979584

A known Row label, a known Column which repeats; find MIN value for intersection of row and columns.

Tried mod to min every nth column, even sumproduct. I'm out of my league.


*Edit:* Thanks for bailing me out martinwilson

----------


## ragulduy

Would appreciate it if someone can take over here:
http://www.excelforum.com/excel-form...y-figures.htmlI'm lost..

----------


## FDibbins

Anybody good at Hyperlinks across a network?

http://www.excelforum.com/excel-gene...ml#post3536279

----------


## martindwilson

cannot see what op wants
http://www.excelforum.com/excel-form...t-isblank.html

----------


## alansidman

this one is just beyond my knowledge and could use some VBA assistance

http://www.excelforum.com/excel-prog...-workbook.html

Thanks guys

----------


## Jacc

This should not be too difficult but I'm running out of time.
http://www.excelforum.com/excel-form...n-a-range.html

----------


## ChemistB

This will require VBA

http://www.excelforum.com/excel-form...ther-page.html

----------


## alansidman

Changing sheet names with dates.  I am at a loss on how to take this to the next step to cure this issue.

http://www.excelforum.com/excel-prog...ate-range.html

Any takers

----------


## FDibbins

After opening the file in this thread...
http://www.excelforum.com/excel-new-...-my-sheet.html

When I try to set the rows to appear at the top of each sheet, it wont let me in there.  Any suggestions??

----------


## TMS

Can someone step in here and either back me up or offer an alternative explanation or approach.

If macro password cracked then kill thisworkbook !

The OP wants his code to activate and kill the workbook if the VBE is visible ... that is, if someone has removed the VBE Password protection.  I've tried to explain that the chances are the code will not be active and so will not kill the workbook.

Am I just not explaining it very well?

Thanks, TMS

----------


## alansidman

I cannot provide a viable solution.  Appears to be over my skill level.  I think it involves a worksheet change event, but I can't get it to work.

http://www.excelforum.com/excel-prog...ate-value.html

----------


## Pete_UK

You can see from my comments at the end of Post #9 in this thread:

http://www.excelforum.com/excel-form...-solution.html

that there is something strange with the cells that appear to be empty in the file that the OP provided in Post #3.

I eventually solved the problem, but I was wondering if anyone can shed any light on the behaviour of the empty cells for the first material.

Pete

----------


## :) Sixthsense :)

> something strange with the cells that appear to be empty in the file that the OP provided in Post #3.



Not sure you are talking about which are of the cells in that file  :Confused:

----------


## alansidman

http://www.excelforum.com/excel-prog...e-add-row.html

I have provided the OP with his steps 2-5 as he requested in the early part of the thread.  He is now asking for additional actions with the macro.  I don't think it is something that can be done, unless it is with a Worksheet Change Event.  I'd really appreciate someone else looking at this.

Alan

----------


## Solus Rankin

I can figure out a rule that the address formats follow that would allow a clean answer to this post: http://www.excelforum.com/excel-prog...-shortcut.html

Also I can't figure out how to do the country abbreviations without a lengthy list defining them.

----------


## MrShorty

For my benefit and the OP's, does someone know how to change the "how should excel treat blanks in charts" options in 2007/2010. I know how to do it 2002/2003, but not in the later versions: http://www.excelforum.com/excel-char...ml#post3552017

----------


## Fotis1991

Anyone that is able to assist here?

http://www.excelforum.com/excel-form...-one-cell.html

----------


## alansidman

I'm stumped on this.  Any assistance?

http://www.excelforum.com/microsoft-...is-denied.html

----------


## :) Sixthsense :)

Even I am also curious to know the solution  :Smilie: 

The issue is the Turning off the Screen updating won't worked for the Worsheet Change Events and from my experience which makes the flickering even more.

Any solution for this issue?  :Confused: 

http://www.excelforum.com/excel-prog...t-working.html

----------


## :) Sixthsense :)

Any suggestion why this can't be able to get the shown result with the help of Pivot Table - Multiple Consolidation Ranges?

http://www.excelforum.com/excel-gene...data-sets.html

----------


## alansidman

Any suggestion on how to solve for the nine most current weeks.

http://www.excelforum.com/excel-prog...ml#post3567548

I cannot think of a viable means to calculate given the current parameters.  OP changes week numbers at beginning of year and throws off my calculation and I cannot think of an alternative.

----------


## ChemistB

Some VBA needed here.  Looks simple but having a mental block.

http://www.excelforum.com/excel-form...-for-this.html

----------


## daffodil11

This has exceeded my skill level.

For each match of A, provide B, C, D when E is not the MIN of the group of A and is different than the row with the MIN value.

https://www.excelforum.com/showthread.php?t=985964

*Edit:* solved with brute force

----------


## FDibbins

anybody use a laptop (I cant stand them lol), that may offer some insight here?
http://www.excelforum.com/excel-gene...ml#post3568932

----------


## :) Sixthsense :)

OP is not convinced in my suggestion and waiting for the second opinion here...  :Confused: 

http://www.excelforum.com/excel-form...is-closed.html

----------


## :) Sixthsense :)

Any suggestions on this one?

I thought to go for Multiple Consolidation Ranges option of PT but not the right choice  :Frown: 

http://www.excelforum.com/excel-gene...g-vlookup.html

----------


## :) Sixthsense :)

I tried to understand the OP's requirement but I completely lost here... :Confused: 

http://www.excelforum.com/excel-prog...eets1-2-a.html

----------


## :) Sixthsense :)

Any suggestions why the comment box is showing as normal textbox in a specific file alone?

http://www.excelforum.com/excel-gene...ing-issue.html

----------


## FDibbins

Anyone any suggestions here?
http://www.excelforum.com/excel-gene...ml#post3578328
OP cam upload a file, but when they download and open that same file, they get a message that the file is corrupted

----------


## MrShorty

http://www.excelforum.com/excel-gene...-variable.html

This one is probably more statistics than Excel, but does anyone have anything more concrete to offer regarding how statisticians would approach this kind of problem?

----------


## alansidman

http://www.excelforum.com/excel-gene...-text-box.html

OP is looking to add comments to a TEXTBOX similar to comments for a cell.  She wants to add a photo to the comment.  Is this an option.  My only thought is to have a msgbox pop up when the Textbox has focus.  Is there an easier way?  Can you help her?

----------


## Olly

I fear I may be getting tired and missing something, here...

http://www.excelforum.com/excel-prog...copy-past.html

Anyone?

----------


## :) Sixthsense :)

In the below link please refer Post # 7 alone, in which I explained the problem with Example file.

The Picture is not loading on Drop down selection when the file opened in *Excel 2003* version  :Confused: 

http://www.excelforum.com/excel-form...-attached.html

----------


## :) Sixthsense :)

I provided my level best answer to OP and OP not seems to be happy with the provided solution.

If anyone is interested then please help the OP  :Smilie: 

http://www.excelforum.com/excel-gene...-on-cells.html

----------


## :) Sixthsense :)

In the below link OP is claiming that the Sheet Reference of the source workbook is getting deleted in formula bar when he opens the source workbook.

I am unable to fix it and any suggestion will be very much helpful  :Smilie: 

http://www.excelforum.com/excel-form...k-is-open.html

----------


## martindwilson

i am lost in the logic of this one probably simple but i'm now going round in circles!
http://www.excelforum.com/excel-form...o-numbers.html

----------


## alansidman

This one now goes beyond my skill level.  http://www.excelforum.com/excel-prog...workbooks.html

----------


## XOR LX

Afraid I'm not at all sure what the OP means. 

http://www.excelforum.com/excel-prog...ease-help.html

Cheers

----------


## FDibbins

Anyone have any suggestions here?

http://www.excelforum.com/excel-gene...rdly-slow.html

----------


## xladept

Can someone help this OP - I have neither the time nor the patience today :Mad: 

http://www.excelforum.com/excel-prog...ml#post3586786


Update** I think it's now solved - all my plans fell through for that day :Frown:

----------


## protonLeah

Wrt:
http://www.excelforum.com/excel-prog...uplicates.html

The code below creates a DEFINED NAME: "genii" and several smaller named lists dependent on it.  I cannot use those names in Data Validation references.  Why?



```
Please Login or Register  to view this content.
```


I can, however use VBA to create the Data Validation Lists directly:



```
Please Login or Register  to view this content.
```





```
Please Login or Register  to view this content.
```

----------


## TMS

Because the Data Validation List has to be text strings or a contiguous range. GenusList is an array.

----------


## judgeh59

http://www.excelforum.com/excel-prog...t-by-date.html

long thread sorry...

I have tried a variety of things and the biggest problem is that it fine on my excel but when he runs it on his actually data he gets a type mismatch error (13).

Any help would be great...thanks....

----------


## AlKey

Could someone please help this OP?

http://www.excelforum.com/excel-form...ort-order.html

It looks like there is a need for vba solution.

Thank you.

----------


## daddylonglegs

Any suggestion here

OP is trying to "array enter" a formula in a range of cells and seems to be following the correct process but it doesn't work

----------


## martindwilson

my patience is going ...going ....gone here,someone has obviously been "jerking this blokes chain" but op seems to have no idea about excel, any one with the patience of a saint want to carry on?
http://www.excelforum.com/excel-new-...ndom-sort.html

----------


## ragulduy

@martin 

That guy has to be a troll.

----------


## TMS

@Martin: you cannot be serious (in a loud voice with a John McEnroe accent)  :Roll Eyes (Sarcastic):

----------


## xladept

@Martin - I told him/her to just do it :Smilie:

----------


## alansidman

Hopefully someone with experience in hyperlinks in VBA can step in and help the OP finish up this project.

http://www.excelforum.com/excel-prog...folders-2.html

----------


## bebo021999

Is there anyone has experience in SUBTOTAL function? The OP in this thread 

http://www.excelforum.com/excel-form...-and-team.html

needs to sum hours and count persons in giving team, task and shift. Fdibbin did the sum and I did the count. The issue is SOLVED, but 

I am quite not satisfied with the result.

I used SUBTOTAL(9,OFFSET...) to count rows with values >0 in row. Results in sheet Template are correct. But if the column of task# in 

sheet Data would be activated, i.e for task 1, the result of task 2,3,4...would return 0.

I tried SUBTOTAL(109,...) for hidden counting, but thing did not change.

I am so confused now with SUBTOTAL in complex cobination with OFFSET, MATCH,...   

Thanks in advance and willing to learn from you.

----------


## Moo the Dog

I offered this VBA solution to the OP's problem of adding successive randomly generated numbers:



```
Please Login or Register  to view this content.
```


The weird thing is that when I close the workbook without saving, then re-open it, it generates the SAME numbers each time, when they should be random.. shouldn't they?

Hopefully someone can see the error of my ways? (Even though it does do what the OP wants, I am concerned that a duplicating sequence of numbers would not be helpful).

- Moo

----------


## TMS

I don't often use Rnd but, reading the help:

Rnd[(number)]

The optional number argument is a Single or any valid numeric expression.

Return Values

If number is: Rnd generates 
Less than zero: The same number every time, using number as the seed. 
Greater than zero: The next random number in the sequence. 
Equal to zero: The most recently generated number. 
Not supplied: The next random number in the sequence. 

You're not supplying a number so I guess it remembers what it last used somehow ... so you get the next number in sequence.

Regards, TMS

----------


## Moo the Dog

Thanks for that explanation TMS. Is there a better way to generate a random number with VBA? Since this method is clearly not anything close to random... not even 'pseudo-random' if there is an actual sequence.. lol

- Moo

----------


## xladept

Hi Moo,

You could use Rnd(Timer)

----------


## TMS

To be honest, I can't offer that much help.  if you Google: excel vba generate unique random numbers, you may find something of interest though one of the responses I saw indicated that the cycle could be quite short and then repeat exactly the same numbers.

Regards, TMS

----------


## TMS

@xladept: ooohhhh ... good thought.

Just out of interest, you could change the code to the following to see the sequence of numbers generated




```
Please Login or Register  to view this content.
```



Regards, TMS

----------


## martindwilson

you have to use randomize i think





```
Please Login or Register  to view this content.
```

----------


## Moo the Dog

Thank you TM, XLA and Martin... you are all so very helpful. =)

- Moo

----------


## xladept

You're welcome! And, thanks for the rep! :Smilie:

----------


## TMS

@Moo: ditto.  Although ... 



> always *quite* helpful



  :Roll Eyes (Sarcastic):   :Smilie:

----------


## martindwilson

op is using importxml in google docs ,im pretty sure there is no native equivalent in excel 2013
but i dont know enough about it ,could it be done with code/udf
http://www.excelforum.com/excel-form...el-2013-a.html

----------


## jaslake

In this Thread, the Code adds new Rows by Button Click. It copies Data from above, including Conditional Formatting.

http://www.excelforum.com/excel-prog...ml#post3597277

I don't know why the Conditional Formatting is not being applied...the Formulas appear to be in place but aren't firing. Anybody help?

----------


## Solus Rankin

I need some suggestions here:  http://www.excelforum.com/excel-prog...-workbook.html

I don't know of a way to check for protection without opening the document.

----------


## Sam Capricci

I know some have visited this poster's issue.  I stoped by to see if I could help him to better define his needs and I'm still not sure though he has gotten closer.  He is private mailing me to ask for help.  I'm going to throw this link out here to see if anyone wants to look at his issue and see if they can help.  
http://www.excelforum.com/excel-gene...ows-plz-2.html 

Thanks.

----------


## bebo021999

Does anyone has 2003 version can help with condition formating:

http://www.excelforum.com/excel-form...ml#post3604015

I am using 2007 and could not remember CF in 2003.

Thanks

----------


## :) Sixthsense :)

In this thread OP is looking for Array solution but for me I don't see any need for Array Formula.  

Seems to be a assignment question for my eyes....

http://www.excelforum.com/excel-gene...t-working.html

----------


## :) Sixthsense :)

In the below thread the OP unable to rename/delete the worksheet in any of his Excel Workbook.

Any suggestion will be highly helpful  :Smilie: 

http://www.excelforum.com/excel-gene...cel-forum.html

----------


## Mad-Mizer

I don't have the time to code these requirements.  Maybe one of you can offer a better solution to the ones that I suggested.

----------


## ChemistB

Feel free to jump in on this one.

http://www.excelforum.com/excel-form...s-of-data.html

----------


## Olly

I think I'm suffering from "It's Friday and it's been a long week"!

Anyone feel like picking up the patient stick on this one: http://www.excelforum.com/excel-form...rd-column.html

I've already declined the OPs kind suggestion by PM that he can Skype me for me to fix for him...

----------


## TMS

@Olly: I don't think so.  It's still Friday afternoon this side of the hills.

Regards, TMS

----------


## daffodil11

VBA, Worksheet Change:

When values are added to Sheet1 Column A, also insert hyperlink on Sheet2 Column B that link back to Sheet1's cell.

I've been teetering on the edge of victory for 24 hours, but I just can't quite debug it.

I've tried both manual and formula methods, but hitting brick walls.




```
Please Login or Register  to view this content.
```





```
Please Login or Register  to view this content.
```


https://www.excelforum.com/showthread.php?t=994673

----------


## FDibbins

can someone see if they can make any sense out of this 1?  Im about as confused as a chamelion in a box of M&M's (or smarties)

http://www.excelforum.com/excel-prog...ml#post3613953

----------


## XOR LX

Hi all,

This should be obvious, but can't say I'm a 100% sure that Excel doesn't have to calculate the SUM on the range beyond the "last-used" cell?

http://www.excelforum.com/excel-form...-of-cells.html

So they're right that simply setting the SUM range to the entire column would be more efficient in this case?

Cheers

----------


## martindwilson

anyone seen this screen shot here and recognise what's being used?
http://www.excelforum.com/excel-gene...his-excel.html

----------


## martindwilson

http://www.excelforum.com/excel-gene...nto-excel.html
simple web queries i can do but this is beyond me anyone know how to do this?

----------


## alansidman

http://www.excelforum.com/excel-prog...-criteria.html

I don't have a clue how to even begin on this one.

----------


## bebo021999

> http://www.excelforum.com/excel-prog...-criteria.html
> 
> I don't have a clue how to even begin on this one.



Look like to establish list of IDs in C column which corresponds to "yes" in D column.

I try with formular to display the expected results in order to help other helpers to put it an end with macro:

In B23:

=IFERROR(INDEX($C$1:$C$14,SMALL(IF($D$2:$D$14="yes",ROW($D$2:$D$14),""),ROW(A22))),B15)

Confirmed with Ctrl-shift-enter

Copy up.

Can anyone give help with macro?

----------


## Solus Rankin

I'm not that good with formulas.  I think formulas might be able to take care of this OP better than looping through two worksheets umpteen times.

http://www.excelforum.com/excel-prog...ml#post3616063

----------


## :) Sixthsense :)

I really can't understand what is the exact problem is?

The below thread is about External Link to other files, anyone have time then please pitch in here... Since I am not interested to continue with this thread  :Frown: 

http://www.excelforum.com/excel-gene...et-rid-of.html

----------


## FDibbins

Can anyone offer any help here?

http://www.excelforum.com/excel-prog...rt-to-pdf.html

----------


## :) Sixthsense :)

For me, it's seems like a real formula challenge  :Confused:  Unable to do it in single cell formula, I am lost here....

OP looking for any solution to arrive the expected result (UDF / Formula), but I am not interested to go for UDF since I am 100% sure some of you can achieve it in single cell formula with the inbuilt excel functions.

Any suggestion will be highly helpful to the OP.

http://www.excelforum.com/excel-form...lculation.html

----------


## ragulduy

Anyone able to help on this one? I'm beyond lost.
http://www.excelforum.com/excel-form...0-targets.html

----------


## zbor

can someone look what this macro should do: http://www.excelforum.com/excel-prog...nd-msgbox.html

----------


## Moo the Dog

Anyone know how to convert Excel 2013 functions to 2010 or earlier?

http://www.excelforum.com/excel-form...el-2010-a.html

I suggested an add-in available on the web, but OP isn't interested in that...

- Moo

----------


## Fotis1991

any idea for this quys?

http://www.excelforum.com/excel-prog...worksheet.html

----------


## JBeaucaire

wanted to draw some other looks at this thread:

http://www.excelforum.com/excel-form...d-updated.html

Down to post #5 where he seems to simplify things a little, thanks.

----------


## Solus Rankin

Need some help here.  http://www.excelforum.com/excel-prog...duplicate.html

I think I've coded what he needs to sheet copy but OP has extra class modules they want to get rid of and the Remove option is greyed out.

----------


## judgeh59

http://www.excelforum.com/excel-prog...-13-error.html


This is a weird LotusNotes to Email problem....

----------


## FDibbins

Anyone any ideas on this 1?  OP says the "split-screen" buttons are not there...
http://www.excelforum.com/excel-gene...ml#post3632487

----------


## SDCh

can someone look at this:
http://www.excelforum.com/excel-form...le-values.html

I think the op need a macro

----------


## SDCh

Another OP that need macro

From my point of view:
1. The OP want hide the sheet with password so anyone can't freely see all the sheet.
2. The OP want if the workbook save on other computer, so the user only see the sheet that the OP allow to see, base on username on the system.

http://www.excelforum.com/excel-form...user-name.html

----------


## FDibbins

ANyone have any suggestions onthis 1?

OP has a sumproduct() with month() inside it, but when we try to change that to weeknum() it errors....
http://www.excelforum.com/excel-form...ml#post3634899

----------


## bebo021999

15 posts without any help!
In fact, I am really really ready to give help, but I dont have much time for such complicated technical issues at the moment. Is there anybody wanna to jumb in and give a start?
http://www.excelforum.com/excel-form...ml#post3635500

----------


## Solus Rankin

I'm not really strong with IE

http://www.excelforum.com/excel-prog...-continue.html

----------


## Olly

I have no idea why this is returning an error for the OP. I've tested on three different machines now - download his workbook (original data.xlsx), paste in my code, and it runs perfectly for me every time, in 2010 and 2013.

Anyone able to test for me, see if I'm missing something obvious?

http://www.excelforum.com/excel-prog...aste-loop.html

----------


## TMS

Can someone have a look at this one.  Run out of steam and I'm going to be away for a few days.

http://www.excelforum.com/excel-prog...n-a-sheet.html


Thanks, TMS

----------


## ChemistB

I leave this to sharper minds than mine.

http://www.excelforum.com/excel-form...-criteria.html

----------


## Tony Valko

> I leave this to sharper minds than mine.
> 
> http://www.excelforum.com/excel-form...-criteria.html



I downloaded the file earlier this morning.

As soon as I opened it I closed it and deleted it off of my hd!  :Wink:

----------


## ChemistB

> As soon as I opened it I closed it and deleted it off of my hd!



LOL,  made me chuckle, Tony

----------


## bebo021999

> I leave this to sharper minds than mine.



Open-Close-reopen-close and:
I wish my mind would sharper than you.  :Smilie: 
Good luck, Chemist B  :Wink:

----------


## XOR LX

Hi all,

Anyone know if this is a known bug?

http://www.excelforum.com/excel-form...-an-array.html

See post #9 only - does everyone else's machine crash/run of out resources when attempting this one simple formula?

Cheers

----------


## XOR LX

Lots of new and interesting features/bugs unearthed today!

Anyone know why SUMIF converts the text to a numeric in this case? And is there a way to stop that conversion (and still use SUMIF)?

http://www.excelforum.com/excel-form...-if-error.html

Cheers

----------


## Solus Rankin

http://www.excelforum.com/excel-prog...ml#post3638639

OP is talking to himself.  Can anyone assist?

----------


## :) Sixthsense :)

Anyone please join in the below thread, since I can't able to understand the OP's requirement  :Confused: 

http://www.excelforum.com/excel-form...f-formula.html

----------


## :) Sixthsense :)

Any idea about how to close the Print Preview modal window in VBA?

http://www.excelforum.com/excel-prog...ng-button.html

----------


## Izandol

I think code will stop when this window is shown. It is easier to let user press Print or Cancel.  :Wink:

----------


## :) Sixthsense :)

Yes, the code is interrupting and waits to run the rest of the codes when the window close.

But even sendkeys doesn't seem to work at my end with application.ontime timevalue("00:00:05")

I thought to close the workbook by using esc as send key and get the user input via msgbox and based on the user selection (Yes/No) we can continue or exit the code.  But it's not working as expected  :Frown:

----------


## :) Sixthsense :)

It's a mad thought, because when the code doesn't run while the Print Preview is active, then how the Send Keys will run  :Wink:   :Smilie:   :Smilie:  Laughing myself  :Wink:

----------


## Izandol

Yes - *all* code will stop. Why not leave the user to print or cancel, then code may continue?

----------


## :) Sixthsense :)

> Why not leave the user to print or cancel, then code may continue?



Just wondering why Microsoft doesn't kept any solution for this one?  The reason for marking the Form Layout as Model explicitly without any way to close it with a powerful tool VBA  :Confused:

----------


## Izandol

I think you must ask Microsoft.  :Smilie:

----------


## :) Sixthsense :)

> I think you must ask Microsoft.



lol  :Wink:   :Smilie:   :Wink: 

I hope any of the MVP's who reads this post will help us to find the solution for this, since they have access to ask Microsoft directly  :Smilie:

----------


## ragulduy

I believe that printpreview will fire the beforeprint event macro - so maybe there is a solution involving code in there, so when the printpreview is fired, the beforeprint code runs, checks whether it should be printing or showing printpreview and then gives you the yes/no message box.

Edit - just tried and that doesn't work, the code runs before the print preview window is shown.

----------


## zbor

Does anybody understand what he's asking? http://www.excelforum.com/excel-prog...nd-column.html

I don't even know what data is in what sheet.

----------


## XOR LX

Anyone with a bit of code for cleaning up unwanted characters?

Never come across this ASCII before.

http://www.excelforum.com/excel-form...er-format.html

Cheers

----------


## MrShorty

http://www.excelforum.com/excel-form...ml#post3645444
This guy seems rather intent on speeding up his formulas. I offered my suggestions (all around the idea of trying to help Excel reduce the number of operations needed), but he doesn't feel helped for one reason or another. I'm not very good with Pivot tables or other database type functions. It is beginning to look to me like there is not going to be a simple tweak to the formulas that will dramatically speed up the calculation. Anyone with more database management type experience that could suggest some ideas for better managing the database? I'm wondering if this could be better accomplished using a completely different set of database tools in Excel, or even a dedicated database application rather than a spreadsheet. I don't do database type work so I really don't know.

----------


## bebo021999

http://www.excelforum.com/excel-form...ml#post3652083
User in my local excel forum needs to sum hours per shift during given dates.
Seems I am in stuck and need your advice.
Is there anyone ready to deal?

----------


## :) Sixthsense :)

Does anyone have Mac version of excel?

If so, then please join in the below thread and *suggest how to change Post 9 formula needs to be converted to Mac excel functions*...  :Confused:  

http://www.excelforum.com/excel-form...btraction.html

----------


## TMS

@Sixthsense: It's not a Mac version of Excel.  Documents To Go is an Android App which allegedly can create and edit Office compatible documents.

Regards, TMS

----------


## :) Sixthsense :)

Hi TMS,

Thanks for correcting me  :Smilie: , I misinterpreted it with Mac since I never seen Mac till now  :Wink: 

As I remember, 3 to 4 years back there was a question posted in MS-Old News group in that the OP asked for an alternative method for ROUND*UP* / ROUND*DOWN* since in his *Windows Mobile* which *have only ROUND()* alone.  I think this thread similar like that one  :Wink:

----------


## TMS

No problem.  I think, in the old days, for ROUNDUP and ROUNDDOWN, you would have added or subtracted 0.5 (or .05, .005, etc) before using ROUND.

Regards, TMS

----------


## :) Sixthsense :)

> you would have added or subtracted 0.5



Yes, exactly  :Wink:

----------


## JBeaucaire

Anyone have some insight on how this OP might get his results on a combinational sums of 360 numbers FASTER in this thread?

----------


## Olly

Anyone able to pick up on this one:http://www.excelforum.com/excel-prog...-computer.html

Interesting migration of some OLD code controlling COM ports, and although I'm enjoying trying to unpick it, it's 2:30am and I need my bed!

----------


## ragulduy

http://www.excelforum.com/excel-prog...-workbook.html

Anyone able to help on this one with some VBA? It's much more complicated in 2003 and I'm unable to test any code I write using the simpler .displayformat property.

----------


## zbor

Does someone see what he's looking for? http://www.excelforum.com/excel-prog...using-vba.html

----------


## ragulduy

http://www.excelforum.com/excel-prog...-document.html

Anyone able to help here - I've not tried this sort of thing before but the followhyperlink event doesn't seem to be picking up the correct target. Is there a fix/another method?

----------


## FDibbins

Any mod/guru up for a challenge (or just want to drive yourself nuts?)  OP here has a circ ref in a mess of formulas over 3 sheets...
http://www.excelforum.com/commercial...ml#post3658194
(Good luck, I am often fairly good with circ's, but I will leave this 1 to some1 else)

When you "fix" the formula and copy it down a few rows, there is no CR, but after a few more rows it kickes in

----------


## ChemistB

Needs VBA solution.  

http://www.excelforum.com/excel-gene...ific-text.html

----------


## Tony Valko

Anyone want to take a stab at this one? It's a complicated ranking scheme.

http://www.excelforum.com/excel-form...-criteria.html

----------


## TMS

Anyone care to have a look at this ...

http://www.excelforum.com/excel-prog...ng-saveas.html

What it says on the tin.  One specific Excel file crashes when saved either using code or manually.  Can be replicated on a brand new machine so it points to the file rather than the environment.

Thank, TMS

----------


## :) Sixthsense :)

Anyone please join in the below thread, since I offered my better coding and I am completely unable to understand what is his original requirement  :Confused: 

Also OP claiming something which won't happen in that way since the code is sticked for certain columns and don't know what's going wrong on OP's side  :Confused: 

http://www.excelforum.com/excel-prog...e-entries.html

----------


## MarvinP

Does anybody have some canned code to create a text file for each row of data on a sheet?

http://www.excelforum.com/excel-prog...text-file.html 

seems to want this function and I really think it is a step backwards.  

I'm disowning the question, as I work for free and think any answer like the OP desires isn't going to help much.
(I give rep for helpers.)

----------


## TMS

@MarvinP: i have provided a basic macro to write a text file for each cell in column A.  They are simply named URL x.txt.

No idea how the OP would like them named but the index is used to make them unique.  If a file exists, it will be overwritten.

Regards, TMS

----------


## alansidman

This is a bit out of my wheel house.  An interesting question

http://www.excelforum.com/excel-prog...-by-dates.html

----------


## Sam Capricci

Can anyone give this person some help?
http://www.excelforum.com/excel-form...-in-other.html
It may be a simple solution but I can't get my head around it as I've tried different versions of substitute and left and mid while adding -- to it but with no success.  
I think it may require VBA.  I'll be interested in seeing what someone comes up with.   :Smilie:

----------


## JBeaucaire

Any suggestions for this OP?:
http://www.excelforum.com/excel-gene...onditions.html

----------


## JBeaucaire

An interesting use of the FollowHyperlink event:
http://www.excelforum.com/excel-prog...ks-in-vba.html

Any suggestions?

----------


## ragulduy

Anyone got an idea with a formula for this:
http://www.excelforum.com/excel-form...er-groups.html

----------


## alansidman

I've hit a snag here with some VBA and don't see the issue causing nothing to be copied.

Your thoughts  http://www.excelforum.com/excel-prog...le-sheets.html

----------


## :) Sixthsense :)

I don't know how better I can explain the OP than this  :Confused:  anyone please jump in and save me  :Wink: 

http://www.excelforum.com/excel-prog...lculation.html

----------


## alansidman

Anyone have any experience using the Excel Forecast Function.  It took me several postings to realize what the OP was after and then realized that I have no idea how to help as I have never worked with the Forecast Function.  If you have some experience here or an understanding, then please jump in and help.

http://www.excelforum.com/excel-gene...le-sheets.html

----------


## FDibbins

Anyone worked with Office 365 and know of any sorting restrictions beyond a certain number of rows>

http://www.excelforum.com/office-365...ml#post3683289

----------


## FDibbins

OK I been banging my head on this 1 for too long, need a fresh pair of eyes....
http://www.excelforum.com/excel-form...lain-this.html





> Column H always displays the first result in G until we get the first result in F and vice versa. Does this answer the question? The previous suggestions were super, super close with just a few glitches. Does this help?

----------


## JBeaucaire

Trying to copy only visible cells, my normal suggestions aren't working for this thread:

----------


## :) Sixthsense :)

Please anyone join in the below thread who knows about setting the ADODB.Connection in VBA, A small adjustment needs to be done in the OP's coding but I am unable to fix it, since it is out of my expertization  :Confused: 

http://www.excelforum.com/excel-prog...t-defined.html

----------


## :) Sixthsense :)

I am really confused about what he is looking for (VBA)  :Confused: 

http://www.excelforum.com/excel-prog...-inputbox.html

----------


## JBeaucaire

In my test workbook, these lines of code are working to filter uniques values from a single column directly into an array:



```
Please Login or Register  to view this content.
```


The OP in this thread is using the exact same code in the workbook provided in the thread and this is not working.  I know other ways to do this, I'm specifically wondering if anyone can spot why this method is not working on this workbook?

Any feedback would be appreciated.

----------


## Tony Valko

Anyone want to see if they can understand what this OP wants:

http://www.excelforum.com/excel-form...the-value.html

I don't understand what they're trying to do.  :Confused:

----------


## alansidman

http://www.excelforum.com/excel-new-...ate-cells.html

OP has an issue in trying to get into VBE.  I have offered my solutions, Alt + F11 and Through Developer Tab on the Ribbon.  It appears that the Developer Tab is greyed out and Alt + F11 does not do the trick for her.  Is this something her IT dept may have done or is there a switch within Excel that may have been turned off.    If you have seen this before and can assist, then please jump in.  Thanks.


Edit:  Just found this link and provided to OP.  Don't know if this is the issue however.

http://www.excelforum.com/excel-prog...-disabled.html

----------


## JBeaucaire

Inserting columns in newer versions of Excel takes noticeably longer than it did in Excel 2003.  The OP in this thread is looking for ideas on speeding up his short macro.  He's only inserting about 10 empty columns, but it's taking 10 minutes to accomplish.  I've given my thoughts, any others are welcome to add to the mix.

----------


## zbor

I would say deleting rows/columns too.

----------


## :) Sixthsense :)

I given a Non VBA solution but the OP looking for VBA solution only, Any one please jump in this thread  :Smilie: 

http://www.excelforum.com/excel-prog...ell-value.html

----------


## ChemistB

Going beyond my limited VBA skills

http://www.excelforum.com/excel-form...n-changes.html

----------


## Fotis1991

Anyone that can helps our friend here? I am not able to understand the logic.

http://www.excelforum.com/excel-form...embership.html

----------


## zbor

I got the logic but can't get formula  :Smilie:

----------


## alansidman

I'm not sure if its me or the OP.  If you think you know what this guy is up to, then please jump in. *Automatic Sorting is the key.*

http://www.excelforum.com/excel-prog...d-on-date.html

----------


## TMS

@Alan: sorry, not  barge pole long enough  :Roll Eyes (Sarcastic):

----------


## alansidman

@Trevor; In that case, can you throw me a life line, because I am sinking here.  :Smilie:

----------


## TMS

@Alan: I would love to but having looked at the example file, I have no idea what it needs doing to it.  Sorry  :Frown:

----------


## alansidman

http://www.excelforum.com/excel-gene...reasheets.html

I have provided a working solution for the OP for an onClose event, but he is looking for an instantaneous change.  I tried a similar solution for the Workbook_Sheet Change Event and the Worksheet Change Event (putting the VBA in all three sheets for the worksheet change event).

In all cases, the VBA just hangs.  If I stop the VBA from running, it has updated the cells A1 and B1.  If you have a solution for this, then please offer it up.  For the record, here is what I tried in the worksheet change event for each of the sheets.  Did I miss something to make it hang???




```
Please Login or Register  to view this content.
```

----------


## TMS

You're making changes in a change event handler so I suspect it is looping ... maybe compounded by making the changes to several sheets.  Use Application.EnableEvents = False before changes and = True after.

That would be my first suggestion anyway.


Regards, TMS

----------


## judgeh59

I'm adding my own thread because nobody in the regular area answered.....

CLick Here

----------


## JBeaucaire

I can tell now the reason you got no answers is because you bumped your own thread almost immediately.  People are looking for new posts with zero replies to jump into, you killed that almost immediately.    TIP of the day,  EDIT your original post until others start to reply, then add new responses.  Bumping really does work against you, accidentally.

----------


## judgeh59

I figured that and really the first "bump" was me adding the link....and I thought I was editing the OP but apparently not....thanks JB....so any ideas on the actual problem....

on a side note: I consider myself a pilot because I fly aerobatic RC's. I have PItt's Special....

----------


## JBeaucaire

No, I won't be joining in, but I did merge your posts back into one so your thread will appear on the Unanswered Posts listing.

----------


## judgeh59

thank you Sir

----------


## alansidman

Here is an interesting problem for the OP.  It is beyond my capabilities.

http://www.excelforum.com/excel-gene...from-27-a.html

----------


## alansidman

OP has changed requirements and I cannot wrap my head around a starting point on this one.

http://www.excelforum.com/excel-prog...-a-column.html

----------


## xladept

Hi,

Formulas are just one of my weaknesses - can one of you formula savvy contributors fix this thread?

----------


## FDibbins

Can someone pitch in on this 1 please?  OP wants to pull max value from various workbooks...
http://www.excelforum.com/excel-gene...ml#post3708686

----------


## TMS

Anyone have a go at this:

http://www.excelforum.com/excel-prog...und-color.html

The OP wants to be able to "highlight" a selected cell on any worksheet by setting Target.EntireRow and Target.EntireColumn interior color as a "cross hairs" effect.  The problem is that, when he selects another cell, it clears any previous color formatting.

I've done some testing, despite not having a sample workbook  :Roll Eyes (Sarcastic):   , and I can't think of a way to protect the pre-existing formatting.

Thanks, TMS

----------


## :) Sixthsense :)

Hi TMS,

CF is the method needs to be used  :Smilie: 

Adding CF and Deleting CF will override the cell formatting and it will not clears the original font color/interior color of cells.

----------


## TMS

@SS: thanks, good thinking.  

An example is shown here:

http://www.tushar-mehta.com/publish_...d%20cell.shtml


Regards, TMS

----------


## :) Sixthsense :)

> @SS: thanks, good thinking.



Glad to know that there was a solution already made with an example  :Wink: .

Thanks a lot for sharing the link and rep also.

I will preserve that link for future use for redirecting the OP, if anyone come with the same type of question  :Smilie:

----------


## TMS

@SS: you're welcome.  I just Googled it and there were other results but this one seemed to explain it well and came with an example workbook.  Lots of different methods demonstrated.  Thanks for the feedback  :Smilie:

----------


## :) Sixthsense :)

I don't know how further I can help this OP  :Confused: 

Will be helpful if anyone join in this thread and offer a suggestion (*VBA*)  :Confused: 

http://www.excelforum.com/excel-prog...ercentage.html

----------


## daffodil11

Sometimes I feel like I step in puddles that have oceans hidden under them.

OP needs to encapsulate existing code in a loop that cycles through all worksheets.

A simple For Each ws / Next loop didn't quite work. I think something in existing code is stopping it.

https://www.excelforum.com/showthread.php?t=1014069

----------


## alansidman

> Sometimes I feel like I step in puddles that have oceans hidden under them.



 What a great line.  I sometimes feel that way, also.

Anyway, here is the current predicament.  I don't see a simplistic solution for this OP.  If you have one, jump in as the ocean is deep enough to accommodate one more.

http://www.excelforum.com/excel-prog...eferences.html

----------


## TMS

@Alan: code provided in the thread.


Regards, TMS

----------


## FDibbins

Need another set of eyes to look at this 1 please?

http://www.excelforum.com/excel-form...f-formula.html

The formula works perfectly, except for a handful of names.  I am using OFFSET(*) to get the month column - when I use that, a few names throw an error, when I use the actual column ref, it works.  The OFFSET() does not use a name, so that cant be it - I have tested all the data in the column, it is all numbers.  Cant find the problem

----------


## :) Sixthsense :)

OP is saying that when he uses the below line in his code then his excel is crashing...




```
Please Login or Register  to view this content.
```


I suggested to turn off the events and try like this...




```
Please Login or Register  to view this content.
```


But that is also not working for the OP and still the OP is facing the crashing issue  :Confused: 

Any suggestion will be highly helpful to the OP  :Smilie: 

http://www.excelforum.com/excel-prog...ml#post3731542

----------


## FDibbins

Can anyone offer help here?

http://www.excelforum.com/excel-prog...ml#post3733843

----------


## ChemistB

http://www.excelforum.com/excel-form...down-list.html

Good clear title says everything.

----------


## MrShorty

I don't think this OP is going to accept my suggestion to use a helper range. Someone who is good at putting together single cell array formulas can help us make this a single cell LINEST() function? http://www.excelforum.com/excel-form...-possible.html

----------


## Tony Valko

Need someone that's good at setting up pivot tables here:

http://www.excelforum.com/excel-form...daterange.html

oeldere ?

----------


## TMS

Anyone pick this up?

http://www.excelforum.com/excel-prog...elimiters.html


I provided some code that worked when I tested it with the sample files but the OP couldn't get it to work.  She's downloaded an alternative solution that she also can't get to work.

I'm going on holiday in the morning so I can't spend time debugging it.

Thanks, TMS

----------


## FDibbins

Got a strange 1 here - anyone got any ideas?

http://www.excelforum.com/excel-gene...el-2010-a.html

----------


## ragulduy

http://www.excelforum.com/excel-prog...ell-edits.html

Anyone with 2013 fancy taking a look at that one.

----------


## Tony Valko

Data validation problem.

https://www.excelforum.com/showthread.php?t=1018083

It works as expected in Excel 2002 but does not work in Excel 2007+.

I'm baffled!  :EEK!:

----------


## JBeaucaire

Any MAC users that can offer some VBA comment on converting the code in this thread to work on a MAC?

----------


## JBeaucaire

This thread posits an interesting problem I've no solution for, and I'm interested If anyone else has an idea?   A VBA event method to watch how sheets are named and make sure new sheets are named as dates in a specific format.

----------


## TMS

If you Google: excel vba worksheet rename event ... you get a few suggestions, mostly from other forums though so I hesitate to provide links.

It's a bit of a botch up but it looks like it could be done.

Regards, TMS

----------


## xladept

You could count the characters in the tab and, if deficient, issue your message or repair it right there! :Smilie:

----------


## TMS

Counting characters alone wouldn't be enough; you'd need to check the content/structure, perhaps with a Regular Expression. But what would prompt you to check it?

Best bet would be to lock the workbook structure and have button to create new sheets.

Regards, TMS

----------


## xladept

```
Please Login or Register  to view this content.
```


???

----------


## TMS

> But what would prompt you to check it?



Day and Month could be in the wrong order, the Year could be two digits. Missing/extra hyphens ...

----------


## xladept

Hey Trevor, the OP's post just shows omissions in the two digit month field :Roll Eyes (Sarcastic):

----------


## TMS

I think not. Look again.  At least two no-no's but I suspect lots more possibilities.

----------


## xladept

Hi Trevor,

I looked again: You're right but

Length 9=> omitted first zero
Length 8=> omitted the 20 for the year
Length 7 => omitted both

You could still work with it!

Probably easiest would be to format it "mm-dd-yyyy".

----------


## xladept

Well I played with the notion but, with appropriate safeguards:




```
Please Login or Register  to view this content.
```


works rather nicely :Smilie:

----------


## martindwilson

op wants to fill in an allocation table and update a planner OR update the planner and it fills out the allocation table cant really see the second bit myself, any ideas peeps?

http://www.excelforum.com/excel-gene...orksheets.html

----------


## Pete_UK

Hi Martin,

can't see which thread you are referring to.

Pete

----------


## martindwilson

that would help wouldn't it  :EEK!: 
http://www.excelforum.com/excel-gene...orksheets.html

----------


## Saarang84

Seems an array formula is required in this thread...  Any suggestions, Sktneer?

Take part information from a cell to create a formula

----------


## Saarang84

Looks like another array formula is required for the OP of this thread : Formula Not Recognising Cells as Blank with Formulas that Return Empty Text

----------


## Saarang84

Any ideas for the OP's requirement in this thread?  Calculations based off formula from another cell

----------


## TMS

Anyone have time to look at this?  I just don't seem to be able to get my head around the requirement.

Counting the number of occurrences before the current week #


Thanks TMS

----------


## Pete_UK

Trevor,

looks like he had his " in the wrong place.

Pete

----------


## TMS

@Pete: thanks for looking ... wood for trees and I don't think the workbook helped me much  :Frown:

----------


## Saarang84

Can anybody extend help for the OP in this thread ?? : Depleting From Multiple Values At Once

----------


## Speshul

I'm not sure if my nesting 24 =SUBSTITUTE()'s is really the best way to go about this problem, any help for this op?

http://www.excelforum.com/excel-gene...haracters.html

----------


## MrShorty

This OP is trying to understand why his (IMO erroneous) number format gives a different display in the spreadsheet than in the chart. I cannot really confirm his observations in my own version and I don't have 2013. Any takers?

http://www.excelforum.com/excel-char...ts-y-axis.html

----------


## Russell Dawson

Can someone help with this mailmerge problem.  OP is unable to upload file.   What he wants to do is produce letters to each of 1500 employers to 9000 employees based on who the employer is.  A Word problem perhaps but I think he's right by raising the question here because of the need to manipulate the data document in Excel.

http://www.excelforum.com/excel-gene...ail-merge.html

----------


## :) Sixthsense :)

OP is trying to use hyperlink() function with root paths which have more than 255 characters.

But unfortunately the Link Location argument of Hyperlink() function accepts maximum of 255 character length only.

Any suggestion will be helpful to the OP  :Smilie: 

http://www.excelforum.com/excel-form...mla-value.html

----------


## MrShorty

OP asked for something "brutally simple", so I suggested a filterable list using Autofilter. I don't do "database" stuff, so I haven't got much more to suggest to him. http://www.excelforum.com/excel-gene...iles-here.html

Can anyone suggest another approach?

----------


## JBeaucaire

Anyone with access to EXCEL 2003, can you download the workbook solution I've provided in this thread and see if you're seeing the same issue the OP is seeing.   We are applying an Autofilter to a range based on a search string typed in H2.


Here's the file in case you don't have access to that thread.

----------


## daffodil11

I have stepped in something terrible. Pretty sure its programming related.




```
Please Login or Register  to view this content.
```


https://www.excelforum.com/showthread.php?t=1028374

----------


## xladept

Hi Jerry,

What I found to be weird about the demo file is that each criterion is listed in every first few cells in the H range - otherwise it seems to work :Confused:

----------


## Izandol

@JB,

I believe this will resolve issue:



```
Please Login or Register  to view this content.
```

----------


## Olly

Anyone have experience of refiltering OLAP pivot tables with VBA / parameters?

http://www.excelforum.com/excel-prog...-function.html

Reading http://support.microsoft.com/kb/234700 would indicate that normal methods of refiltering pivots won't work - as I've found, eventually, after going through all the usual steps of trying to get code to work...

Anyone?

----------


## alansidman

This is worksheet change event and it is a bit out of my wheelhouse.  Hopefully, you can get the OP on the right track. 

http://www.excelforum.com/excel-prog...er-column.html

----------


## :) Sixthsense :)

This OP is created an excel file by referencing the formulas to the desktop file.  The issue is that the desktop root path will get differed to each persons which is creating an issue by showing the Update Link popup message.

OP looking for automatic adjustment of desktop link in formulas.

OP is eagerly waiting for the solution for this issue.

http://www.excelforum.com/excel-gene...reference.html

----------


## judgeh59

I tried a few things but couldn't get it to work....anybody?

thanks

http://www.excelforum.com/excel-prog...in-column.html

----------


## ChemistB

Requires Array formulas and no helper columns

http://www.excelforum.com/excel-form...condition.html

----------


## XOR LX

Hi all,

Any ideas for forcing this formula to re-calculate in Automatic Calculation mode?

Usually just an additional clause, e.g. with NOW(), forces the issue, though I can't get anything to work here.

Cheers

http://www.excelforum.com/excel-form...ml#post3796764

----------


## FDibbins

Anyone want to help out with this 1?
http://www.excelforum.com/excel-form...to-column.html

----------


## martindwilson

Any one have any idea why the code in this
Thread will not work in 2013 works fine
In 2007
http://www.excelforum.com/excel-prog...onversion.html

----------


## ragulduy

@Martin

I think the problem is you have a reference to the solver in your workbook that will probably be missing in 2013. So the str() and trim() functions will be undefined. You need to either remove the reference or add a parent to the str/trim (e.g. conversion.str / strings.trim)

----------


## TMS

@Martin: I couldn't find the reference so I just unticked the MISSING Solver.xlam reference.  Compiled OK after that with no other changes.


And, as it implies, 79228162514264337593543950245 is the highest number but you need to convert it with: 

```
Please Login or Register  to view this content.
```

 which returns: 111111111111111111111111111111111111111111111111111111111111111111111111111111111111111110100101

Regards, TMS

----------


## jaslake

In this Thread OP gets a Type Mismatch error in response to a Message Box. I've tested in Excel 2007 and 2010 and the Code performs as expected. OP uses Excel 2013 and is receiving Error 13.

Any of you with Excel 2013 have time to look?  Thanks.

http://www.excelforum.com/excel-gene...ml#post3800110

Note to Mod/Admin...I posted to the wrong Sub Forum. Would you please move to Call In The Cavalry.

----------


## ragulduy

Anyone able to help here:
http://www.excelforum.com/excel-form...-counting.html

----------


## Tony Valko

This should be a simple "count if" but I'm just not understanding the OP's requirements.

Need a fresh set of eyes on this one:

https://www.excelforum.com/showthread.php?t=1031643

----------


## romperstomper

I think the OP said you solved it with one small change to your last formula?

----------


## :) Sixthsense :)

This is going to be a simple AND() Function usage but I am unable understand the OP's requirement  :Confused: 

Anyone please help with this below thread  :Smilie: 

http://www.excelforum.com/excel-form...of-values.html

----------


## JBeaucaire

Any Google Docs oficianados care to take a glance at this one, OP is trying to use INDIRECT() which is supposedly a usable GD function.

http://www.excelforum.com/for-other-...-formulas.html

----------


## ChemistB

Could use help on this one

http://www.excelforum.com/excel-form...lp-needed.html

----------


## ChemistB

VBA issues

http://www.excelforum.com/excel-form...ell-value.html

----------


## Speshul

Not sure on this one, I think it's possible but my attempt is giving me confusing results.

http://www.excelforum.com/excel-form...ml#post3814241

He wants to rank by the Average of each row, but within one formula per row (no helper columns).

----------


## ChemistB

I think this one will require laying out the data as the OP suggests or VBA but I'd love to see a formula solution

http://www.excelforum.com/excel-gene...ertically.html

----------


## XOR LX

Hi all.

Didn't notice that the OP had posted to the VBA section on this one, so offered a formula-based solution, which turns out to be inappropriate for the size of the dataset that the OP has.

Said I'd bump it for them to anyone with expertise in VBA and willing to have a go.

http://www.excelforum.com/excel-prog...al-amount.html

Thanks a lot.

----------


## Speshul

As far as I can understand with this question,  I solved it in post #3.. It is now on post #25.   Can someone else take a look at this, before I lose my mind?  :Frown: 

http://www.excelforum.com/excel-gene...me-cell-2.html

----------


## ChemistB

Busy with other projects, won't be on here much for a bit.   Can someone take this one?

http://www.excelforum.com/excel-gene...-a-series.html

----------


## judgeh59

This was more than I expected from the OP....anybody want to give it a go?

http://www.excelforum.com/excel-prog...ml#post3824784

----------


## XOR LX

Lord have mercy. Someone. Please. Help.

http://www.excelforum.com/excel-gene...ml#post3826744

----------


## Olly

> Lord have mercy. Someone. Please. Help.
> 
> http://www.excelforum.com/excel-gene...ml#post3826744



I've tried...  :Wink:

----------


## JBeaucaire

Anyone with ADO connectivity experience that can comment on this thread?

Op is trying to read text from closed workbooks, and apparently the text is huge per cell, they need to be able to pull out more text than 255 characters, and the workbook takes too long to actually open/close.

Any help at all is appreciated, I have no exp with ADODB connections and pulling large text from closed workbooks.

----------


## :) Sixthsense :)

This one is going to be a simple formula logic but understanding the logic is something very hard to me.

Anyone please jump in this thread to solve this OP's issue  :Smilie: 

http://www.excelforum.com/excel-form...-are-true.html

----------


## zbor

Does anybody understand this 'cause I don't  :Frown: 

http://www.excelforum.com/excel-prog...the-macro.html

----------


## ChemistB

This one will require VBA

http://www.excelforum.com/excel-form...-column-f.html

----------


## Olly

No idea what could be causing this - anyone encountered anything similar before?

http://www.excelforum.com/excel-prog...-cut-keys.html

Macro apparently runs fine when called directly, but "just stops" part way through when called via keyboard shortcut...

----------


## JBeaucaire

I'm inexperienced at the connections changes this thread is dealing with, can anyone offer suggestions I cannot?

----------


## MrShorty

Since I just use Rob Bovey's chart labeler add-in, I'm not very familiar with the code behind it, and this OP claims that he cannot use third party add-ins for the task. If he needs further help developing the procedure for this, can someone step in and help him?

http://www.excelforum.com/excel-prog...ta-labels.html

----------


## ben_hensel

I think this OP has basically filled out the "make lookups harder" bingo card.

1) Two-column lookup to return third column
2) Indeterminate column depth, maybe
3) Return multiple matches?
4) Check all the worksheets for matches

I'm a little too intimidated to even ask questions to specify the things I'm not sure about, and get kinda, committed to answering.

----------


## TMS

@ben_hensel: not sure there's a barge pole long enough with a description like that.  Good of you to highlight it though  :Smilie:

----------


## ChemistB

Yeah, I read that and moved on too.  My brain can't handle it today.

----------


## alansidman

http://www.excelforum.com/excel-prog...logy-tool.html

I've exhausted my ideas for this OP's request.  You got any?

----------


## MrShorty

http://www.excelforum.com/excel-char...eto-chart.html

this OP says that she really wants a pivot table solution. I expect it can be done, but I have no experience with pivot tables. I think the solution I proposed will work, but, if anyone who knows pivot tables could chime in and show her how to do it with a pivot table/chart, it could be a better solution.

----------


## TMS

On behalf of xladept:

Can anyone help with this concept?

----------


## ChemistB

http://www.excelforum.com/excel-form...t-of-data.html

----------


## JBeaucaire

Dependent Drop Down lists - INDIRECT()

Dependent Dynamic Drop Down Lists - OFFSET()

The OP in this thread is searching for a way to do it without either of these functions, which I have never found.

----------


## ChemistB

If anyone wants to jump in on this one, I wouldn't complain.   :Wink: 

http://www.excelforum.com/excel-form...combining.html

----------


## xladept

@ChemistB - no thank you/dynamic requirements :Roll Eyes (Sarcastic):

----------


## JBeaucaire

A simple form question has stumped us, trying not to need the OP to post the form... guess I should just do that.
http://www.excelforum.com/excel-prog...-text-box.html

----------


## JBeaucaire

A pretty advanced topic, IMO, thought I'd draw some more eyes to it.

http://www.excelforum.com/excel-prog...t-methods.html

----------


## :) Sixthsense :)

> A pretty advanced topic, IMO, thought I'd draw some more eyes to it.



I came across that thread and if we resolve the current issue (Disabling the Insert/Delete From Right Click) then the next question will be disabling the Ctrl++ and Ctrl+- from the Keyboard which requires Application.Onkey method.

Even if we do that then the next question will be raised from the OP that this should happen on that sheet alone.  So we have to enable the Application.Onkey on deactivation which needs lot of follow-ups, so I thought to stay away from that thread  :Smilie:

----------


## TMS

Can anyone have a look at this please: Delete fifth Name

I provided a relatively simple formula solution to extract the first four elements (space separated) from a cell.  



```
Please Login or Register  to view this content.
```



And the OP seemed OK with that but then came back with a much more complex formula that he wants/needs to adapt.



```
Please Login or Register  to view this content.
```



Lost on me, I'm afraid.

Thanks, TMS

----------


## Olly

Odd problem here - unable to refresh data queries on a protected sheet, despite unprotecting first, or protecting with userinterfaceonly=true...

http://www.excelforum.com/excel-prog...ry-tables.html

----------


## JBeaucaire

Calling once again, this OP has pinged looking for assistance on his one request, promising not to feature creep anyone.  I've no idea or would jump in...

http://www.excelforum.com/excel-prog...t-methods.html

----------


## :) Sixthsense :)

I used this code for dependent drop down list (Validation) based on the selection the next validation cell will be cleared and I used the sendkey method to auto populate the drop down.




```
Please Login or Register  to view this content.
```


But this is not working in MAC excel and throwing some errors to the OP.

After doing some google search I got the below link and I found the send key method for MAC excel and used it in the below code.

http://www.mrexcel.com/forum/excel-q...dkeys-mac.html




```
Please Login or Register  to view this content.
```


But unfortunately this also not working for the OP  :Frown: 

Anyone have any suggestions? If so, then please join in the below thread  :Smilie: 

http://www.excelforum.com/excel-prog...r-changes.html

----------


## JBeaucaire

Any suggestions for updating hardcoded hyperlinks to broken destinations?
http://www.excelforum.com/excel-gene...auto-save.html

----------


## JBeaucaire

Something for the FORM wizards...
http://www.excelforum.com/excel-prog...a-project.html

----------


## :) Sixthsense :)

Anyone have any suggestion about what's going wrong in *JapanDave'*s code?  :Confused: 

http://www.excelforum.com/excel-prog...n-columns.html

----------


## Olly

http://www.excelforum.com/excel-prog...-with-vba.html

I _THINK_ the OP wants to resize a workbook window, within the (maximized) Excel application window. I only have 2013 on this PC, where every workbook opens in it's own Excel instance.

Anyone able to help?

----------


## JBeaucaire

Anyone care to pipe in here?

----------


## TMS

@JB: I think not.  I have a mental picture of the thread with little round "bruises" all over it.  That would be from the barge poles touching it.  :Wink:

----------


## FDibbins

Not from my barge pole, I wouldnt even use yours, Trevor, let alone mine

----------


## xladept

What we have here is an attitudenal chism :Smilie:

----------


## TMS

Is that similar to a schit attitude?

----------


## xladept

Yes - if the "c" is silent like P in PSmith :Smilie:

----------


## Olly

> Yes - if the "c" is silent like P in PSmith



A silent p, like in swimming pool?  :Wink:

----------


## xladept

No, Olly, I mean the one without a big grin :Smilie:

----------


## Jacc

Here is a well defined problem it seems and rather interesting. Surely there are lots of people here that can handle this?  I can't/don't have time/bitten of more than I can chew etc.

http://www.excelforum.com/excel-form...-criteria.html

----------


## Crooza

> Here is a well defined problem it seems and rather interesting. Surely there are lots of people here that can handle this?  I can't/don't have time/bitten of more than I can chew etc.
> 
> http://www.excelforum.com/excel-form...-criteria.html




Jacc I had a look at this and posted a solution of sorts but some of the experts may be able to refine my solution. Probably easier to do in VBA but I've crafted something that will work with formulas only.

----------


## JBeaucaire

This OP has some EVENT code I've given for workbook_open and workbook_aftersave... they are looking to make that work from the Personal.xlsb file so they trigger on every workbook open and every workbook save event.  I believe this will require some CLASS module assistance I've no real experience with yet.

http://www.excelforum.com/excel-prog...e-changes.html

----------


## JBeaucaire

This is a FABULOUS question, I have been poking at it unsuccessfully for an hour now.

http://www.excelforum.com/excel-form...e-formula.html


Doing this in VBA would be trivially simple.  But the formula approach has be baffled.  Maybe not doable at all.

*=Randbetween(1,6)+Randbetween(1,6)*
...is an easy way to simulate the total of 2 dice being rolled.

This OP want to put a number in a cell that represents the number of "rolls" and have a single cell formula that does that many rolls on the fly.  This is not same single roll multiplied 3 or 20 times, it should be 3 or 20 individual rolls of the two dice, and the random results added up.   The key cell can be changed to any number of "rolls" and the random roll summarizer does that many rolls in the one cell.

I'm baffled.   Remember, I can knock out the VBA version, I'm looking for some brain power to help me ascertain, is it NOT doable by formulas only?

----------


## Crooza

jBeaucaire I've put one solution up but you might want to test it.

----------


## JBeaucaire

Yeah, a brute force formula,  :Wink: .  No way to do 100 rolls there...but thanks for input.

----------


## xladept

@ Jerry - formulas are not my thing, but aren't there array formulas that could be constructed to do n rolls??

----------


## daddylonglegs

> @ Jerry - formulas are not my thing, but aren't there array formulas that could be constructed to do n rolls??



You'd think so wouldn't you?

....but it isn't quite that simple - e.g. if you use this formula

=SUM(IF(ROW(A1:A100)<=B3,RANDBETWEEN(1,6)))

You'd think that might generate and sum the correct amount of random numbers, but if B3 is 20 that just generates the same random number 20 times. I don't think you can use a single formula without helper cells (barring the "brute force" approach), unless you turn on iteration as suggested by MrShorty.

----------


## Crooza

> Yeah, a brute force formula, .  No way to do 100 rolls there...but thanks for input.



No 100 wouldn't work. OP asked for 20 and this does 21 actually but to do 100 then you could do this formula in 5 cells and add them which would give the same result.

 But ..... If I was doing 100 I think I'd tackle this differently (other than the obvious vba solution) and try to fit a curve to the distribution curve for the roll options between 100 and 600 noting that they have different probabilities. I worked a curve out for three dice rolls but that only has 216 combinations between 3 and 18. 100 rolls has 6^ 100 which is beyond my comprehension. I'd be UDFing this one.

----------


## JBeaucaire

> I'd be UDFing this one.



Me too.  The UDF would be remarkably simple.  But I was thoroughly intrigued if a single formula could be created to do it.

----------


## xladept

@ DDL - 



> =SUM(IF(ROW(A1:A100)<=B3,RANDBETWEEN(1,6)))



 - you're saying that this only invokes the Rand() once??

----------


## JBeaucaire

Romperstomper has slain this dragon.  I am so thrilled, something new accomplished today.  *sigh*

----------


## romperstomper

It's 1am and I've had a few, so in a good mindset.  :Wink:

----------


## daddylonglegs

> @ DDL -  - you're saying that this only invokes the Rand() once??



Yes, you'll get an array of values but the "random" values will all be the same with the formula I suggested

......but I see a better way! Nice work Rory!

----------


## daffodil11

Ok, I've been toying around with this for a few days and I'm thoroughly stuck.

https://www.excelforum.com/showthread.php?t=1043548

OP has a number of rows by Account, and wants to sequentially deduct a given balance from each until a line zeroes out. This is either actually a hard question, or something deceptively simple.

----------


## JBeaucaire

Any thoughts on this one?

----------


## :) Sixthsense :)

Any thoughts about why the Status bar is not showing the Count,Sum,Average,Numerical Count in the excel status bar eventhough *everything is enabled* in Customize Status Bar Popup Menu


http://www.excelforum.com/excel-gene...ml#post3879494

----------


## Norie

Anyone any ideas about this one?

http://www.excelforum.com/excel-prog...nge-error.html

----------


## JBeaucaire

This one has me completely stumped.... how to add .CSV permanently to the list of "All Excel Files" extensions that are offered when you activate the OPEN FILE window from inside EXCEL.   Currently it lists all the .xls? options only.

http://screencast.com/t/XHhZ7dnMk

----------


## martindwilson

any one fancy a bit of vba tutoring?
http://www.excelforum.com/excel-prog...p-problem.html

----------


## :) Sixthsense :)

I tried to help this OP but the OP is not understanding the way I am trying to populate the data.  I felt frustrated and not interested to go further, but I hope someone will take care of this post *VBA question*. 

http://www.excelforum.com/excel-prog...ml#post3882114

----------


## :) Sixthsense :)

This is going to be a *Sumif()* function solution but I lost my patience here and I am not interested to give solution.  Anyone please take care....

http://www.excelforum.com/excel-prog...alues-vba.html

----------


## TMS

Anybody care to take a look at this: Fire up Worksheet_Change

Regards, TMS

----------


## FDibbins

I got most of what the OP wanted, but this thread has carried on for so long now that I am lost lol.  They keep using different files (I think), Its aboyt different teams and players, and now they want a list of all players from teams selected.  Im lost lol

http://www.excelforum.com/excel-form...riteria-2.html

----------


## FlameRetired

I am officially in way over my head on this one. The OP marked thread [SOLVED]..........pre-maturely I'm sure.

Post #14 summarizes my concerns. If nothing else _I_ am in serious need of leadership and guidance on this one.

http://www.excelforum.com/excel-gene...ttern-end.html

Thanks,
Dave

Edit: [SOLVED]. 01:34 AM 11/4/2014

----------


## Sam Capricci

anyone can help this person?  I at least helped the OP to define his problem more.  
http://www.excelforum.com/excel-gene...t-hlookup.html
thx.  Sam

----------


## FDibbins

Anybody want to jump in onthis 1?

http://www.excelforum.com/excel-prog...ml#post3893548

----------


## FDibbins

I have another 1 here that could use some help...
http://www.excelforum.com/excel-form...ml#post3894037

----------


## jaslake

Please, can someone look at this Thread and test the Code in the attachment to post 24?

Make an entry into C12, entire Row 13 should unhide as it does on my platform...on the OP's it does not.

Thanks, I appreciate.

http://www.excelforum.com/excel-prog...hide-rows.html

----------


## TMS

@jaslake: something very strange is going on with this workbook/worksheet/code.  I have put a Stop in at the top of the Worksheet_Change event handler.  If I make a change in column A, it Stops, as expected.  If I make a change in column C, it does not Stop.  It does, however, convert a lower case x to an upper case X ... but it doesn't hide the rows.

I'm guessing it has something to do with the UDF HasCmt which is applied to all the range of interest.  CF formula: 

```
Please Login or Register  to view this content.
```

 applied to 

```
Please Login or Register  to view this content.
```






```
Please Login or Register  to view this content.
```



I note that when the UDF is commented out, my Stop is complied with.  With the Stop working, you can step through the code.  What is happening is that the first bit of code converts the content of the cell to Upper Case but the second Undo causes an error and goes directly to the Reset: label without executing the Unhide Row code.  So, if you switch off the error trap you get Run-time error '1004': Method 'Undo' of object '_Application' failed.

That's the problem.  What to do about it ... over to you.  Maybe you can't have an undo stack in VBA because VBA routines clear the undo stack.

Regards, TMS

----------


## jaslake

@Trevor

I appreciate you looking at this.  I'm not certain what's going on but I'll remove the error handling and see where it leads.

I'll get back to you and the OP...probably not tonight...I truly do appreciate the feedback.

Thank you.

----------


## JBeaucaire

The OP on this thread has asked for second opinions:
http://www.excelforum.com/excel-prog...ingle-row.html

----------


## alansidman

This is an Access Question:  The OP has a table that he allows users to make changes to.  I have advised him that this is a bad practice.  I have also advised that no data should be removed from databases, but rather add a field to make a record inactive is the preferred db practice.  The issue that the OP has, can code be written that is driven by changes to the table, similar to Worksheet_Change event in Excel.  I am not aware of any VBA triggers from changes to Tables.

If you can help, please look at this link.

http://www.excelforum.com/access-tab...ess-table.html

----------


## ChemistB

I've never seen this before.

http://www.excelforum.com/excel-form...worksheet.html

----------


## daffodil11

OP is trying to clean up a long formula, which is sort of a SUMPRODCT/COUNTIF but with multiple strings as criteria.

Is it possible to use named ranges (of multiple cells) for criteria in a COUNTIF?

https://www.excelforum.com/showthread.php?t=1051788

----------


## JBeaucaire

This OP appears to be asking a simple Conditional Formatting question that I truly am not grasping... too much turkey in my system perhaps.
http://www.excelforum.com/excel-gene...ergen-map.html

Can anyone see the simple thing I am missing?

----------


## :) Sixthsense :)

OP wants to bypass the Alert Message in IE 11

I don't know anything about HTML object and all, so anyone please take care of this thread  :Smilie: 

http://www.excelforum.com/excel-prog...ml#post3916115

----------


## daffodil11

OP wants to paste clipboard to ActiveCell, inserting rows to make room for however many cells are in clipboard.

Can't figure out a way to stop Excel from copying clipboard into every new cell in the inserted rows.

https://www.excelforum.com/showthread.php?t=1052424

----------


## XOR LX

Anyone want to have a go at this?

http://www.excelforum.com/excel-gene...formula-2.html

I don't have the time at the moment to look into this one, and it's certainly not a case for a single-cell solution, I would imagine.

Thanks a lot

----------


## ChemistB

I can't wrap my head around what he's asking for or doing here.  

http://www.excelforum.com/excel-form...med-range.html

----------


## Debraj Roy

I am totally stumpped.. Can anyone help me.. to solve..

http://www.excelforum.com/excel-form...hree-rows.html

----------


## davesexcel

Can anyone help with this?

http://www.excelforum.com/excel-prog...ml#post3920883

----------


## alansidman

I'm out of ideas on this issue.  If you are up to it--->http://www.excelforum.com/excel-prog...not-empty.html

----------


## FDibbins

Can anyone help here please?
http://www.excelforum.com/excel-gene...ml#post3928510

I have no idea what they want  :Frown:

----------


## Olly

> Can anyone help here please?
> http://www.excelforum.com/excel-gene...ml#post3928510
> 
> I have no idea what they want



Made an attempt  :Smilie: 

I am rather drunk. It may not make much sense  :Wink:

----------


## Olly

> Made an attempt 
> 
> I am rather drunk. It may not make much sense



Not sure if it's _because_ I am drunk or _despite_ it, but I reckon that's a pretty cool solution  :Cool:

----------


## :) Sixthsense :)

Not sure what I am missing...  :Confused:  the date selection in Autofilter is not picking the actual date (VBA)

http://www.excelforum.com/excel-gene...ate-range.html

----------


## davesexcel

Can anybody find a formula approach to this?
http://www.excelforum.com/excel-form...ncatenate.html

----------


## davesexcel

Here's oine
http://www.excelforum.com/excel-gene...g-message.html

----------


## Debraj Roy

Both are same.. can someone help to grab a formula approach..

http://www.excelforum.com/excel-form...-criteria.html
http://www.excelforum.com/excel-form...-criteria.html

----------


## davesexcel

http://www.excelforum.com/excel-prog...-text-box.html
Ran out of time

----------


## TMS

Can someone have a look at this, please.  Lost on me.

http://www.excelforum.com/excel-prog...rten-data.html

Regards, TMS

----------


## davesexcel

Could use another opinion.
http://www.excelforum.com/excel-prog...r-filters.html

----------


## zbor

I guess I'm too tired to understand this today.. So can someone jump in: http://www.excelforum.com/excel-prog...-two-rows.html

----------


## martindwilson

ive gone as far as i can with this chart problem any takers
http://www.excelforum.com/excel-char...-the-week.html

----------


## MrShorty

This thread has suddenly entered an arena I know nothing about**: http://www.excelforum.com/excel-char...curve-fit.html The OP indicates that he wants to be able to add an option to the chart trendline dialog to accomplish his task rather than do the work directly in the spreadsheet. Anyone who wants to dive into this one? Is it even possible to modify Excel's built-in dialogs?

----------


## davesexcel

Can't seem to get Vlookup up to work in the Userforms 
http://www.excelforum.com/excel-prog...ml#post3944556

----------


## Debraj Roy

Isn't its time to create a new thread..  :Smilie: 

"Call in the Cavalry - 2015 onwards "

----------


## davesexcel

> Isn't its time to create a new thread.. 
> 
> "Call in the Cavalry - 2015 onwards "



Hre it is,
http://www.excelforum.com/the-water-...ml#post3944935

----------


## Richard Buttrey

...pedantic note. 2015 is '2014 onwards' 

I'll get me coat... :Wink:

----------


## davesexcel

Oh ya, Ha! :Roll Eyes (Sarcastic):

----------


## JBeaucaire

All who are interested should perhaps subscribe to the 2015 Cavalry thread started here:
http://www.excelforum.com/the-water-...5-onwards.html

----------

