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

  • Thread starter Thread starter Bradley Burton
  • Start date Start date
B

Bradley Burton

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
 
Bradley

I think that what you need is a Pivot Table Report. Debra Dalgliesh has lots
of information onhttp://www.contextures.com/tiptech.html. Scroll down to P.

Peter










- Show quoted text -
Click to expand...

Ok. thanks. I will look at it, but i think I figured out what was
wrong. I couldn't figure out how to attach it, but my report is here:
http://home.comcast.net/~bradbad/report2.xls

Here's what's happening if I can just sum it up. When I scroll down on
each of the (referenced) data individually on my report, it works
correctly (the data appears in succession), but when I highlight
multiple fields (like my whole report) and scroll down, it doesn't
show up in succession.

This is what's wrong. 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 are 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.

Thanks a lot,
Brad
 
Bradley

Sorry for the delay, I had to go out.

What you want is VLOOKUP formulas. I have looked at your wkbook and inserted
the formulas in the first report. I can send this back to you if you e-mail
me at
peter_atherton AT hotmail.com. Do the obvious with the AT.

If you prefer to do it on your own here are the steps I took.

1. Moved the JobID to column A on sheet 2
2. Created a named range for the data called JobDetails for the range A1:S500
3. Sorted the JobDetails by JOBID (column 1) this is necessary because
LOOKUPs can return an error if the UNIQUE key is not sorted

Lookup takes the form VLOOKUP(ref to lookup, Range to lookup, Column Offset,
type)

4. On sheet1 c5 I Type an invoice number to lookup (I changed this later)
5. In A3 type =VLOOKUP($C$5,JobDetails,2,0) this i draged across and changed
the column offset using helper column a,b,c .... s and 1,2,3 ... 19

For sales tracking Sheet1 B17 I changed it to
=IF(VLOOKUP($C$5,JobDetails,15,0)="","No
Details",VLOOKUP($C$5,JobDetails,15,0))
(be careful of the line wraps on the above formula) as I was thrown when no
details appeared.

6. Included similar formulas in B26 and B35.

Now you could just copy this formulas down to the next version of the report
after removing the Absolute cells references for C5 by pressing F4 while each
formula is in Edit mode.

However, I added a combo box from the form menu in sheet1 c1. Right-click on
this and select Format control.

I set the Input range to Sheet2!$A$2:A500,
set the linked cell to D1

and changed the Formula in sheet1 c5 (The Invoice number) to
=INDEX(JobDetails,D1+1,1)

The +1 added to D1 is necessary because of the headings in sheet 2

As I say, you can e-mail me as above and I'll send you the Report2(copy)

Regards
Peter
 
Back
Top