Technical Discussion
  >> Windows Issues


Register (or login) on our website and you will not see this ad.


Pages in this thread: 1 | 2 | (show all)   Print Thread
Standard User DavidFinbarr
(freechataholic) Sun 12-Feb-12 12:24:41
Print Post

Excel help needed.


[link to this post]
 
On sheet 2 I have two columns that contain numbers.
EG A1 95 .... B1 1318 ..... A2 94.5 ..... B2 1313 ..... and so on.

On sheet 1 I want to type in a number into A1. In B1 I want it to find that number in sheet 2 column A and
give the number that is in column B (same cell number)

So if I type in sheet 1 A1 94.5 the result will be 1313 in sheet 1 B1

A bottle of white, a bottle of red
Perhaps a bottle of rosé instead.
Standard User camieabz
(sensei) Sun 12-Feb-12 12:45:51
Print Post

Re: Excel help needed.


[re: DavidFinbarr] [link to this post]
 
Code for sheet 1, B1, assuming your range of sheet 2 data is A1 to B4 (increase the range to suit).

=VLOOKUP(A1,Sheet2!A1:B4,2,FALSE)

~~~~~~~~~~


© Camieabz 2002-2012

All Connection Data ~ plusnet

Scottish Labour politician: “The SNP are on a very dangerous tack. What they are doing is trying to build up a situation in Scotland where the services are manifestly better than south of the border in a number of areas.”

Interviewer: ”Is that a bad thing?”

Scottish Labour politician: “No, but they are doing it deliberately.”
Standard User DavidFinbarr
(freechataholic) Sun 12-Feb-12 13:03:52
Print Post

Re: Excel help needed.


[re: camieabz] [link to this post]
 
Thanks Camie smile

How would I do it if instead of 2 long column I had 8 shorter ones in sheet 2

eg instead of A1 to 100 & B1 to B100
I have A1 to A25 B1 to B25, C1 to C25 D1 to D25 etc

A bottle of white, a bottle of red
Perhaps a bottle of rosé instead.


Register (or login) on our website and you will not see this ad.

Standard User camieabz
(sensei) Sun 12-Feb-12 14:01:24
Print Post

Re: Excel help needed.


[re: DavidFinbarr] [link to this post]
 
Break up the formula I gave you.

=VLOOKUP(A1,Sheet2!A1:B4,2,FALSE)

=VLOOKUP(source data cell,data range,column number,FALSE)

The 'FALSE' condition sets it to look for exactly that value. Bear in mind that if there are duplicate values in the first column, Excel will return the first result, e.g. using '4' as the search value:

1
2
3
4 (this is the one returned, and the others are ignored)
4
4
5

That aside if you're looking at A1 to D25, your code would be:

For cell B1 (first sheet):

=VLOOKUP(A1,Sheet2!A1:D25,2,FALSE)

cell C1:

=VLOOKUP(A1,Sheet2!A1:D25,3,FALSE)

cell D1:

=VLOOKUP(A1,Sheet2!A1:D25,4,FALSE)

~~~~~~~~~~


© Camieabz 2002-2012

All Connection Data ~ plusnet

Scottish Labour politician: “The SNP are on a very dangerous tack. What they are doing is trying to build up a situation in Scotland where the services are manifestly better than south of the border in a number of areas.”

Interviewer: ”Is that a bad thing?”

Scottish Labour politician: “No, but they are doing it deliberately.”

Edited by camieabz (Sun 12-Feb-12 14:02:36)

Standard User camieabz
(sensei) Sun 12-Feb-12 14:27:09
Print Post

Re: Excel help needed.


[re: DavidFinbarr] [link to this post]
 
Here's a better one:

http://www.camieabz.co.uk/vlookup.xls

Sheet 1 is the 'search' sheet, where you pop the year (note the sheet names), and the week number in. It will return the sales for Tom, Dick and Harry and then sum them (just for fun).

~~~~~~~~~~


© Camieabz 2002-2012

All Connection Data ~ plusnet

Scottish Labour politician: “The SNP are on a very dangerous tack. What they are doing is trying to build up a situation in Scotland where the services are manifestly better than south of the border in a number of areas.”

Interviewer: ”Is that a bad thing?”

Scottish Labour politician: “No, but they are doing it deliberately.”
Standard User DavidFinbarr
(freechataholic) Sun 12-Feb-12 14:51:13
Print Post

Re: Excel help needed.


[re: camieabz] [link to this post]
 
Not to sure I was clear. No new information is added, the information is just moved.

I only want to use cells A1 and B1 in sheet 1
In sheet 2 the information runs from A1 to A100 & B1 to B100

I want to have sheet 2 only going down to row 25, so want to move what is in Cells A26 / B26 down to cell A50/ B50 to cells C1/D1 to C25/ D25 and so on.

I want the result to be the same having a single column

A bottle of white, a bottle of red
Perhaps a bottle of rosé instead.

Edited by DavidFinbarr (Sun 12-Feb-12 14:51:42)

Standard User camieabz
(sensei) Sun 12-Feb-12 17:50:15
Print Post

Re: Excel help needed.


[re: DavidFinbarr] [link to this post]
 
Hi David.

Was just giving an example workbook with some VLOOKUPs included, showing how it can be done. Just adapt some of the VLOOKUPs. The INDIRECTS are for if you want to specify different sheets.

~~~~~~~~~~


© Camieabz 2002-2012

All Connection Data ~ plusnet

Scottish Labour politician: “The SNP are on a very dangerous tack. What they are doing is trying to build up a situation in Scotland where the services are manifestly better than south of the border in a number of areas.”

Interviewer: ”Is that a bad thing?”

Scottish Labour politician: “No, but they are doing it deliberately.”
Standard User DavidFinbarr
(freechataholic) Sun 12-Feb-12 17:58:58
Print Post

Re: Excel help needed.


[re: camieabz] [link to this post]
 
Cheers Camie,
will have a play around later .

A bottle of white, a bottle of red
Perhaps a bottle of rosé instead.
Standard User XRaySpeX
(eat-sleep-adslguide) Sun 12-Feb-12 18:22:04
Print Post

Re: Excel help needed.


[re: camieabz] [link to this post]
 
No, for some reason that doesn't appear necessary, the OP is trying to break up his 100-long single column-pair into 4 x 25 long logically contiguous column-pairs but still get the same result.

This would seem to over-complicate the problem for no advantage but to make the sheet more "square".

1999: Freeserve 48K Dial-Up => 2005: Wanadoo 1 Meg BB => 2007: Orange 2 Meg BB => 2008: Orange 8 Meg LLU => 2010: Orange 16 Meg LLU => 2011: Orange 19 Meg WBC
Standard User camieabz
(sensei) Sun 12-Feb-12 18:22:51
Print Post

Re: Excel help needed.


[re: XRaySpeX] [link to this post]
 
So post up the perfect solution.

~~~~~~~~~~


© Camieabz 2002-2012

All Connection Data ~ plusnet

Scottish Labour politician: “The SNP are on a very dangerous tack. What they are doing is trying to build up a situation in Scotland where the services are manifestly better than south of the border in a number of areas.”

Interviewer: ”Is that a bad thing?”

Scottish Labour politician: “No, but they are doing it deliberately.”
Pages in this thread: 1 | 2 | (show all)   Print Thread

Jump to