Posts by hyperdreamz

ON the left hand side if you input desired count and weekly offs’s the equation generates an output grid consumed shift planning equation.

I wish to reverse the equation and enter the output grid and aquire the weekly schedules by reverse enginering the formulas used.

Please help.

Objective: With reference to current sheet, select another sheet by user input and conditional format to highlight differences in cell f23 current sheet vs. f23 selected sheet

I have combined a script from tom-urtis.5 with my recorded macro in order to achive this outcome.

The conditional formating is applied, but for some reason the condional formating formula is ending up encapsulated with double quotes

i.e "F23<>Sheet1!F23"

When I remove the douple quotes the formating works.

i.e F23<>Sheet1!F23

What am I doing wrong in this code?

Sub Show_Diffrences_v1()

Const ColItems  As Long = 20
Const LetterWidth As Long = 20
Const HeightRowz As Long = 18
Const SheetID As String = "__SheetSelection"
 
Dim i%, TopPos%, iSet%, optCols%, intLetters%, optMaxChars%, optLeft%
Dim wsDlg As DialogSheet, objOpt As OptionButton, optCaption$, objSheet As Object
optCaption = "": i = 0
 
Application.ScreenUpdating = False
 
On Error Resume Next
Application.DisplayAlerts = False
ActiveWorkbook.DialogSheets(SheetID).Delete
Application.DisplayAlerts = True
Err.Clear
 
Set wsDlg = ActiveWorkbook.DialogSheets.Add
With wsDlg
.Name = SheetID
.Visible = xlSheetHidden
iSet = 0: optCols = 0: optMaxChars = 0: optLeft = 78: TopPos = 40
 
For Each objSheet In ActiveWorkbook.Sheets
If objSheet.Visible = xlSheetVisible Then
i = i + 1
 
If i Mod ColItems = 1 Then
optCols = optCols + 1
TopPos = 40
optLeft = optLeft + (optMaxChars * LetterWidth)
optMaxChars = 0
End If
 
intLetters = Len(objSheet.Name)
If intLetters > optMaxChars Then optMaxChars = intLetters
iSet = iSet + 1
.OptionButtons.Add optLeft, TopPos, intLetters * LetterWidth, 16.5
.OptionButtons(iSet).Text = objSheet.Name
TopPos = TopPos + 13
 
End If
Next objSheet
 
If i > 0 Then
 
.Buttons.Left = optLeft + (optMaxChars * LetterWidth) + 24
 
With .DialogFrame
.Height = Application.Max(68, WorksheetFunction.Min(iSet, ColItems) * HeightRowz + 10)
.Width = optLeft + (optMaxChars * LetterWidth) + 24
.Caption = "Select sheet to compare with"
End With
 
.Buttons("Button 2").BringToFront
.Buttons("Button 3").BringToFront
Application.ScreenUpdating = True
 
If .Show = True Then
For Each objOpt In wsDlg.OptionButtons
If objOpt.Value = xlOn Then
optCaption = objOpt.Caption
Exit For
End If
Next objOpt
End If
 
If optCaption = "" Then
MsgBox "You did not select a worksheet.", 48, "Cannot continue"
Exit Sub
Else
 
MsgBox "You selected the sheet named ''" & optCaption & "''." & vbCrLf & "Click OK to apply formating", 64, "FYI:"
'Sheets(optCaption).Activate
 
'---------------------

    Range("F23").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:="F23<>" & optCaption & "!F23"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .Color = 16764108
        .TintAndShade = 0
    End With
    Selection.FormatConditions(1).StopIfTrue = False

'---------------------
End If
 
End If
 
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
 
End With

End Sub

I'm personally a proficient PowerPoint user, but for some reason I've hit a wall

I need assistance in learning the technique used to create the shape/color of the pillar in attached slide.

In particular I'm curious how the gradient was created for the pillar, I just cant seem to get it right.

Re: Calculate Max Concurrent Connected Calls In A Halfhour Intervals Frm Strt &amp; End Time

solved

Hi All

Need a little help in calculating the maximum concurrent connected calls in a half hour based on a dump of start time and end time of 4000000 call records in ACCESS.

I have been supplied a raw database in ACCESS.

For your reference I have attached a sample EXCEL file to represent the data I have in ACCESS.

I need to populate columns “E” to “AZ” with the count.

For example

Start Time 05/08/2008 1:57 AM
Stop Time 05/08/2008 2:37 AM

