Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Eelco Wiertsema
 
Posts: n/a
Default Vlookup with 2 ranges in one worksheet

Ok what I need to do is to get the number from a certain cell with all kinds
of data (Stocks) into a worksheet in wich I can modify my investments.
Now the problem is that I get the data in the following order:

DATE COMPANY HIGH LOW VOLUME
x x x x x

What I would like now is that as I will have to add more data that the
'HIGH' and 'LOW' will be copied within a formula to another worksheet in
which the Date can be found on the different rows and all the companies are
filled in the columns now there are 24 different companies I have to aquire
the stockindexes from.

Will this be possible? What I can do is to get all the first stock indexes
correctly but as I try to autofill all the dates it doesn't seem to work with
only just the VLOOKUP formula!

Any help will be greatly appreciated.

  #2   Report Post  
Biff
 
Posts: n/a
Default

Hi!

Can't really tell what it is you want.

Can you provide more detailed info? Be very specific.

Biff

-----Original Message-----
Ok what I need to do is to get the number from a certain

cell with all kinds
of data (Stocks) into a worksheet in wich I can modify my

investments.
Now the problem is that I get the data in the following

order:

DATE COMPANY HIGH LOW VOLUME
x x x x x

What I would like now is that as I will have to add more

data that the
'HIGH' and 'LOW' will be copied within a formula to

another worksheet in
which the Date can be found on the different rows and all

the companies are
filled in the columns now there are 24 different

companies I have to aquire
the stockindexes from.

Will this be possible? What I can do is to get all the

first stock indexes
correctly but as I try to autofill all the dates it

doesn't seem to work with
only just the VLOOKUP formula!

Any help will be greatly appreciated.

.

  #3   Report Post  
Eelco Wiertsema
 
Posts: n/a
Default

Date Company Name Mnemonic opening
3-1-2005 ABN AMRO HOLDINGAABA 19,53
3-1-2005 AEGON AGN 10,09
3-1-2005 AHOLD KON AH 5,67
3-1-2005 AKZO NOBEL AKZA 31,25
3-1-2005 ASML HOLDING ASML 11,76
3-1-2005 BUHRMANN BUHR 7,2
3-1-2005 DSM DSM 47,51
3-1-2005 ELSEVIER REN 10,04
3-1-2005 FORTIS FORA 20,44
3-1-2005 GETRONICS GTN 1,68
3-1-2005 HAGEMEYER HGM 1,72
3-1-2005 HEINEKEN HEIA 24,55
3-1-2005 IHC CALAND IHC 46,65

So this is my Data input as I can download it from the server what I would
like now is that this number you can see under opening is being looked up in
another worksheet.
But now the A column states all the different dates and the first row states
the different companies so that i don't have to copy everything by hand.
As you can see the date here is only the 3rd of january but in the complete
worksheet this must accumulate to a whole year as time goes by and I can
download the data weekly.

Like this:

AABA

Date Open End
3-01-05 ぎ 19,53 ぎ 19,70
4-01-05 ぎ 19,65 ぎ 19,82
5-01-05 ぎ 19,66 ぎ 19,76
6-01-05 ぎ 19,80 ぎ 20,48
7-01-05 ぎ 20,46 ぎ 20,65
10-01-05 ぎ 20,56 ぎ 20,58
11-01-05 ぎ 20,52 ぎ 20,28
12-01-05 ぎ 20,24 ぎ 20,06
13-01-05 ぎ 20,14 ぎ 20,09
14-01-05 ぎ 19,98 ぎ 20,11
17-01-05 ぎ 20,17 ぎ 20,27
18-01-05 ぎ 20,51 ぎ 20,56
19-01-05 ぎ 20,64 ぎ 20,69
20-01-05 ぎ 20,62 ぎ 20,61
21-01-05 ぎ 20,53 ぎ 20,73
24-01-05 ぎ 20,66 ぎ 20,64
25-01-05 ぎ 20,72 ぎ 20,84

And then if this would be columns A through C but in the complete worksheet
it goes through to AQ

