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 5.10 seconds.
Search: Posts made by: JLatham
Forum: New Users May 15th, 2010, 09:18 PM Posted to microsoft.public.excel.newusers
Replies: 5
Views: 499
Posted By JLatham
#N/A error

We need to see the formulas in cells where you're getting the #N/A entries.
Typically the #N/A entry appears in formulas involving functions like
VLOOKUP().

You can "inject" a #N/A error in a...
Forum: General Discussion May 15th, 2010, 09:07 PM Posted to microsoft.public.excel.misc
Replies: 5
Views: 165
Posted By JLatham
Dynamic List Data Validation

Maybe try it this way? Remember that this code goes on into the code module
for the sheet this is all happening on: Right-click the sheet name and choose
[View Code] to get to the right place. ...
Forum: General Discussion May 14th, 2010, 09:54 PM Posted to microsoft.public.excel.misc
Replies: 5
Views: 165
Posted By JLatham
Dynamic List Data Validation

Are you using the code I provided, or Roger Govier's fromthe "reset column C"
help request? Not that it matters a whole lot, just so we know what we're
working with here.

As I understand your...
Forum: New Users May 14th, 2010, 12:56 PM Posted to microsoft.public.excel.newusers
Replies: 4
Views: 477
Posted By JLatham
How do I match records from two Sheets?

I may have misunderstood your request. The code above will find entries on
sheet 2 that are MISSING from sheet 1 and add them to the list on sheet 1.

You seem to want something that matches the...
Forum: New Users May 14th, 2010, 12:47 PM Posted to microsoft.public.excel.newusers
Replies: 4
Views: 477
Posted By JLatham
How do I match records from two Sheets?

This code should do it for you. You'll need to edit it to enter the correct
sheet names, column IDs that the records are in on each sheet, and indicate
the row on each sheet where the records...
Forum: General Discussion May 14th, 2010, 02:38 AM Posted to microsoft.public.excel.misc
Replies: 7
Views: 117
Posted By JLatham
Can you delete all asterisks in a sheet?

yeah, I realized that once I saw Gord's posting. Goes to show you that given
bad test data, even the worst solution may APPEAR to work great.

"Dave Peterson" wrote:

And yours did get rid of all...
Forum: General Discussion May 14th, 2010, 02:33 AM Posted to microsoft.public.excel.misc
Replies: 5
Views: 143
Posted By JLatham
DROP DOWN LIST WHEN SPREAD SHEET OPENS

Dave,
I personally prefer to use the Combo box from the Forms toolbar, but
situations vary. I like it mostly because if I have to assign a macro to it,
it can be one in a standard code module,...
Forum: General Discussion May 14th, 2010, 01:54 AM Posted to microsoft.public.excel.misc
Replies: 3
Views: 180
Posted By JLatham
Counting words within a merged range of cells

Try this formula:
=(LEN(D3)-LEN(SUBSTITUTE(UPPER(A15),UPPER(B9),"")))/LEN(B9)

It should accept A15 as the address for the merged range. The way it works
is it compares the UPPERCASE equivalents of...
Forum: General Discussion May 14th, 2010, 01:32 AM Posted to microsoft.public.excel.misc
Replies: 3
Views: 89
Posted By JLatham
Using SaveAs

