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.58 seconds.
Search: Posts made by: Luke M[_4_]
Forum: Worksheet Functions June 4th, 2010, 09:09 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 4
Views: 333
Posted By Luke M[_4_]
COUNTA or COUNTIF or COUNT

=SUMPRODUCT(--(C3:C5="YES"),--(B3:B5="Bill"))

--
Best Regards,

Luke M
"Liz" wrote in message
...
Trying to...
Forum: General Discussion June 4th, 2010, 08:33 PM Posted to microsoft.public.excel.misc
Replies: 1
Views: 400
Posted By Luke M[_4_]
Conditonal Format based on two different criteria

It should work...make sure your time really is greater than 0.416, and not
something like 0.41599999999

--
Best Regards,

Luke M
"Chad_Oglesbay" wrote in...
Forum: Worksheet Functions June 4th, 2010, 08:20 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 3
Views: 409
Posted By Luke M[_4_]
Formula to pull the lowest supplier name

To get the minimum and ignore zeros, can use this array (confirm formula
using Ctrl+Shift+Enter, not just Enter) formula:
=MIN(IF(B3:D30,B3:D3))

To get Supplier...
Forum: General Discussion June 4th, 2010, 08:14 PM Posted to microsoft.public.excel.misc
Replies: 2
Views: 437
Posted By Luke M[_4_]
How do I look at two worksheets in one Excel workbook at same time

Window - New Window
this will create a second view of active workbook. Then you can do Window -
Arrange, active workbook only.

--
Best Regards,

Luke M
"PeoriaJean"...
Forum: General Discussion June 4th, 2010, 08:14 PM Posted to microsoft.public.excel.misc
Replies: 5
Views: 333
Posted By Luke M[_4_]
Find Function

=FIND("B",SUBSTITUTE(A1,"B",CHAR(160),1))

Note that CHAR(160) is simply a non-printable character rarely used in
normal usage. The formula replaces the first instance of desired letter, and
then...
Forum: Worksheet Functions June 4th, 2010, 07:19 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 2
Views: 369
Posted By Luke M[_4_]
Workbook causes calculation errors

Thanks, Charles, that seems to describe what's going on. Any thoughts as to
why the Charts in Workbook B are affected so (not updating)?

As your website says, there doesn't seem to be a good way to...
Forum: Worksheet Functions June 4th, 2010, 04:04 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 2
Views: 369
Posted By Luke M[_4_]
Workbook causes calculation errors

I've never seen this error before, and unfortunately, my Google searches
keep bogging me down with the simple "Automatic/Manual" solution...

I have Workbook A, which contains a large amount of...
Forum: New Users June 4th, 2010, 02:05 PM Posted to microsoft.public.excel.newusers
Replies: 2
Views: 2,058
Posted By Luke M[_4_]
copy selected rows to second worksheet (NOT Cut + Paste)

Let's say you mark the rows you want by placing an "X" in column A. To pull
column B of selected rows, could do this array* formula:

