Excel 2007 : Need help pulling info from several tabs into one final tab

**Pokey** · 06-25-2012, 04:19 PM

I have a tab for each day of the week and a summary tab. I need all the info from day 1 thru day 7 to roll up into the summary tab. I'm trying to eliminate any empty lines on the summary tab so it only contains information for the week. Is there a formula that will check the appropriate cells on day 1 and enter the info into the summary sheet then when the cells are empty move on to day 2, etc., etc? There is a possibility for as many as 52 entries per day or as few as 0 on some days.

I have uploaded an example. Thanks so much for your input and assistance.

Pokey

**dip11** · 06-25-2012, 05:19 PM

If I read what you wrote correctly, you want blanks to appear as blank instead of 0?

In that case close your IF clauses FALSE statements to "".

If you also need help with the pulling of data in general, I recommend reading my thread here:
http://www.excelforum.com/excel-2007...uplicates.html

Marcol posted an excellent solution to the problem I had, which is easily adapted to yours.

**Pokey** · 06-26-2012, 09:05 AM

Thanks for the info. I'm trying to decipher it now. I'm just a novice really and even though I know what I would like to happen, I'm not even sure it can happen.

As for the blanks - On the summary tab the only time there should be blanks is where there is no more information to transfer from previous tabs and yes those lines should be blank. As for the rest, any time there is information on any given tab, it should be transferred to the summary tab. For example: Tab 1 has 2 shipments so the first two lines on the summary tab should contain that information. Tab 2 has 3 shipments so lines 3-5 on the summary tab should contain this information. Tab 3 has no shipments because it's a holiday and Tab 4 has 5 shipments. So line 6 of the summary tab should contain the 1st shipment information from Tab 4, line 7 should be the second shipment information, etc. Basically, the formula should recognize when there is no order # and "look" at the next tab and transfer that info on the next available line.

Thanks,
Pokey

**Pokey** · 07-05-2012, 11:13 AM

I'm still having trouble with this. I guess I didn't explain correctly. I have a tab for each day of the week and a summary tab. Right now we are not working on Saturdays or Sundays. However, during Peak Season we will be. I need my spreadsheet to do the following: If there is information entered on Sunday then that information needs to be automatically inserted on the Summary Tab. So if 5 lines are completed on the Sunday Tab, then the first 5 lines on the Summary Tab need to be that exact same information as on the Sunday Tab. Then whatever information is entered for Monday should appear on the Summary Tab beginning on line 6. If there is no information on Sunday, then the Monday Tab information should transfer to line 1 on the Summary Tab. Any day could be blank due to a holiday, so on the Summary Tab, the next day's information should fill in in a manner that would not leave any blank lines.

Thanks,

**MarvinP** · 07-05-2012, 11:37 AM

Hi Pokey,

I see what you have and what you want. My question is, why don't you enter your data on the Weekly sheet only. Also, you don't need to do this weekly as you could do a monthly or yearly sheet and just keep adding rows downward on the summary sheet. The design of your daily sheets doesn't help with any of the group totals. To have Excel perform its magic you really need your data in TABLES, similar to your summary sheet.

Read about tables at http://office.microsoft.com/en-us/ex...010048546.aspx

What kind of tables you create is dependent on what answers you want to get from the table. Are you trying to use it to pay the empolyees? Do you need the total of Puller or Stacker hours? Do you need it broken out by day, month, year? Or do you need to see how many pgs were sent to which stores?

These questions need to be assessed before you create your table to insure you can get what you want from the table. Then start entering data into an EXCEL TABLE FORMAT (instead of your daily tab format) so you can get what you need from the data.

**Pokey** · 07-06-2012, 08:25 AM

Thanks for the input. The reason I have it broken down by day is that I have to report this information daily using information completed by my employees. There are different interests in the data. Some are interested in just the shipments, some are interested in the quantities, as well as it being used for evaluations etc. Rather than complete several different pages for each group, I wanted to have one place where everyone could get what they needed which would save me time in completing something for everyone. I'm basically trying to consolidate the info needed by several entities. I'll definitely check out the link and thanks again for the input.

Have a great weekend!!