Excel-sections of two sheets (same workbook) printed on one page?

Can you print two sections on two sheets of a workbook on the same page.

Problem is, I have a spreadsheet with data above and below. Each week, new data is entered, and the results are printed. I have about 50 columns where potential data can be entered, but rarely do I use more than 10, therefore, the rest are hidden and only the ones with data are printed.

Same with the calculations below. The data from above is pulled below and any rows that don't have calculations (because the columns above have no data) I hide.

The problem is the data below is in Columns A to F, and I often need to hide columns C to F, as data is rarely used in those columns. Obviously when I hide them above, they hide my calculations below.

I know that I cannot selectively hide parts of columns, so I thought if I could move the calculations from below to a new sheet, then it would solve the problem. However, management wants the data above and calculations below on the same page, not on two pages when printed. I know i can set the print area across the two sheets, but they still print on two pages.

I don't know much about generating reports in Excel, but is there some easy VB script that could be written to print both on one page? Any other ideas?

 

I would create a report sheet. Set that out so it will print nicely. Then link cells on the report sheet to the cells holding the data.

So you have a sheet called data1, and another called data2. The output will be on a sheet called Report.

I want the data in cell A3 on data1 to appear in cell B2 on Report. So in B2 Report, I will enter:

Code:

=data1!A3

I want the data in cell G6 on data2 to appear in cell B3 on Report. So in B3 on Report I enter:

Code:

=data2!G6

Not the most elegant of solutions, but simple and I like simple

 

hehe. I didn't think of that. Makes sense, but the person who will be using the spreadsheet may have trouble with that (she knows NOTHING about excel), because it would have to be transfered to the "report" sheet with each report she does, and she does a lot of them each week. Basically, a calculation is done for each supervisor in the company, calculating the time they spend "supervising" (we are on an hourly system here). There are about 50 different rates, and 3 different percentages that calculation the pay, so there could be 3 entries in a week, or 30...it all depends.

And because some of the data is hidden, she can't just copy and paste the data over, because that would copy the hidden data.

For your way to work, she would have to complete all the data, hide all that is not used in each, go to the report page, and manually input the formula right? Or is there an easier way to do it?

 

Not if you can standardise where the data is put on each sheet.

You put the formulas in first! Try this for example:

• Create a new workbook
• Rename the three sheets: data1, data2, report
• Go to the report sheet. In cell A1 enter the formula:
  
  Code:

  =data1!A1

• Drag the content across the report sheet to update the content of the row 1 cells (you don't need to go mad. Just across the viewable area will do)
• Then in the report sheet cell A3 enter the formula:
  
  Code:

  =data2!A1

• Drag the content across the row to update the content of all the cells in row three.

Now anything you enter in row 1 of sheet data1 will automatically appear on row 1 of the report sheet. Anything you enter in row 1 of sheet data2 will appear on 3 of the report sheet.

By default, when you print in Excel, you only print the active sheet. Therefore, if the user switches to the report sheet and prints, they will not need to hide anything on the other two sheets.

 

That makes sense, but is there a way to set it so that only data that is "filled in" will show up on the report page? Because I have all the way from A5 to BZ49 where there could be data, but I only want to print the parts that have data.

For example, across the top I have rates from $53 per hour to $85 per hour, however, they can be paid at 4% or 5%, so I have 53 to 85 at 4% and 53 to 85 at 5%. Most of the time it is either 65/75/or 85 at either 4 or 5%, but data could be completed at any of the other rates.

Then down the rows, we have the individual names, and we put the time they spent at each rate ([hh]:mm) under the appropriate column.

That data is pulled to the calculations below/sheet 2 (depending which way I do it).

Any of the columns/rates that are not used, are hidden for printing. How would I get the report sheet do to that as well? Would the report sheet automaticall skip blank data rows and put it in order?

I'm not sure if I am explaining this right. It just seems that I would have the same problem if the data is pulled to the report, since it would all need to be copied, and I would have the same problem when I hide the unused rows, and it would hide the data below in the calculations.

For example.

In row 6, I could have data in columns N, O, Y, and AI.
In row 7, I could have data in columns F, N, AI, and BP.
In row 8, I could have data in column N only.

and so on. It's totally random. How would the report sheet know which data to copy?

 

The report sheet system I have suggested gets you over the problem of the print out being on two seperate sheets.

Then that is a fundamental problem. You need to design the sheets so as to get rid of the randomness, or you will never be able to automate or simplify it.

Have a look at the COUNT, COUNTA, COUNTBLANK, and COUNTIF functions. They can be very useful for picking information out of a group of cells.