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

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.

[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

[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

[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?

Sub VBA_DefineNames()

    Dim GenusList   As Variant, _
        Genus       As Variant, _
        SpeciesList As Variant, _
        TestCell    As Range, _
        GenusRow    As Range, _
        ListCount   As Long, _
        RowPtr      As Long
        
    GenusList = Array(Cells(1, 1).Value)
    For RowPtr = 2 To Cells(Rows.Count, "A").End(xlUp).Row
        If Cells(RowPtr, "A").Value <> Cells(RowPtr - 1, "A").Value Then
            ListCount = ListCount + 1
            ReDim Preserve GenusList(0 To ListCount)
            GenusList(ListCount) = Cells(RowPtr, "A").Value
        End If
    Next RowPtr
    
    With ThisWorkbook.Names
        .Add _
            Name:="genii", _
            RefersTo:=GenusList
        
        For Each Genus In GenusList
            Set GenusRow = Nothing
            Set SpeciesList = Nothing
            ListCount = 0
            
            ListCount = WorksheetFunction.CountIf(Columns("A:A"), Genus)
            
            Set GenusRow = Cells.Find(Genus, after:=Range("a1"), SearchOrder:=xlRows, LookIn:=xlValues, SearchDirection:=xlNext)
            
            SpeciesList = GenusRow.Offset(0, 1).Resize(rowsize:=ListCount).Value
            
            .Add _
                Name:=Genus, _
                RefersTo:=SpeciesList
        Next Genus
    End With
End Sub

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

Sub VBA_ValidationList()
    Dim GenusList   As Variant, _
        Genus       As Variant, _
        SpeciesList As Variant, _
        TestCell    As Range, _
        GenusRow    As Range, _
        ListCount   As Long, _
        RowPtr      As Long
        
        GenusList = Array(Cells(1, 1).Value)
        For RowPtr = 2 To Cells(Rows.Count, "A").End(xlUp).Row
            If Cells(RowPtr, "A").Value <> Cells(RowPtr - 1, "A").Value Then
                ListCount = ListCount + 1
                ReDim Preserve GenusList(0 To ListCount)
                GenusList(ListCount) = Cells(RowPtr, "A").Value
            End If
        Next RowPtr
'-------------------------------------------------------------------------
        With Sheets("sheet1").Range("F2").Validation
            .Delete
            .Add Type:=xlValidateList, Formula1:=Join(GenusList, ",")
        End With
'-------------------------------------------------------------------------
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Address(0, 0) <> "G2" Then Exit Sub
        Dim Genus As String
        Dim GenusList As Variant
        Dim SpeciesList As Variant
        Dim GenusRow As Variant
        Dim ListCount As Long
        Dim LastGen As Long
        
        Genus = Range("F2").Value
        LastGen = Cells(Rows.Count, "A").End(xlUp).Row
        
        With Range("A1:a" & LastGen)
            Set GenusRow = .Find(Genus, LookIn:=xlValues, SearchDirection:=xlNext)
        End With
        
        ListCount = WorksheetFunction.CountIf(Columns("A:A"), Genus)

        SpeciesList = GenusRow.Offset(0, 1).Resize(rowsize:=ListCount).Value
        SpeciesList = WorksheetFunction.Transpose(SpeciesList)
        With ActiveSheet.Range("G1").Validation
            .Delete
            If ListCount > 1 Then
                .Add Type:=xlValidateList, Formula1:=Join(SpeciesList, ",")
            Else
                .Add Type:=xlValidateList, Formula1:=SpeciesList
            End If
        End With
End Sub

[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??

[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.

[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

[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

[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

[Moo the Dog]

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

Sub Button2_Click()
Cells(1, 1).Value = Rnd()
Cells(1, 2).Value = Cells(1, 2).Value + Cells(1, 1).Value
End Sub

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

[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

[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

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?

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...

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

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

[:) Sixthsense :)]

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

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

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

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

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

[ChemistB]

Feel free to jump in on this one.

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

[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!

[bebo021999]

I leave this to sharper minds than mine.

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

[ChemistB]

Needs VBA solution.

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

[ChemistB]

Going beyond my limited VBA skills

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

[ChemistB]

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

Good clear title says everything.

[ChemistB]

Requires Array formulas and no helper columns

http://www.excelforum.com/excel-form...condition.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

[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

[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

[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

[ChemistB]

This one will require VBA

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

[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.

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

[ChemistB]

I've never seen this before.

http://www.excelforum.com/excel-form...worksheet.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

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


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

[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.

[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)

[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?

[xladept]

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

[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

[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

[TMS]

@xladept: ooohhhh ... good thought.

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

Sub Button2_Click()
Dim dValue As Double
dValue = Rnd(Timer)
Cells(1, 1).Value = dValue
Cells(1, 2).Value = Cells(1, 2).Value + dValue
Cells(Cells(Rows.Count, 2).End(xlUp).Row + 1, 2) = dValue
End Sub

Regards, TMS

[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

[martindwilson]

you have to use randomize i think

Sub Button2_Click()
Randomize
Cells(1, 1).Value = Rnd()
Cells(1, 2).Value = Cells(1, 2).Value + Cells(1, 1).Value
End Sub

[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!

[TMS]

@Moo: ditto. Although ...

always quite helpful

[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

[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

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.

[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.

Hyperlinks.Add Anchor:=Sheets("Received").Range(Target.Offset(0, 1).Address), _
            Address:="", SubAddress:= _
            "Data!" & Target.Address, TextToDisplay:=Target

Sheets("Received").Range(Target.Offset(0, 1).Address) = "=HYPERLINK(""#Data!" & Target.Address & """,""" & Target & """)"

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

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 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

[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]

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

LOL, made me chuckle, Tony

[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

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.

[:) 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

[:) 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 Laughing myself

[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

[Izandol]

I think you must ask Microsoft.

[:) Sixthsense :)]

I think you must ask Microsoft.

lol

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

[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...

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 , I misinterpreted it with Mac since I never seen Mac till now

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 ROUNDUP / ROUNDDOWN since in his Windows Mobile which have only ROUND() alone. I think this thread similar like that one

[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

[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

[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

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

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.

[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 anyone please jump in and save me

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

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

[:) Sixthsense :)]

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

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:

With Worksheets("Sheet1")
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    .Range("A:A").AdvancedFilter xlFilterInPlace, Unique:=True
    MyRegions = Application.WorksheetFunction.Transpose(.Range("A2:A" & LastRow).SpecialCells(xlVisible))
    .AutoFilterMode = False

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.

[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

http://www.excelforum.com/excel-prog...ell-value.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

[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

[alansidman]

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

[TMS]

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

[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???

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Dim myDate As Date
myDate = Application.WorksheetFunction.Max(Sheets("Sheet1").Range("A1"), _
    Sheets("Sheet2").Range("A1"), Sheets("Sheet3").Range("A1"))
Sheets("Sheet1").Range("A1") = myDate
Sheets("Sheet2").Range("A1") = myDate
Sheets("Sheet3").Range("A1") = myDate
Sheets("Sheet1").Range("B1") = Environ("UserName")
Sheets("Sheet2").Range("B1") = Environ("UserName")
Sheets("Sheet3").Range("B1") = Environ("UserName")
End If
End Sub

[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 , 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

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 .

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

[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

[:) Sixthsense :)]

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

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

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