# Posts by RichardS

• ## checking if a cell is numeric

Re: checking if a cell is numeric

Code
``=IF(ISNUMBER(A1)=TRUE,Your Formula Here,Do Nothing)``

If A1 contains a number, your formula is processes, otherwise nothing happens.

HTH

Richard

• ## cmp 2 cols and assign occurences count

Re: cmp 2 cols and assign occurences count

You eg looks like column C & D are doing the same thing. I've assumed you want to assign a count (1) in column C, then a cumulative count in column D. If so, try this

In column C a simple test to see if A & B are the same

Code
``=IF(B1=A1,1,0)``

In column D, a cumulative total

Code
``=IF(C1=1,SUMIF(\$B\$1:B1,B1,\$C\$1:C1),0)``

which is copied down as far as your data goes.

Any help?

Richard

• ## member info retrivial from number???

Re: member info retrivial from number???

Sorry about delay in responding, I've been sleeping etc.

In the workbook I attached, the formulae can be modified to look at a list in a different book. Once you have one row working OK, it's just a matter of copying it down as far as you need. The \$ signs in the formulae make them absolute, so when you copy them down they stay the same. Those refs without \$ are relative, and will change as you copy down.

The easiest way to create the formulae is to start typing =IF( then highlight the cells as needed.

I'm not sure that I can provide much more advice. As you note, you appear to have limited experience with Excel. Hang in there and tough this one out. In my opinion, VLOOKUP and HLOOKUP are probably two of the most useful features of Excel, so once you get it right, you'll be on your way.

Richard

• ## member info retrivial from number???

Re: member info retrivial from number???

Richard

• ## member info retrivial from number???

Re: member info retrivial from number???

Revised eg. Note, labels in the extract area must match exactly the labels on your source data. Are we getting closer?

Richard

• ## member info retrivial from number???

Re: member info retrivial from number???

It depends on the layout of your members database, but I suggest you look in Excel Help at VLOOKUP and HLOOKUP functions. If that doesn't make sense, post back with a bit more detail on the layout of your source data.

Richard

ps added a samll eg to get you going

• ## Comparing list

Re: Comparing list

=MATCH(A1,\$B\$1:\$B\$500,0)

will return #N/A if there is no match. If there is a match, it returns the position in column B of the corresponding entry in column A.

HTH

Richard

• ## Footy Tipping Minimum Score

Re: Footy Tipping Minimum Score

Ta very much, works a treat, once I tweaked my ISBLANK formula.

Richard

• ## Footy Tipping Minimum Score

Hi all,

I've been charged with developing a spreadsheet to run the local footy tipping comp. The rules include one which states if you don't get your tips in on time, you get the lowest score of the other pickers. I was trying to use MIN, but of course if the Tipster didn't pick, he would have a value of 0, so that is the MIN. Therefore, what I need is a formula which selects the lowest value from a range, but ignores zero values.

Any ideas. This is obviously really important.

• ## compare and copy data

Re: compare and copy data

I'm sure there is a more glamorous VBA solution, but you could use VLOOKUP if you are prepared to receive errors when there is no match. You could always paste values, then sort the results and remove the errors.

In your destination sheet, column A

=Sheet1!A1

Column B

=Sheet1!B1

Column C

=VLOOKUP(\$A1,Sheet1!\$C\$1:\$E\$7,2,0)

Column D

=VLOOKUP(\$A1,Sheet1!\$C\$1:\$E\$7,3,0)

Copy the formulae down as far as the source data in Sheet1 goes.

If what is in column A doesn't exist in column C, you will get #N/A errors in columns C & D. As I said, once you've got the results, you could paste values, sort and delete the error rows.

Probably not what you're after, but the best I can come up with.

Richard

• ## Re-appear after calling other application

Re: Re-appear after calling other application

Hi Andy,

Thanks for the reply. That's not the way it behaves for me. If I have Explorer open, or Outlook, or our finance package, when I close the browser window, something else has the focus. Just did it then, and this browser window is active, not my program. Not a big issue, just hoped to make it a little more professional for end users. It's not a commercial system or anything, so don't sweat it.

Thanks again,

Richard

• ## Lookup function.

Re: Lookup function.

Any reason you can't use Autofilter?

Richard

• ## if (data+number = number, true, false)

Re: if (data+number = number, true, false)

or

=IF((RIGHT(A1,LEN(A1)-FIND("=",A1))+0)=A2,"correct","wrong")

Richard

• ## compare and copy data

Re: compare and copy data

There seems to be a bit of a contradiction in what you are asking. In the first part, you indicate that if column A and C in the same row are the same, transfer the values from that row, column B, D and E to another sheet. In the example, the matching values are in different rows. Which is it?

Either way, you should be able to use an if A=C then B type formula in your other sheet to bring the desired data across.

Richard

• ## Re-appear after calling other application

Re: Re-appear after calling other application

bump

• ## Re-appear after calling other application

Hi,

I have a very simple application, which has a link to a help file. The trouble is, when the help file is closed (html file), Excel is no longer the active application. I'm not hot on VBA, so some help with the code would be appreciated.

Thanks,

Richard

• ## Autofilter - adding a column range after filter

Re: Autofilter - adding a column range after filter

You shouldn't need to copy to another sheet. If you autofilter and make you're selection, select a cell below the column you want to sum, and click on the autosum button, you'll get a formula like
=SUBTOTAL(9,C6:C50)
which will only sum the visible cells.

Is that any help?

Richard

• ## Copy Worksheet

Re: Copy Worksheet

Not a macro, but a quick way to do it is copy the master, then group those two and copy, then group four and copy etc. After 8 copies, you will be up to 256 sheets, and you'll only need to select 109 of them to copy.

Richard

• ## Chart Legend as Labels

Re: Chart Legend as Labels

I'll give it a go when I get a chance. Thanks in advance for your response.
Richard

• ## minutes * cost/time

Re: minutes * cost/time

Formula is column H needs to include *24. Excel stores times as a proportion of a day, so your 80 hours is 3.333 days, multiplied by 1.5 is 5.

Richard