Problem with data sheet and linking it to another sheet to make a report

Originally Posted by Psychotron

This is kind of hard to explain, but I will try. I'm making a report in excel by linking the report sheet to another sheet that has the data. What I am doing is entering the equal sign, and then clicking on the data in sheet 2 and put them under the labels I have on sheet 1 (the report). So here are the steps so far:

1. I open excel and design a report with lables and field names, now i just need the data for the report
2. I insert a new sheet that has the data.
3. I go back to the first sheet (report) and put an equal sign into one of the fields where I want the data to appear in my report.
4. I go back to the data sheet and select the first field with the data I want. Then I click enter.
5. The data appears and I can use the plus sign to scroll down and get all of the data that was in the data sheet for that column.
6. I do this for all the fields I need.
7. Then I highlight the whole report and scroll down with the plus sign to make multiple reports with the data in succession.

When I do that, only the first two new reports contain the the correct data.
The data on the reports are also out of order from what appears on the data sheet.

8. I highlight only one field that should contain the data and scroll down, it works perfectly.
9. I highlight a few more fields (not all), I get a few more correct reports, but less data than if i used one field. It seems the more fields on my report are highlighted, I get less and less data. It's still out of order. The only time it is in the correct order and everything is correct is if I only highlight one cell.

If anyone understands what I'm doing could you help me out? How do I get it to work like it should?

Thank you,
Brad

Hi,

you didn't describe what the error was, ie, if you use row 1 in your data and fill that down you expect to see 2 3 4 5 6 7 8 etc in succession, this applies to all columns A B C D E through IV (2003)

If you didn't progress through the rows what did you get?
ie, in place of what you expected what did you get?

It might help if you post a small samply sheet.
---

Originally Posted by Psychotron

Yes, when I scroll down on each of the data individually it works correctly, but when I highlight multiple fields and school down it doesn't.

I think i figured out what is wrong though. When I highlight the report and scroll down to make multiple reports, it skips about 40 rows of data from the data sheet each time. That's why it shows zeros because there's only about 90 rows of data in all. My problem is I need to get all the data to appear in secession instead of jumping 40 rows.

I've attached the report to this post.

Thanks a lot,
Brad

I guess you can say I'm really struggling to see what you are doing.

You have a sheet2 of data, and a Sheet1 that selects (apparently) every 44th row via formula, but you say "When I highlight the report and scroll down to make multiple reports " - which is highlight what and scrolldown where and how are you making multiple reports?

Are you referring to sheet1 where you have a formula that refers to row 2, then you formula fill that 44 rows later and want it to refer to row 3????


--

if so then instead of

=Sheet2!J2

try

=INDIRECT("Sheet2!J"&INT(ROW()/40)+2)

---