Again is this possible?



"Biff" wrote:

Hi!

Can't really tell what it is you want.

Can you provide more detailed info? Be very specific.

Biff

-----Original Message-----
Ok what I need to do is to get the number from a certain

cell with all kinds
of data (Stocks) into a worksheet in wich I can modify my

investments.
Now the problem is that I get the data in the following

order:

DATE COMPANY HIGH LOW VOLUME
x x x x x

What I would like now is that as I will have to add more

data that the
'HIGH' and 'LOW' will be copied within a formula to

another worksheet in
which the Date can be found on the different rows and all

the companies are
filled in the columns now there are 24 different

companies I have to aquire
the stockindexes from.

Will this be possible? What I can do is to get all the

first stock indexes
correctly but as I try to autofill all the dates it

doesn't seem to work with
only just the VLOOKUP formula!

Any help will be greatly appreciated.

.


  #4   Report Post  
Biff
 
Posts: n/a
Default

Hi!

This should be pretty easy but there are a couple of=20
things that I don't quite understand.

Do you download the raw data daily? And if so, after you=20
have that data sorted out to the other sheet do you delete=20
the raw data?

Where do you get the closing values?=20

I can think of a few different ways to do this but at the=20
same time it sounds like this file is very big and would=20
require a lot of formulas.

Have you considered using a pivot table or even a database=20
program?

To come up with a specific solution I would need to see=20
the file.

Biff

-----Original Message-----
Date Company Name Mnemonic opening
3-1-2005 ABN AMRO HOLDINGAABA 19,53
3-1-2005 AEGON AGN 10,09
3-1-2005 AHOLD KON AH 5,67
3-1-2005 AKZO NOBEL AKZA 31,25
3-1-2005 ASML HOLDING ASML 11,76
3-1-2005 BUHRMANN BUHR 7,2
3-1-2005 DSM DSM 47,51
3-1-2005 ELSEVIER REN 10,04
3-1-2005 FORTIS FORA 20,44
3-1-2005 GETRONICS GTN 1,68
3-1-2005 HAGEMEYER HGM 1,72
3-1-2005 HEINEKEN HEIA 24,55
3-1-2005 IHC CALAND IHC 46,65

So this is my Data input as I can download it from the=20

server what I would=20
like now is that this number you can see under opening is=20

being looked up in=20
another worksheet.
But now the A column states all the different dates and=20

the first row states=20
the different companies so that i don't have to copy=20

everything by hand.
As you can see the date here is only the 3rd of january=20

but in the complete=20
worksheet this must accumulate to a whole year as time=20

goes by and I can=20
download the data weekly.

Like this:

AABA=09
=09
Date Open End
3-01-05 =E2,=AC 19,53 =E2,=AC 19,70=20
4-01-05 =E2,=AC 19,65 =E2,=AC 19,82=20
5-01-05 =E2,=AC 19,66 =E2,=AC 19,76=20
6-01-05 =E2,=AC 19,80 =E2,=AC 20,48=20
7-01-05 =E2,=AC 20,46 =E2,=AC 20,65=20
10-01-05 =E2,=AC 20,56 =E2,=AC 20,58=20
11-01-05 =E2,=AC 20,52 =E2,=AC 20,28=20
12-01-05 =E2,=AC 20,24 =E2,=AC 20,06=20
13-01-05 =E2,=AC 20,14 =E2,=AC 20,09=20
14-01-05 =E2,=AC 19,98 =E2,=AC 20,11=20
17-01-05 =E2,=AC 20,17 =E2,=AC 20,27=20
18-01-05 =E2,=AC 20,51 =E2,=AC 20,56=20
19-01-05 =E2,=AC 20,64 =E2,=AC 20,69=20
20-01-05 =E2,=AC 20,62 =E2,=AC 20,61=20
21-01-05 =E2,=AC 20,53 =E2,=AC 20,73=20
24-01-05 =E2,=AC 20,66 =E2,=AC 20,64=20
25-01-05 =E2,=AC 20,72 =E2,=AC 20,84=20