Would have a count in the following intervals

01:30 to 02:00 = 1
02:00 to 02:30 = 1
02:30 to 03:00 = 1

Any help or suggestion would be highly appreciated.

Hyper Dreamz

Re: Disable &quot;Automatic Calculation&quot; only on CERTAIN SHEETS in a Workbook ?

.

Thanks a million

Appreciate the assistance

However even with that my sheets going all crazy on me

I jus give up???

I?ll probably have to redo the whole thing from scratch cause something is screwing something somewhere an I cant seem to figure it out?????

Thanks once aging

Hyperdreamz

.

Re: Disable &quot;Automatic Calculation&quot; only on CERTAIN SHEETS in a Workbook ?

Plz forgive me if I seem like a little excel newbie

But I am one

I'm not a pro at VB so I'm kind of lost here, my macro creating skills a limited to CTRL+C & CTRL+V and most basic editing

I have 9 sheets in the workbook

I have sheets 2 - 6 to be on Automatic Calculation

And Sheets 7 - 9 on Manual Calculation

Could u be so kind as to give specific steps as to achieve this result?

.

Re: Disable &quot;Automatic Calculation&quot; only on CERTAIN SHEETS in a Workbook ?

Whr should i insert tht code ?

Disable "Automatic Calculation" only on CERTAIN SHEETS

1) Is it possible to disable "Automatic Calculation" only on CERTAIN SHEETS in an Excel Workbook?

2) Is it possible to do an ActiveSheet.Calculate every time a user uses a particular Drop-Down Menu?

I have a mini business application I just got done with

I use o LOT of SUMPRODUCTS on certain sheets

And the data that it working with is vast

So due to the Automatic calculate function being enabled by default my comp just goes loopy on me every 30 second?s I?m using the sheet (switching between sheets)

So I went ahead and disable Automatic calculations and made it Manual

Provided a macro do an ActiveSheet.Calculate when I need it to update

Sadly this has caused more problems than it solved

On certain sheets I have some VLOOKUP's INDEX's that need to be automatically calculated but since I turned the damm thing off it will only update when I run the macro

But this screw's up the resultant data on screen producing hundreds of errors (UGLY)

This has totally frustrated me after putting in so much effort into this project it finally seemed to be done then this......

I would really appreciate any assistance with this problem

Regards,
HyperDreamz

Re: Make a dynamic alphabetically sorted UNIQUE list

.

Issue resolved

Modified ur code a little & its working like a charm

Thanks a million

Regards,
Hyperdreamz

,

Re: COUNTIF data after applying Auto filter

.

headings are replaced too

the formatting re-applied

but if it’s needed I can go the A2 way too

so long as this results in only the filtered data appearing to the right

if all the data reappears

the and I save the file in error

it will never open (at least on the lame ass mc I work wit)

jus ran a check wit this week’s data 175MB

had to delete it because it kept freezing every time I opened it

I think it’s due to all the SUMPRODUCT's I have working in there

Hyperdreamz

.

Re: COUNTIF data after applying Auto filter

What happens is

On the sheet DATA Complete select all clear is done

Then a paste only values is done

This is done because next conditional formatting is applied that changes week to week

I too researched the filter bit ur talking bout

Plz forgive my naivety

The best way I can describe it is that the cells that have the calculations in will be cleared every week

And new data pasted in there

Resulting in blank cells where ur doing the calculations now

In spite of that it’s returning the unique values on the unique sheet after that

But along with it its pasting all the data in the range DATABASE on sheet data to the right wher u did the calculations

Can pasting of all the data again be prevented?

Try clearing the cells where ur doing the calculations on DATA then go to UNIQUE & come back to DATA & see what happens

U’ll se what I mean

Hyperdreamz

Re: Searching for duplicate values in a list

=IF(COUNTIF($A$1:A1,A1)=1,"",A1)

put this in 1st cell

in B

fill down

in C1 (or anywere) run a count if on B

Done

Seasons Greetings,
Hyperdreamz

Re: Searching for duplicate values in a list

This may help you

http://www.cpearson.com/excel/duplicat.htm

Seasons Greetings,
Hyperdreamz

Re: COUNTIF data after applying Auto filter

A quick question

The file that u sent works like a charm however there’s a little snag

I noticed that certain calculation are being performed on sheet DATA to obtain the unique data on sheet UNIQUE

However this (DATA) sheet is updated constantly with a huge cut paste of a dump of an html page

