A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Search Forums
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Showing results 1 to 25 of 100
Search took 0.81 seconds.
Search: Posts made by: Roger Govier[_8_]
Forum: Worksheet Functions June 5th, 2010, 06:12 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 1
Views: 610
Posted By Roger Govier[_8_]
sum by rows in Pivot

Hi Dinesh

your data is in the wrong format for this to work.
The PT will not carry out a Grand Total by row, as A and B may not be
additive - column B could be Apples and column B could be...
Forum: General Discussion June 4th, 2010, 04:58 PM Posted to microsoft.public.excel.misc
Replies: 2
Views: 1,357
Posted By Roger Govier[_8_]
Using SUMIFs on a changing Pivot Table - Help!

Hi James

As Luke has told you, GetPivotData is designed to deal with exactly this
type of situation.
However, why are you wanting to do these calculations outside of the PT?
Can you not just filter...
Forum: General Discussion June 4th, 2010, 04:51 PM Posted to microsoft.public.excel.misc
Replies: 5
Views: 733
Posted By Roger Govier[_8_]
Sumproduct;3 criteria

Quick heads up, Don

You can use Entire columns in XL2007

However, I would not recommend it for use with Sumproduct.
It is a very processor intensive function, and does not have the built in...
Forum: Worksheet Functions June 4th, 2010, 04:36 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 2
Views: 433
Posted By Roger Govier[_8_]
How to tell COUNTIF to not count a few characters

Hi Rafa

Countif is a very efficient function
=COUNTIF(F14:F43,"OFF*")
will deal with both Off and Off Site
=COUNTIF(F14:F43,"Admin")

Deduct these values from your other value to get the net...
Forum: Worksheet Functions June 4th, 2010, 07:42 AM Posted to microsoft.public.excel.worksheet.functions
Replies: 8
Views: 680
Posted By Roger Govier[_8_]
How to give a string of data into different columns

Hi Elton

Mark your block of dataDataText to columnsDelimitedselect Comma as
delimitedFinish

--

Regards
Roger Govier
Forum: Worksheet Functions June 4th, 2010, 06:48 AM Posted to microsoft.public.excel.worksheet.functions
Replies: 6
Views: 656
Posted By Roger Govier[_8_]
vendor matches

Or avoiding the double lookup (hence marginally quicker)
for XL2003 and earlier
=IF(COUNTIF(A:A,G1),VLOOKUP(G1,A:B,2,0),"")
for XL2007
=IFERROR(VLOOKUP(G1,A:B,2,0),"")

--

Regards
Roger Govier
Forum: Worksheet Functions June 4th, 2010, 06:42 AM Posted to microsoft.public.excel.worksheet.functions
Replies: 4
Views: 695
Posted By Roger Govier[_8_]
Pre-configuring Pivot table "views"

Hi Herb

I tried downloading the original file again, and still had the same
problems.
That was without making any changes whatsoever.
The changes I made (as per my previous posting) were just to...
Forum: Worksheet Functions June 3rd, 2010, 07:41 AM Posted to microsoft.public.excel.worksheet.functions
Replies: 21
Views: 2,584
Posted By Roger Govier[_8_]
How can I insert a date which is the first date of the following m

Hi

With first date in A1, enter in A2
=DATE(Year(A1),MONTH(A1)+1,1)

--

Regards
Roger Govier
Forum: Worksheet Functions June 3rd, 2010, 07:37 AM Posted to microsoft.public.excel.worksheet.functions
Replies: 4
Views: 695
Posted By Roger Govier[_8_]
Pre-configuring Pivot table "views"

Hi Herbert
Very nice coding (as always)
However, I cannot get it to run properly.
In Sub FieldSelect, the program chokes on the line
.PivotItems(i).Visible = True
with "Error 1004 - Unable to set...
Forum: Worksheet Functions June 3rd, 2010, 06:44 AM Posted to microsoft.public.excel.worksheet.functions
Replies: 6
Views: 630
Posted By Roger Govier[_8_]
Copying daily data from one tab to a weekly summary table

Hi

Supposing your weeknum is in column H on Sheet1
With Weeknumin A2 of Sheet2 running down the page, enter in B2
=SUMIF(Sheet1!$H:$H,$A2,Sheeet1!A:A)
Copy across through columns C:H
Copy down the...
Forum: Worksheet Functions June 2nd, 2010, 04:15 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 5
Views: 469
Posted By Roger Govier[_8_]
Alternate Rounding Up & Down of .50 in a column

Hi

Try
=IF(MOD(ROW(),2)0,ROUNDUP(A1,0),ROUNDDOWN(A1,0))

Depending upon whether the first row you are trying to Roundup is Odd (use
formula as is) or if even the change to =

Copy down as...
Forum: Worksheet Functions June 2nd, 2010, 04:11 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 3
Views: 506
Posted By Roger Govier[_8_]
difference between two time

Hi

You can have negative times if you switch to the 1904 Date system, but
beware of any other dates you may have on the sheet altering.