And then if this would be columns A through C but in the=20

complete worksheet=20
it goes through to AQ

Again is this possible?



"Biff" wrote:

Hi!
=20
Can't really tell what it is you want.
=20
Can you provide more detailed info? Be very specific.
=20
Biff
=20
-----Original Message-----
Ok what I need to do is to get the number from a=20

certain=20
cell with all kinds=20
of data (Stocks) into a worksheet in wich I can modify=20

my=20
investments.
Now the problem is that I get the data in the=20

following=20
order:

DATE COMPANY HIGH LOW VOLUME
x x x x x =20

What I would like now is that as I will have to add=20

more=20
data that the=20
'HIGH' and 'LOW' will be copied within a formula to=20

another worksheet in=20
which the Date can be found on the different rows and=20

all=20
the companies are=20
filled in the columns now there are 24 different=20

companies I have to aquire=20
the stockindexes from.

Will this be possible? What I can do is to get all the=20

first stock indexes=20
correctly but as I try to autofill all the dates it=20

doesn't seem to work with=20
only just the VLOOKUP formula!

Any help will be greatly appreciated.

.

=20

.

  #5   Report Post  
Eelco Wiertsema
 
Posts: n/a
Default

To your first question yes but sometimes I've been away for a while and then
I download a lot of info at the same time.
then no I do not delete the raw data but it's being copied into the sheet
aswell.
The closing values I would like to treat the same.

All of this is for my own convenience so that the worksheet will do all the
work it self and I don't have to copy and paste every day.

I would like to send you the file but I don't know how to do it here! Any
suggestions?

"Biff" wrote:

Hi!

This should be pretty easy but there are a couple of
things that I don't quite understand.

Do you download the raw data daily? And if so, after you
have that data sorted out to the other sheet do you delete
the raw data?

Where do you get the closing values?

I can think of a few different ways to do this but at the
same time it sounds like this file is very big and would
require a lot of formulas.

Have you considered using a pivot table or even a database
program?

To come up with a specific solution I would need to see
the file.

Biff

-----Original Message-----
Date Company Name Mnemonic opening
3-1-2005 ABN AMRO HOLDINGAABA 19,53
3-1-2005 AEGON AGN 10,09
3-1-2005 AHOLD KON AH 5,67
3-1-2005 AKZO NOBEL AKZA 31,25
3-1-2005 ASML HOLDING ASML 11,76
3-1-2005 BUHRMANN BUHR 7,2
3-1-2005 DSM DSM 47,51
3-1-2005 ELSEVIER REN 10,04
3-1-2005 FORTIS FORA 20,44
3-1-2005 GETRONICS GTN 1,68
3-1-2005 HAGEMEYER HGM 1,72
3-1-2005 HEINEKEN HEIA 24,55
3-1-2005 IHC CALAND IHC 46,65

So this is my Data input as I can download it from the

server what I would
like now is that this number you can see under opening is

being looked up in
another worksheet.
But now the A column states all the different dates and

the first row states
the different companies so that i don't have to copy

everything by hand.
As you can see the date here is only the 3rd of january

but in the complete
worksheet this must accumulate to a whole year as time

goes by and I can
download the data weekly.

Like this:

AABA

Date Open End
3-01-05 テ「,ツャ 19,53 テ「,ツャ 19,70
4-01-05 テ「,ツャ 19,65 テ「,ツャ 19,82
5-01-05 テ「,ツャ 19,66 テ「,ツャ 19,76
6-01-05 テ「,ツャ 19,80 テ「,ツャ 20,48
7-01-05 テ「,ツャ 20,46 テ「,ツャ 20,65
10-01-05 テ「,ツャ 20,56 テ「,ツャ 20,58
11-01-05 テ「,ツャ 20,52 テ「,ツャ 20,28
12-01-05 テ「,ツャ 20,24 テ「,ツャ 20,06
13-01-05 テ「,ツャ 20,14 テ「,ツャ 20,09
14-01-05 テ「,ツャ 19,98 テ「,ツャ 20,11
17-01-05 テ「,ツャ 20,17 テ「,ツャ 20,27
18-01-05 テ「,ツャ 20,51 テ「,ツャ 20,56
19-01-05 テ「,ツャ 20,64 テ「,ツャ 20,69
20-01-05 テ「,ツャ 20,62 テ「,ツャ 20,61
21-01-05 テ「,ツャ 20,53 テ「,ツャ 20,73
24-01-05 テ「,ツャ 20,66 テ「,ツャ 20,64
25-01-05 テ「,ツャ 20,72 テ「,ツャ 20,84