This will overwrite anything on this sheet

It’s a paste of pure values resulting in those being effectively erased

Now this actually works inspire of that but I’m getting all the info on sheet DATA pasted over to the right when the original cell were cleared and the macro re run

This bloats up the file resulting in a 25-30 MB file when a real sample of the data is used.

This in turn kills my comp

Is it possible to move all the calculations to the unique sheet only?

And prevent the data getting pasted over again

Sheet UNIQUE must be a stand alone sheet and should not have any reference to any other sheet other than DATA

The sole function must be to obtain unique values from the given columns on sheet DATA

Also I would old like to thank you for all the assistance I’m really obliged by the prompt and fantastic solutions ur throwing my way

It’s all truly appreciated

Seasons Greetings,
Hyperdreamz

.

Re: COUNTIF data after applying Auto filter

Thanks a ton man

That file put me back on track

However there a small modification I need

I need it too dump ALL the unique values to a sheet called “Unique”

Column A = UNIQUE Tech Name
Column B = UNIQUE Tech Badge
Column C = UNIQUE Manager Name
Column D = UNIQUE Fiscal Week Number

Instead of manually running the macro to generate the lists is it possible to run the macro by just viewing the UNIQUE sheet.

i.e. the UNIQUE macro must be triggered when a user views the UNIQUE sheet thereby updating the unique values

For now I’m dropping the upper lower limit on the fiscal week

I think I’ve found another simpler way to tackle that problem

Just need a complete list of all UNIQUE values

Is this possible?

Seasons Greetings,
Hyperdreamz

Re: COUNTIF data after applying Auto filter

I have received excellent solutions from the people here

But sadly don’t have a final solution

I have got bits and pieces from many people that are getting me there

I sincerely appreciate the effort

However I’m going to update you guys very soon with my progress

My current points of failure in the solution are :-

- My inability to obtain a DYNAMICALLY UPDATED UNIQUE LIST for given parameters. I need these unique lists for the DROP DOWN menus.

For a given Upper limit fiscal week & Lower limit fiscal week I need a DYNAMICALLY UPDATED UNIQUE LIST of

1) Tech names
2) Badge ID’s
3) Fiscal weeks
4) Manager Names

- For a given fiscal week range pull up a DYNAMICALLY UPDATED UNIQUE LIST of manager and the corresponding techs that appear under those individual managers

Seasons Greetings,
Hyperdreamz

Re: Make a dynamic alphabetically sorted UNIQUE list

Both valid solutions

But sadly don’t solve my problem

I appreciate the effort anyways

Seasons Greetings,
Hyperdreamz

Re: Make a dynamic alphabetically sorted UNIQUE list

Got the plug-in

Every thing works like a charm

But is there a way to distribute the plug-in embedded in the file

Or that particular function

For e.g. can I extract the UNIQUE VALUES function to say a macro and sent it as a part of a single file????

Quote from Krishnakumar

See the attached file.

Kris

Re: Make a dynamic alphabetically sorted UNIQUE list

I actually got the file to open and even got it extracting unique values from multiple columns

Hover the single resultant sheet for 5000 records was 8MB and froze forever when entering any data

Is there a way to do the same thing using a DYNAMIC RANGE

Something that won’t bloat up the file to such un-reasonable sizes and actually open

On an average 2000-5000 records will be added on a weekly basis

The calculations bust be instantaneous or at least reasonable

I can’t seem to get this to work

Plz help ! !

I’m ready to even accept a macro at this stage

Need to jus get UNIQUE ALPHABETIACLLY SORTED VALUES from columns A, E, & F from a sheet called DATA and save those to a sheet called UNIQUE in columns A, B & C respectively without the need to run a macro somthing that will probably auto update the moment u switch between sheets.

Plz help Ahhhhhhhhhhhhhhh !!!

Quote from Hyperdreamz

I HAVE OFFICE XP

THT FILE TOOK 15 MIN TO DO SOME CALCULATION THEN FROZE ON ME AT LIKE 76% (CALCULATING CELLS)

IS THIS NORMAL ?

OR AM I DOIN SOMETHING WRONG ?

Display More

Quote from Domenic

Since I make it a practice not to open attachments, I've attached a sample file for you.

You'll notice that the formulas have been copied to Row 20 only, to keep the size of the file small. Any new data you enter, up to and including that row, will automatically be updated.

Hope this helps!