Alternatively, if you can accept decimal times...
Forum: General Discussion June 2nd, 2010, 03:56 PM Posted to microsoft.public.excel.misc
Replies: 4
Views: 403
Posted By Roger Govier[_8_]
COUNT BETWEEN A SPECIFIC DATE RANGE

Hi Dave
I'm not fully understanding what you are after here.
You said that Jacob and my solutions solved your problem.

Are you saying, that on say Sheet2, if you have 25/04/2007 in cell A1, then...
Forum: Worksheet Functions June 2nd, 2010, 02:06 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 7
Views: 689
Posted By Roger Govier[_8_]
Summing a non continuous range

Hi

MOD(COLUMN($A3:$P3),4)0
will return True for all of the columns that are not Quarterly Values
The double unary minus -- coerces True to 1 and False to 0
Therefore the values for the columns you...
Forum: General Discussion June 2nd, 2010, 01:23 PM Posted to microsoft.public.excel.misc
Replies: 9
Views: 525
Posted By Roger Govier[_8_]
count between a date range

Hi Dave

Try
=IF(AND(M2=datecell,
M2=DATE(YEAR(datecell)-1,MONTH(datecell),DAY(datecell))),"Yes","No")

--

Regards
Roger Govier
Forum: Worksheet Functions June 2nd, 2010, 12:24 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 3
Views: 426
Posted By Roger Govier[_8_]
Loose formula/value (when building graphs)

Hi Paul

Just use Data Validation for your dropdown, rather than the Combo Box.
The Combo Box returns an index number of the sheet selected within the list.
DV will give just the Sheet name

--...
Forum: Worksheet Functions June 2nd, 2010, 10:18 AM Posted to microsoft.public.excel.worksheet.functions
Replies: 3
Views: 426
Posted By Roger Govier[_8_]
Loose formula/value (when building graphs)

Hi Paul

Sorry, I didn't test it before posting.
You're correct, it translates to the fixed range.

Can you work with a VBA solution?
If so then the following piece of event code might help.

Private...
Forum: Worksheet Functions June 2nd, 2010, 09:14 AM Posted to microsoft.public.excel.worksheet.functions
Replies: 10
Views: 868
Posted By Roger Govier[_8_]
Drop Down Lists 2007

Sorry, Gord and Max
Didn't read the bit about Paste List.
Fortunately Mike read the question correctly and gave you the answer.

--
-------
Regards
Roger Govier

"Gord Dibben" gorddibbATshawDOTca...
Forum: General Discussion June 1st, 2010, 10:11 PM Posted to microsoft.public.excel.misc
Replies: 3
Views: 261
Posted By Roger Govier[_8_]
Information for Pivot Table

Hi

Within the PT, highlight any cell with a dateright clickGroup &
OutlineGroupMonths

--
-------
Regards
Roger Govier
Forum: General Discussion June 1st, 2010, 10:10 PM Posted to microsoft.public.excel.misc
Replies: 3
Views: 242
Posted By Roger Govier[_8_]
How do you stack 3 words in one cell

Hi
Format the cell textWrapped
Then use Alt+Enter after each value where you want to start a new line

--
-------
Regards
Roger Govier

"Tami Sawyer" Tami wrote...
Forum: Worksheet Functions June 1st, 2010, 10:06 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 2
Views: 466
Posted By Roger Govier[_8_]
Calculate the number of seconds between two times

Hi
=(B1-A1)*84600
Format the cell with the formula as General or Number

XL stores times as fractions of a day (24 hours)
To get to Seconds therefore you need to multiply by 24*60*60 or 84600

--...
Forum: Worksheet Functions June 1st, 2010, 10:03 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 10
Views: 868
Posted By Roger Govier[_8_]
Drop Down Lists 2007

Hi Max

Formulas tabDefine Name or Formulas tabName ManagerNew
or as a shortcut
Alt+M+M+D
--
-------
Regards
Roger Govier
Forum: Worksheet Functions June 1st, 2010, 07:15 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 10
Views: 868
Posted By Roger Govier[_8_]
Drop Down Lists 2007

Hi

It sounds as though your "blahList" is more than one column Wide.

--
-------
Regards
Roger Govier

"tlynn" wrote in message
Forum: Worksheet Functions June 1st, 2010, 07:07 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 2
Views: 490
Posted By Roger Govier[_8_]
HLookup?

Hi

With Vendors names in A1:M1 and prices in A2:M2 and Min of A2:M2 in N2
=INDEX($A1:$M$1,MATCH($N2,$A2:$M2,0))

--
-------
Regards
Roger Govier
Forum: Worksheet Functions June 1st, 2010, 11:02 AM Posted to microsoft.public.excel.worksheet.functions
Replies: 4
Views: 479
Posted By Roger Govier[_8_]
Reference(s) to worksheets for building graphs

Hi Paul

I guess you missed the solution I posted to your first question
Hi

One way
With a list of your sheet names in A1:A20 of sheet Graphs
Create a named range called GraphRange with a value...
Showing results 1 to 25 of 100

 
Forum Jump

All times are GMT +1. The time now is 12:19 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 OfficeFrustration.
The comments are property of their posters.