=IF(ROWS(A$20:A20)COUNTIF('Sheet 1'$A:$A,"X"),"",INDEX('Sheet...
Forum: Worksheet Functions June 4th, 2010, 01:39 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 3
Views: 383
Posted By Luke M[_4_]
Import old dos sequencial data

Text to Columns, fixed length?

--
Best Regards,

Luke M
"Rich Stone" wrote in message
...
I have an...
Forum: General Discussion June 4th, 2010, 01:29 PM Posted to microsoft.public.excel.misc
Replies: 2
Views: 1,214
Posted By Luke M[_4_]
Using SUMIFs on a changing Pivot Table - Help!

Assuming your PivotTable has subtotals, you might be able to use some
version of the GETPIVOTDATA function. (see XL help)

--
Best Regards,

Luke M
"James" wrote...
Forum: General Discussion June 4th, 2010, 01:27 PM Posted to microsoft.public.excel.misc
Replies: 9
Views: 487
Posted By Luke M[_4_]
Conditional Formating

Copy the format. (easiest way is the format painter)

Or, select all the cells of interest, and then apply the conditional format.

--
Best Regards,

Luke M
"Going Crazy with excel"...
Forum: Worksheet Functions June 4th, 2010, 01:24 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 2
Views: 397
Posted By Luke M[_4_]
Size of file with pivot table

Were the 6 PivotTable independent, or did they all use the same cache?

If you build PivotTables that use another PivotTable as reference (for when
you're reference the same raw data, just taking a...
Forum: General Discussion June 4th, 2010, 01:18 PM Posted to microsoft.public.excel.misc
Replies: 2
Views: 698
Posted By Luke M[_4_]
Excel calculation problem

Are you in manual calculation mode? (try hitting F9)

--
Best Regards,

Luke M
"scarmalt" wrote in message...
Forum: Charts and Charting June 2nd, 2010, 09:00 PM Posted to microsoft.public.excel.charting
Replies: 2
Views: 2,139
Posted By Luke M[_4_]
Column Chart Conditional Formatting

You'll need to do this by manipulating the actual data.

Setup 3 dummy series, first with a static value of 10. On the chart, format
these green, red, and grey respectively. Now, go back and change...
Forum: General Discussion June 2nd, 2010, 08:56 PM Posted to microsoft.public.excel.misc
Replies: 2
Views: 320
Posted By Luke M[_4_]
Excel turning percentages into scientific notation

Perhaps add a last line of code the reads:

Cells.NumberFormat = "0.00%"

--
Best Regards,

Luke M
"mr-tom" wrote in message...
Forum: General Discussion June 2nd, 2010, 08:53 PM Posted to microsoft.public.excel.misc
Replies: 2
Views: 462
Posted By Luke M[_4_]
Conditional Formatting, Date Ranges and Rows

Select the entire row.
Conditional format,
formula 1:
=TODAY()=$R3+30

formula 2:
=AND(TODAY()$R3+30,TODAY()=$R3+60)

formula 3:
=TODAY()$R3+60
Forum: General Discussion June 2nd, 2010, 08:51 PM Posted to microsoft.public.excel.misc
Replies: 3
Views: 483
Posted By Luke M[_4_]
sumif only seeing one row

SUMIF doesn't work like that. The second range (a 9x107 matirix) is not the
same size as the first range (a 9x1 matrix).

If the totals are already in row 113, why not do:
=SUMIF('Sheet...
Forum: General Discussion June 2nd, 2010, 08:41 PM Posted to microsoft.public.excel.misc
Replies: 1
Views: 408
Posted By Luke M[_4_]
Locking a cell based on a cells content

Data - Validation, Custom
=COUNTBLANK(B1:C1)2

Also, uncheck the "ignore blank" option.

--
Best Regards,

Luke M
"The Rook" wrote in message
Forum: Worksheet Functions June 2nd, 2010, 05:08 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 3
Views: 333
Posted By Luke M[_4_]
Formula Help

=IF(G2="bail",B2&","&G2&","&H2,"")

--
Best Regards,

Luke M
"HeatherJ" wrote in message
...
I need a...
Forum: Worksheet Functions June 2nd, 2010, 03:59 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 3
Views: 425
Posted By Luke M[_4_]
difference between two time

XL can not display negative times/dates. You could "cheat" and do something
like:
=IF(B1A1,"-","")&TEXT(B1-A1,"h:mm")&" hrs"

--
Best Regards,

Luke M
"Radhakant Panigrahi" ...
Forum: Worksheet Functions June 2nd, 2010, 03:27 PM Posted to microsoft.public.excel.worksheet.functions
Replies: 2
Views: 380
Posted By Luke M[_4_]
Counting cells with conditional formatting

Use the formula that the Conditional format is based on to calculate your
count. There is no built-in way of counting color within XL, and even using
VB, counting conditional formats is tricky.

--...
Forum: New Users June 2nd, 2010, 01:33 PM Posted to microsoft.public.excel.newusers
Replies: 23
Views: 5,443
Posted By Luke M[_4_]
The Day After ???

I was thinking the same thing. Long live the newsgroups?

--
Best Regards,

Luke M
"John" wrote in message
...
June 2, 8.00 am and...
Forum: General Discussion June 1st, 2010, 07:15 PM Posted to microsoft.public.excel.misc
Replies: 8
Views: 316
Posted By Luke M[_4_]
Active Workbook

Correct, when you save-as, you are changing the name of active file, not
creating a copy. Perhaps this code?

Sub ReName()
xName = ActiveWorkbook.FullName
'Change this to something...
Forum: General Discussion June 1st, 2010, 06:54 PM Posted to microsoft.public.excel.misc
Replies: 2
Views: 212
Posted By Luke M[_4_]
"Save file before closing"

Sounds like at least one of the files has a volatile function such as:
=NOW()

Possible workaround:
Open XL, change calculation mode to manual (Tools-Options-Calculation), then
open all the files...
Forum: General Discussion June 1st, 2010, 06:47 PM Posted to microsoft.public.excel.misc
Replies: 5
Views: 429
Posted By Luke M[_4_]
Text to Columns

Instead of using Text to columns, I'd do this via formulas:
First Name:
=LEFT(A2,FIND(" ",A2)-1)

Surname:
=MID(A2,FIND(" ",A2)+1,999)

--
Best Regards,
Showing results 1 to 25 of 100

 
Forum Jump

All times are GMT +1. The time now is 12:30 AM.


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