And then if this would be columns A through C but in the

complete worksheet
it goes through to AQ

Again is this possible?



"Biff" wrote:

Hi!

Can't really tell what it is you want.

Can you provide more detailed info? Be very specific.

Biff

-----Original Message-----
Ok what I need to do is to get the number from a

certain
cell with all kinds
of data (Stocks) into a worksheet in wich I can modify

my
investments.
Now the problem is that I get the data in the

following
order:

DATE COMPANY HIGH LOW VOLUME
x x x x x

What I would like now is that as I will have to add

more
data that the
'HIGH' and 'LOW' will be copied within a formula to
another worksheet in
which the Date can be found on the different rows and

all
the companies are
filled in the columns now there are 24 different
companies I have to aquire
the stockindexes from.

Will this be possible? What I can do is to get all the
first stock indexes
correctly but as I try to autofill all the dates it
doesn't seem to work with
only just the VLOOKUP formula!

Any help will be greatly appreciated.

.


.




  #6   Report Post  
Biff
 
Posts: n/a
Default

Hi!

If the file is under 1 mb in size send it to:

Biff, is valko01 at comcast dot net.

If it's over 1 mb:

Goto this site and upload the file:

http://s30.yousendit.com/

Just follow the directions. It's very easy.

Biff

-----Original Message-----
To your first question yes but sometimes I've been away=20

for a while and then=20
I download a lot of info at the same time.
then no I do not delete the raw data but it's being=20

copied into the sheet=20
aswell.
The closing values I would like to treat the same.

All of this is for my own convenience so that the=20

worksheet will do all the=20
work it self and I don't have to copy and paste every day.

I would like to send you the file but I don't know how to=20

do it here! Any=20
suggestions?

"Biff" wrote:

Hi!
=20
This should be pretty easy but there are a couple of=20
things that I don't quite understand.
=20
Do you download the raw data daily? And if so, after=20

you=20
have that data sorted out to the other sheet do you=20

delete=20
the raw data?
=20
Where do you get the closing values?=20
=20
I can think of a few different ways to do this but at=20

the=20
same time it sounds like this file is very big and=20

would=20
require a lot of formulas.
=20
Have you considered using a pivot table or even a=20

database=20
program?
=20
To come up with a specific solution I would need to see=20
the file.
=20
Biff
=20
-----Original Message-----
Date Company Name Mnemonic opening
3-1-2005 ABN AMRO HOLDINGAABA 19,53
3-1-2005 AEGON AGN 10,09
3-1-2005 AHOLD KON AH 5,67
3-1-2005 AKZO NOBEL AKZA 31,25
3-1-2005 ASML HOLDING ASML 11,76
3-1-2005 BUHRMANN BUHR 7,2
3-1-2005 DSM DSM 47,51
3-1-2005 ELSEVIER REN 10,04
3-1-2005 FORTIS FORA 20,44
3-1-2005 GETRONICS GTN 1,68
3-1-2005 HAGEMEYER HGM 1,72
3-1-2005 HEINEKEN HEIA 24,55
3-1-2005 IHC CALAND IHC 46,65

So this is my Data input as I can download it from the=20

server what I would=20
like now is that this number you can see under opening=20

is=20
being looked up in=20
another worksheet.
But now the A column states all the different dates=20

and=20
the first row states=20
the different companies so that i don't have to copy=20