Ok, as I understand it, you finish a month and use SaveAs to save it and then
return to using the original book (which I'll call "current book")? If
that's the case:

With the current book open,...
Forum: General Discussion May 13th, 2010, 02:36 PM Posted to microsoft.public.excel.misc
Replies: 4
Views: 88
Posted By JLatham
remove the #n/a and make it 0

You need to "wrap" your formula with a test for the error like this:

=IF(ISNA(your formula),0,your formula)

The most common #N/A comes from VLOOKUP() results without a match, so you
might have a...
Forum: General Discussion May 13th, 2010, 12:36 AM Posted to microsoft.public.excel.misc
Replies: 6
Views: 179
Posted By JLatham
How can I loop through a the values in multiple rows

First, take a look at Dave Peterson's post - he may have something for you.
It's doing essentially the same thing in a different way.

In the meanwhile, I checked, and the little code snippet you...
Forum: General Discussion May 13th, 2010, 12:28 AM Posted to microsoft.public.excel.misc
Replies: 5
Views: 143
Posted By JLatham
DROP DOWN LIST WHEN SPREAD SHEET OPENS

Let's see if it's a combo box from the Forms toolbar. Try right-clicking on
the control (while the sheet is unprotected) and see if a popup list appears.
If it does, then choose [Format Control]....
Forum: General Discussion May 12th, 2010, 10:18 PM Posted to microsoft.public.excel.misc
Replies: 2
Views: 389
Posted By JLatham
Version Upgrade

Unless 2003 (or 2007 or 2010) has something you specifically need and 2000 is
still doing the work you need it to do, stick with 2000. You'll probably
have a difficult time finding a copy of 2003...
Forum: General Discussion May 12th, 2010, 10:13 PM Posted to microsoft.public.excel.misc
Replies: 3
Views: 388
Posted By JLatham
SUMPRODUCT with Date?

That was going to be my response, but the site went sloooooow on me.

T.Valko's will give the quarter, which may aid him further down the road
rather than having to add yet another --() to see if...
Forum: General Discussion May 12th, 2010, 10:10 PM Posted to microsoft.public.excel.misc
Replies: 7
Views: 117
Posted By JLatham
Can you delete all asterisks in a sheet?

Hey, mine worked in my special case (just * entered into a few cells). Oh
well....

"Gord Dibben" wrote:

Congrats.

You are the only responder who got it rightg

Forum: New Users May 12th, 2010, 07:46 PM Posted to microsoft.public.excel.newusers
Replies: 1
Views: 384
Posted By JLatham
link text box in powerpoint to a cell

Try it this way: with the Excel file open, select the cell with the data you
need (K4) and Copy it.
Now go click in the text box on the powerpoint presentation and choose
Edit -- Paste Special and...
Forum: General Discussion May 12th, 2010, 07:33 PM Posted to microsoft.public.excel.misc
Replies: 5
Views: 125
Posted By JLatham
formula for CF

Select cells A6:E35 and use Conditional Formatting and Formula is
type this in as the formula:
=$E6="done"
choose the shading you want and hit [OK].

"dp" wrote:

Excel 2007. I've read other...
Forum: General Discussion May 12th, 2010, 07:27 PM Posted to microsoft.public.excel.misc
Replies: 2
Views: 186
Posted By JLatham
Auto Page Numbering Question

Custom Footer:
in the section for the footer where you want the page number, hit the page
icon which should insert
&[Page]
and immediately type
+8 of 12
and voila! you're done.

So the entry might...
Forum: General Discussion May 12th, 2010, 07:21 PM Posted to microsoft.public.excel.misc
Replies: 6
Views: 179
Posted By JLatham
How can I loop through a the values in multiple rows

I think what you wrote will probably work, you just need to change your
formula to include the = symbol:

ActiveCell.FormulaR1C1 = "=R[" & i & "]C"

which should be the same as typing something like...
Forum: General Discussion May 12th, 2010, 03:50 PM Posted to microsoft.public.excel.misc
Replies: 5
Views: 143
Posted By JLatham
DROP DOWN LIST WHEN SPREAD SHEET OPENS

Is this drop down a control or is it in a cell?

I'll tackle the drop-down-in-a-cell possibility, and if it turns out to be a
control, the discussion will continue g.

If it is in a cell, then the...
Forum: General Discussion May 12th, 2010, 03:28 PM Posted to microsoft.public.excel.misc
Replies: 2
Views: 121
Posted By JLatham
Need Macro to reset dependent list

Right-click on the worksheet's name tab and choose [View Code] then copy and
paste the code below into the code module.

Private Sub Worksheet_Change(ByVal Target As Range)
'this will clear the...
Forum: General Discussion May 12th, 2010, 03:22 PM Posted to microsoft.public.excel.misc
Replies: 2
Views: 223
Posted By JLatham
copy and pasting one file into several different folders

Not literally. You can copy one file and then choose different folders, one
at a time, and paste the copy into them individually. But at least you don't
have to go back to the original and start...
Forum: General Discussion May 12th, 2010, 03:20 PM Posted to microsoft.public.excel.misc
Replies: 2
Views: 106
Posted By JLatham
copy formulae in excel

Easiest way is to make the cell references absolute. If your formula looks
like this:
=A5 + B6
change it to
=$A$5 + $B$6
and then you can copy it without changing the references. The $ makes the...
Forum: General Discussion May 12th, 2010, 03:18 PM Posted to microsoft.public.excel.misc
Replies: 7
Views: 117
Posted By JLatham
Can you delete all asterisks in a sheet?

Edit -- Replace
enter an asterisk ( * ) into the find what,
leave the replace with empty
[Replace All]

I'd make a copy of the book before doing this, just in case you discover you
really didn't...
Forum: General Discussion May 12th, 2010, 01:36 PM Posted to microsoft.public.excel.misc
Replies: 1
Views: 151
Posted By JLatham
version differences in excel 2007 - use of macros & range names

If we could see the code, it would surely help. Might even be of some help
if we knew what the named ranges were referring to.

As for the Student/Home version; check your macro security settings. ...
Showing results 1 to 25 of 100

 
Forum Jump

All times are GMT +1. The time now is 10:54 AM.


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