VLOOKUP's

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?

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" wrote:

>
> 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?
>
>
> --
> Aikisteve
> ------------------------------------------------------------------------
> Aikisteve's Profile: http://www.excelforum.com/member.php...o&userid=36111
> View this thread: http://www.excelforum.com/showthread...hreadid=565964
>
>

Hi Barb

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

I have VLOOKUP functions set to give me the first sections of the file 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 have set up for that work fine. but i now want a function that generates a 4 digit number to go onto the end of that so, at a glance, i can see how many red cars have been bought in Newcastle e.g the first one gets assigned 0001, the second 0002 etc.

[B]but[B]

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

Hope this makes sense. thanks for the help.

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" wrote:

>
> Hi Barb
>
> Im a bit of an excel newbie so im not quite sure if i fully follow what
> you mean.
>
> I have VLOOKUP functions set to give me the first sections of the file
> 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 have
> set up for that work fine. but i now want a function that generates a 4
> digit number to go onto the end of that so, at a glance, i can see how
> many red cars have been bought in Newcastle e.g the first one gets
> assigned 0001, the second 0002 etc.
>
> [B]but[B]
>
> my problem comes when it is a greeen car bought in newcastle, or a red
> bike bought in swansea etc. so a normal incremental number system wont
> work, as it would only tell me how many files had been created, not how
> many of a certain kind.
>
> Hope this makes sense. thanks for the help.
>
>
> --
> Aikisteve
> ------------------------------------------------------------------------
> Aikisteve's Profile: http://www.excelforum.com/member.php...o&userid=36111
> View this thread: http://www.excelforum.com/showthread...hreadid=565964
>
>

Hi Barb.

That works brilliantly, thanks mate.

Steve