everything by hand.
As you can see the date here is only the 3rd of=20

january=20
but in the complete=20
worksheet this must accumulate to a whole year as time=20

goes by and I can=20
download the data weekly.

Like this:

AABA=09
=09
Date Open End
3-01-05 =C3=A2,=C2=AC 19,53 =C3=A2,=C2=AC 19,70=20
4-01-05 =C3=A2,=C2=AC 19,65 =C3=A2,=C2=AC 19,82=20
5-01-05 =C3=A2,=C2=AC 19,66 =C3=A2,=C2=AC 19,76=20
6-01-05 =C3=A2,=C2=AC 19,80 =C3=A2,=C2=AC 20,48=20
7-01-05 =C3=A2,=C2=AC 20,46 =C3=A2,=C2=AC 20,65=20
10-01-05 =C3=A2,=C2=AC 20,56 =C3=A2,=C2=AC 20,58=20
11-01-05 =C3=A2,=C2=AC 20,52 =C3=A2,=C2=AC 20,28=20
12-01-05 =C3=A2,=C2=AC 20,24 =C3=A2,=C2=AC 20,06=20
13-01-05 =C3=A2,=C2=AC 20,14 =C3=A2,=C2=AC 20,09=20
14-01-05 =C3=A2,=C2=AC 19,98 =C3=A2,=C2=AC 20,11=20
17-01-05 =C3=A2,=C2=AC 20,17 =C3=A2,=C2=AC 20,27=20
18-01-05 =C3=A2,=C2=AC 20,51 =C3=A2,=C2=AC 20,56=20
19-01-05 =C3=A2,=C2=AC 20,64 =C3=A2,=C2=AC 20,69=20
20-01-05 =C3=A2,=C2=AC 20,62 =C3=A2,=C2=AC 20,61=20
21-01-05 =C3=A2,=C2=AC 20,53 =C3=A2,=C2=AC 20,73=20
24-01-05 =C3=A2,=C2=AC 20,66 =C3=A2,=C2=AC 20,64=20
25-01-05 =C3=A2,=C2=AC 20,72 =C3=A2,=C2=AC 20,84=20

And then if this would be columns A through C but in=20

the=20
complete worksheet=20
it goes through to AQ

Again is this possible?



"Biff" wrote:

Hi!
=20
Can't really tell what it is you want.
=20
Can you provide more detailed info? Be very specific.
=20
Biff
=20
-----Original Message-----
Ok what I need to do is to get the number from a=20

certain=20
cell with all kinds=20
of data (Stocks) into a worksheet in wich I can=20

modify=20
my=20
investments.
Now the problem is that I get the data in the=20

following=20
order:

DATE COMPANY HIGH LOW VOLUME
x x x x =20

x =20

What I would like now is that as I will have to add=20

more=20
data that the=20
'HIGH' and 'LOW' will be copied within a formula to=20
another worksheet in=20
which the Date can be found on the different rows=20

and=20
all=20
the companies are=20
filled in the columns now there are 24 different=20
companies I have to aquire=20
the stockindexes from.

Will this be possible? What I can do is to get all=20

the=20
first stock indexes=20
correctly but as I try to autofill all the dates it=20
doesn't seem to work with=20
only just the VLOOKUP formula!

Any help will be greatly appreciated.

.

=20
.

=20

.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to use a cell value as Table Array in VLOOKUP worksheet function willydlish Excel Discussion (Misc queries) 2 February 16th 05 03:47 AM
vlookup data hidden within worksheet Excel Worksheet Functions 0 January 26th 05 01:09 PM
vlookup and named ranges chathag Excel Worksheet Functions 3 November 22nd 04 05:07 PM
how do I name ranges in an Excel worksheet becca122121 Excel Worksheet Functions 4 November 8th 04 07:14 PM
Excel 2003 - referenceing the active worksheet page in vlookup Ernst Excel Worksheet Functions 4 November 7th 04 12:23 AM


All times are GMT +1. The time now is 02:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ゥ2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"