Excel vlookup function with complex copy and paste
 

Hello,
I have a spreadhseet with listings of all 50 states and its
populations and than I have a 2nd sheet that has each of these states
listed but seperated by 6 rows. What I would like to do is use
vlookup to bring in the populations from sheet 1 to sheet2 but paste
them seperated by 6 rows. For example.
Sheet1:
State Populations
Alabama 1 mil
Alaska 80,000
Arizona 10 mil

Sheet2:
State Populations
Alabama 1 mil
-
-
-
-
-
-
Alaska 80,000
-
-
-
-
-
-
Arizona 10 mil

thanks!

Excel vlookup function with complex copy and paste
 

Type your VLOOKUP formula into the cell next to Alabama
Let's say this is B2
Select and copy that cell and the next 6 empty cells
In the name box type B8:B351 and press enter (this selects B8:B351)
Use CTRL+V to paste

I get 351 from (50 states times 7 cells/state) plus 1 since we began in B2
not B1
Adjust if you have Wash Dc as a 'state'
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

wrote in message
...
Hello,
I have a spreadhseet with listings of all 50 states and its
populations and than I have a 2nd sheet that has each of these states
listed but seperated by 6 rows. What I would like to do is use
vlookup to bring in the populations from sheet 1 to sheet2 but paste
them seperated by 6 rows. For example.
Sheet1:
State Populations
Alabama 1 mil
Alaska 80,000
Arizona 10 mil

Sheet2:
State Populations
Alabama 1 mil
-
-
-
-
-
-
Alaska 80,000
-
-
-
-
-
-
Arizona 10 mil

thanks!

Excel vlookup function with complex copy and paste
 

On Nov 6, 4:01*pm, "Bernard Liengme"
wrote:
Type your VLOOKUP formula into the cell next to Alabama
Let's say this is B2
Select and copy that cell and the next 6 empty cells
In the name box type B8:B351 and press enter (this selects B8:B351)
Use CTRL+V to paste

I get 351 from (50 states times 7 cells/state) plus 1 since we began in B2
not B1
Adjust if you have Wash Dc as a 'state'
best wishes
--
Bernard V Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme
remove caps from email

wrote in message

...



Hello,
I have a spreadhseet with listings of all 50 states and its
populations and than I have a 2nd sheet that has each of these states
listed but seperated by 6 rows. *What I would like to do is use
vlookup to bring in the populations from sheet 1 to sheet2 but paste
them seperated by 6 rows. *For example.
Sheet1:
State * * * * * Populations
Alabama * * *1 mil
Alaska * * * * 80,000
Arizona * * * *10 mil

Sheet2:
State * * * * *Populations
Alabama * * 1 mil
-
-
-
-
-
-
Alaska * * *80,000
-
-
-
-
-
-
Arizona * * 10 mil

thanks!- Hide quoted text -

- Show quoted text -

I was looking more for a VBA code, but I did try your suggestions. I
was confused with the name box that you mentioned, is that the one on
the Insert function where you could name a range? thanks!