Excel 2007 : Pull data from multiple worksheets to one worksheet

**kristend** · 05-17-2012, 04:31 PM

Hello all -
I need some help which I'm hoping can be done without using a macro. I've attached my spreadsheet (I had to remove my business specific info) but I need some help bad. WHat I'm trying to do is use the front sheet period report for all jobs to pull all info from the next four sheets based on the period number I enter in cell D4. So for instance if I entered 1, I would want it to pull all the data from the four sheets where a 1 is in column B and I want the data to pull into the specific areas based on the job number.

Thank you for any help you can provide!

Kristen

**kristend** · 05-17-2012, 05:05 PM

Or if it needs to be a macro, that's ok, I'm just not very familiar with using them

**FDibbins** · 05-17-2012, 05:12 PM

will your dates all be unique?

**kristend** · 05-17-2012, 05:27 PM

No, they won't unfortunatly.

**pacvek** · 05-17-2012, 05:44 PM

so, you are just needing the totals? to pull onto the first page because if that's the case then you may just want to put all your info for job 1 onto one tab, second tab all infor for job 2 and so on, that way whenever you just type in the number 1 in cell D4 on the first spreadsheet it will pull all the info from the tab that has all the info for job 1, for instance if you are an employer and you are wanting to know just about anything about your employees, you will obviously asign an employee number, when you type in the employee number on a specific cell, then all the information about that employee will populate onto the designated fields on the first spreadsheet, then you can change the employee number to another one and that info will pupulate again, correct? if that is the case then there are several solutions.

**kristend** · 05-17-2012, 05:52 PM

What I need is to be able to go into the spreadsheet at any time and put in the period number and it pull all the data for all jobs (the four on this spreadsheet) to that current tab. Because that data changes based on what is spent and when it is paid. So for instance I may go in and look at period 1 and then go in and look at period 3. The problem with copying it to another tab is that when ever it's updated it has to be updated in 2 places, I just want to be able to pull the most current information by period.

**pacvek** · 05-17-2012, 06:29 PM

try this formula, i think this is what you need. =CHOOSE(D4,'Job 1'!E13,'Job 1'!E16,'Job 1'!E19)

**FDibbins** · 05-17-2012, 06:42 PM

1 of the problems i see with achieving what you want, is that your dates are not all in the same formats. some dates look to be actual dates (1/4/2011) but others are date ranges (11/16-11/19) and while these may look like dates, they are actually text, and not values (dates are values). so no matter what date you enter, unless those date ranges are changed (or a complex formula used) they will never be included in cany calc

edit: oh, and the "merge and center" doesnt help either lol...its always better to use "format/alignment/center across selection"