Excel vlookup function with complex copy and paste

[matrix7410]

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!

 

[Bernard Liengme]

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

 

[matrix7410]

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








- 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!