Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

Excel , puilling infor from several worksheets

I would like to pull information from a series of cells in a row and update a series of cells  in a columns on another worksheet.

See attached
Populate sheet 1, COL B with information from sheet 2 ROW 2

Cheers
Q1-sample-file.xls
0
thebigtable
Asked:
thebigtable
  • 6
  • 5
1 Solution
 
StephenJRCommented:
There are no dates in sheet 2?
0
 
Saqib Husain, SyedEngineerCommented:
Enter this formula in B2 and copy down

=INDIRECT(ADDRESS(2,ROW()-1,,,"Sheet 2"))
0
 
thebigtableAuthor Commented:
What if I don't have a column header?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Saqib Husain, SyedEngineerCommented:
which sheet?
0
 
thebigtableAuthor Commented:
sheet 2
0
 
Saqib Husain, SyedEngineerCommented:
=INDIRECT(ADDRESS(1,ROW()-1,,,"Sheet 2"))
0
 
thebigtableAuthor Commented:
that gave me all "0"s
here is the file again with the change
Indirect.xls
0
 
Saqib Husain, SyedEngineerCommented:
Use the first formula. It is not affected by any data in row 1
0
 
thebigtableAuthor Commented:
=INDIRECT(ADDRESS(2,ROW()-1,,,"Sheet 2"))

This is your formula,
if the ROW number is different than in my sample will I need to change something?
The information on sheet 2 will start at E7
and be put in C5

What am I missing?
0
 
Saqib Husain, SyedEngineerCommented:
2 is the row number. You can change it to any other number. Change the 1 in row()-1 to modify the column.

If your data starts in E7 then use

=INDIRECT(ADDRESS(7,ROW()+3,,,"Sheet 2"))
0
 
Saqib Husain, SyedEngineerCommented:
You can make life simpler by using

=INDIRECT(ADDRESS(ROW('Sheet 2'!$E$7),ROW()+COLUMN('Sheet 2'!$E$7)-2,,,"Sheet 2"))

and only changing the two $E$7 to whatever your first cell is.
0
 
thebigtableAuthor Commented:
I had to change the -2 to a -5 for it to pull from E7

Here is the actual file
-2012-Budget-without-PW.xls
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now