To Bob Philips & Ken - Dynamic Range

  • Thread starter Thread starter hce
  • Start date Start date
H

hce

Dear Guys

Thanks for your help... but I'm still confused on how I can apply the
formula to my IF formula... Can you teach me how to do it? For example,
Column A (Phone) has 10 records so what vb code do I write in the
module (macro) to tell it to apply the IF formula in Column B and only
for that 10 records? And like what I mentioned before, the number of
records will change from time to time. Currently, I am writing in the
macro to apply the formula from B:B.

Cheers
Kelvin
 
Best if you keep to the original thread, it gets confusing otherwise

Firstly you don't need VBA.

Let's start with a few assumptions
- the id is in column B in APN
- the id is in column A in TOTAL
- the phone number is in column A in APN (stated)
- the phone number is to go in column B in TOTAL
- the data starts in row 1 on both sheets

VLOOKUP will not work as the phone number comes before the id, so we will
need INDEX and MATCH

The basic formula is, in B1 on TOTAL

=INDEX(APN!$A$1:$A$10,MATCH($A1,APN!$B$1:$B$10,0))

To make it dynamic, we then use

=INDEX(OFFSET(APN!$A$1,0,0,COUNTA(APN!A:A),1),MATCH($A1,OFFSET(APN!$B$1,0,0,
COUNTA(APN!B:B),1),0))

and just copy down the rows

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
If you want to find duplicates, you can use

=countif(APN!$A:$A,A1) in sheet total, column b

or
=if(countif(APN!$A:$A,A1)>1,"Dups","OK")



Countif restricts the area it looks at to the Usedrange, so it would be just
as fast as using a dynamic range.
 
Back
Top