VLOOKUP's

[Aikisteve]

I Have a spread sheet set up whereby i have vlookups and if functions
(tied to hyperlinks). These all work fine and dany, BUT they have to
be tied to a certain cell (don't they?) i.e. a3, b3, c3 etc. My
problem is that this spreadsheet could run into the 1,000's is there
any way to avoind having to copy this onto every line?

Also, is there a way to generate sequential numbers that can be split.
For example if I wanted

Red, 3, Cars = 0001 then if it happenes again =0002 etc?

but if

Red, _2_, cars = 0001 (not 0003)

Is there any way to do this?

 

[Barb Reinhardt]

I'm not clear on how you want to increment your numbers for RED and CARS.
Are they incremented separately or pairwise?

Barb Reinhardt

 

[Aikisteve]

Hi Barb

Im a bit of an excel newbie so im not quite sure if i fully follow wha
you mean.

I have VLOOKUP functions set to give me the first sections of the fil
numbers.

For example if I have a red car bought in Newcastle

Red=01
Car=03
Newcastle=04

therefore, so far my file number is 010304, all of the functions i hav
set up for that work fine. but i now want a function that generates a
digit number to go onto the end of that so, at a glance, i can see ho
many red cars have been bought in Newcastle e.g the first one get
assigned 0001, the second 0002 etc.

but

my problem comes when it is a greeen car bought in newcastle, or a re
bike bought in swansea etc. so a normal incremental number system won
work, as it would only tell me how many files had been created, not ho
many of a certain kind.

Hope this makes sense. thanks for the help

 

[Barb Reinhardt]

Let's assume that you have data in the following columns.
A B C
Red Car Newcastle

Let's also assume that the first row of data is ROW 2. Put this in D2 and
copy down.

=SUMPRODUCT(--(A$2:A2=A2),--(B$2:B2=B2),--(C$2:C2=C2))

HTH,
Barb Reinhardt

 

[Aikisteve]

Hi Barb.

That works brilliantly, thanks mate.

Steve