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

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

0
5/10/2007 9:42:30 PM
excel 39879 articles. 2 followers. Follow

3 Replies
478 Views

Similar Articles

[PageSpeed] 45

Bradley

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

Peter

"Bradley Burton" wrote:

> 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
> 
> 
0
BillyLiddel (193)
5/11/2007 10:51:01 AM
On May 11, 5:51 am, Billy Liddel
<BillyLid...@discussions.microsoft.com> wrote:
> 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
>
>
>
> "Bradley Burton" wrote:
> > 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- Hide quoted text -
>
> - Show quoted text -

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

0
5/11/2007 11:30:05 PM
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
"Bradley Burton" wrote:

> On May 11, 5:51 am, Billy Liddel
> <BillyLid...@discussions.microsoft.com> wrote:
> > 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
> >
> >
> >
> > "Bradley Burton" wrote:
> > > 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- Hide quoted text -
> >
> > - Show quoted text -
> 
> 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
> 
> 
0
BillyLiddel (193)
5/12/2007 7:24:00 PM
Reply:

Similar Artilces:

Problems with reallocations in the Advanced Budget
I am a using the Advanced Budget in Money and for years I have reallocated money between categories and months. recently when I updated my transactions all my real locations were wrong. Basically the money I allocated from March to February was not only back in February it reversed it and made it look like I sent Money from February to March so now any category that I have reallocated money (an that is most) is wrong. Does anyone know of a way to fix this, I have run the repair, but that did nothing. Microsoft Money Plus Premium Version 17.0.125.1415 I am so glad to see that I am...

Problem automating page in IE 8
Hi, I am using the following code to open a page to sign in to MyMsn and it does not work in the same way as other secure sites do. I don't know if Microsoft does something different(big grin here), but the code, which I believe I got from Karl Peterson, shows 22 objects on the document, but does not print anything for the obj type, obj name or obj value. Passing the document to a timer to run HTML links does not show the sign in boxes at all either. Is there another way to see the items on this page and pass the email address and password to them? Code: Private Su...

Reporting from Project Server
I dont know if i need to ask this question here or in the Access section. I have an ODBC connection to the Project Server database so I can make reports through Access. Access' limit of 255 fields per table is causing me some trouble. for example, the MSP_VIEW_PROJ_PROJECTS_ENT table has well over 255 fields. Access only shows me the first 255 fields. how can I change that so I can see all the fields in that table? thanks, Hadi Hadi, I have not tried this yet it may be a viable option. Have your DBA create a view that pulls the key fields to this table and the specifi...

Autonumber created.. problems in the future?
I managed to create an autonumber in Microsoft CRM. I did this by making a field "Number"(in the database "New_Number") and I published it on the form. Then I went To the SQL server and I changed the field in the table to Identity Yes, Identity seed 1, Identity Increment 1. I locked the field on the form. It worked! I think that this is not supported by Microsoft. But has anybody got any idea which troubles i could get with this configuration? San ________________________________ Do you know all add-ons for Microsoft CRM? Visit http://www.pimpmycrm.com The biggest dange...

Linking files 2 ways
I have a work book that is linked to another and vise versa. As thus: Workbook A is where the input of data is made; Workbook B has a link to the input from workbook A; Workbook A retrieves the altered data back as a link. Although this all works fine with both books open, I note that if I open workbook A by itself, that the data it retrieves from Workbook B is not updated . If However, both books are open, there's no problem. I thought linked books were updated automatically if the Update remote references has been selected?? But it appears that the second book is not updated until it ...

Question -Problem
Has anybody ever seen an error message (0x800cccF6) message? I cannot configure my Outlook to retrieve Hotmail. Works with other profiles on this computer running winxp. Stan-The-Man-2006 wrote: > Has anybody ever seen an error message (0x800cccF6) message? I cannot > configure my Outlook to retrieve Hotmail. Works with other profiles > on this computer running winxp. If you have a free Hotmail account, note that it won't work anymore (even your other profiles will eventually have problems) - see http://www.slipstick.com/addins/services/online.htm#hotmail ...

Changing Cells and entering data in them
Thanks for the help again. Big thanks to Steve you've got me this far. I went out and bought a book, but it's like reading a foreign language. I was informed today that I can't have message boxes come up. I need to have the code point at the cells and if they are blank turn which ever one is blank red or if both are then both turn red then pause for each cell to be filled in. Cell F14 "Last Name" then automatically go to Cell F16 "First Name" on tab or enter. Basically if Cell F22 or F23 has an X in it, Cells F14 an F16 turn red and cell F14 has the focus...

AD Sync problem
Hello, One of my DC was disconnected from the network for quite a long time and cannot be synchronized with AD anymore. I have been trying to apply the http://support.microsoft.com/kb/325850/en-us procedure to reset the machine account password. After launching the command "netdom resetpwd /S: ....." I've got the following error message : "The machine account password for the local machine could not be reset. Echec d'ouverture de session : unknown user or incorrect password. The command failed to complete successfully." ANy idea to help me ? Man...

Using Relative path for XML data file?
Is there a way to specify a relative path to an XML data file imported into Excel 2003? I am writing a web app that generates report data as XML for the user to download to their local machine. This data is to be consumed by an Excel reporting spreadsheet, which contains display-formatted tables and charts that are mapped to various data fields in an XML Map, which is in turn linked to the xml data file they will download. The idea is the user only needs to download the data for updates, not the whole spreadsheet. However, since I cannot predict the path where the user will store their...

Excel button problem
Hi All I have a macro that copies a worksheet in the active workbook and puts it into a new workbook - then formats it and deletes any buttons on the worksheet. On the first click on the button the macro works ok. On the second click, it fails because the all assigned macros on all buttons in the active workbook changed from "mba" to "book1!mba". Book 1 doesn't exists (wasn't opened, wasn't saved, doesn't have the macros). I've never experienced this problem before?? Can anyone help to solve this problem? FYI The macro to do this is c...

WCF Client serialization problem
I posted the problem on another forum, and to prevent duplicate posts, but get as many professionals as possible to look at it, I include the url in this post. Please help! http://stackoverflow.com/questions/2948657/migrating-webclient-to-wcf-wcf-client-serializes-parametername-of-method ...

Pulling data from separate tabs
When charting in Excel 2002 is there a way to use sets of data from two different tabs within the same worksheet? For example, a spreadsheet contains separate tabs for prior year and current year data. Is there a way to reference the data or label series to pick up data from both? I tried pointing and clicking, and then typing the following as a reference for the axis labels: ='Prior Year'!$B$110:$M$110,'Current Year'!'$B$110:$M$110 but receive an error that I'm referring to an external worksheet. I've used the comma (') in the past to reference breaks ...

print multiple pages on one sheet of paper
I am using mailmerge in Publisher to create placecards for a party we are hosting. The final size of the placecards is 1.5" by 1.5" and we have to print 100 final cards. Publisher gives me the option of printing multiple copies of the same page on one sheet of letter sized paper or one page on one sheet of letter sized paper. What I would like to do, however, is print multiple different pages on one sheet of paper. If I cannot find a solution for this, I will need to print 100 separate pages with a 1.5" square box of copy in the center of each sheet. In page setup, sel...

cannot open hyper links in outlook
when I try to open a hyperlink in outlook, I get the following message: This operation has been cancelled due to restrictions in effect on this computer. Please contact your system administrator. ----- I am the system administrator. HELP This is a problem with IE, not Outlook. You need to reset your internet settings in IE's Tools, Internet Options, Advanced tab. (Or Control Panel, Internet options, Advanced tab). See http://www.slipstick.com/problems/link_restrict.htm for more information. "Donald McNeely" <Donald McNeely@discussions.microsoft.com>...

data sort
ok now should be simple >> I need to sort by month on data that is held in format >> day/month so eg 1511 1510 3011 3010 now custom/ends with/ 11... does not work custom/ends with/ ??11.. or *11 does not work either contains 11 does not work (& would also be wrong if data set contained 1011) but still I am stumped so any help would be great cheers Alex I would be inclined to add a new, temporary field of formulas that pull off the right 2 digits, and sort by that: =RIGHT(A1,2) -- Jim Rech Excel MVP ...

Problem with Range
Hello All, Using Windows & Excel XP. I have a worksheet that has times located in every other column, A1:A30, C1:C30, E1:E30. I then name the range "times". I want to find the count of times that are between 0:30:00 and 0:39:59 (30 and 39:59 minutes). I write the formula: =COUNTIF(times,">=" & TIME(0,30,0)) - COUNTIF(times,">=" & TIME(0,39,59)) but get the error #VALUE! I have tried writing a formula for times in one column and consecutive columns and it gives the correct count, it is just when the times in every other column that th...

Having problem with spoofing email
Our users just received multiple email from different users outside the company. In the To: line, it shows his user name correctly but when he print those email, the To: line was showing somebody else name on the print out. Is there a way to block this behavior? I'm using E2k3. For some reason our spam (postini) didn't pick up these emails. Thank you, Could you please post the message in raw format (including the mail headers) Petch wrote: > Our users just received multiple email from different users outside the > company. In the To: line, it shows his user name cor...

Offline Synchronization Problem #2
I get the following error when I try going offline. The excelption is taken from Event Viewer of the Client Machine. Event Type: Error Event Source: MSCRMOfflineSync Event Category: None Event ID: 6000 Date: 3/25/2008 Time: 5:03:33 PM User: N/A Computer: WINXP Description: An error occurred during Offline Synchronization. Try going offline again, or restart Microsoft Outlook. saInsert failed for entity 'ActivityMimeAttachment', batchRows=0 with exception System.FormatException: Index (zero based) must be greater than or equal to zero and less than the size of the argument li...

outlook 97 and express email problems
Hi, I am currently on an IBM X21 laptop and is running windows 98 se with office 97 pro. I recently experienced some problems with outlook (illegal operations etc) and reinstalled office to fix the problem but since then I have not been able to send or recieve emails with outlook 97 and outlook express 6. I simply get an error message saying the host can't be found (but does exist and I can ping it successfully). Any suggestions on what I might do? I have tried creating new accounts in windows mail and outlook express, but I still get the same error. Thankyou in advance! Tim D...

message delivery problems
Hello! There are several email address that when I send them an e-mail, they just sit in the Queue. I did message tracking and its telling me that "Message Routed and Queued for Remote Delivery". Can anyone suggest symptoms for this? Thank You! usually messages sitting in outgoing queue take time due to DNS related issues. Do you have issues performing NSlookups on any domains from your workstation? Can you perform the same NSlookups from the exchange server? From the exchange server, can you perform smtp communication directly to the mailserver that would receive the e...

Cannot open Outlook, keep getting Send error report of Microsoft.
We are having problems with Outlook 2003. Everytime we try to open, we get this: Send error report to Microsoft. Any ideas why? All windows and office update have been completed. ...

q Emailing Excel Sheet With Outlook
I am trying to send a table from Excel within the body of an Outlook (lastest versions) to be sent as a fax. Am able to do this, BUT when the document prints out on the fax machine the formating is off (too big for the sheet). I am flexible on changing the method I send it to the fax machine, however it must be sent to the fax via macro. Below is the code I am using. Any help would be greatly appreciated. David Public Sub DoIt() 'On Error GoTo Handler Dim EmailAddress(0 To 2) As String Dim Count As Integer Dim N As Integer Dim sRec1(0) As String Dim sRec2(0 To 1) As...

predict future data
Is there a way to create an XY line graph wih plotted data, yet leave room to predict future data on the axes? I can get the graph, but the x and y axes stop at the last data points, and I want those axes continued so that the existing data can be examined and future data predicted and plotted on the same graph, but I am not sure how to accomplish this. Any suggestions would be appreciated. Thanks. Jeff 1) Click on data series in chart, use Add trendline; in Option tab specify some units forward OR 2) Read Help about TREND and FORECAST, and SLOPE and INTERCEPT OR 3) Get crystal bal...

XSL Problem
Hi, I have the following problem. There are 3 files. ------------------------------------------------------- a) XML file <?xml version="1.0" encoding="UTF-8"?> <?xml-stylesheet type="text/xsl" href="hall.xsl"?> <quizcirclecontests> <contest> <id>1</id> <date>15-Nov-02 to 22-Nov-02</date> <participants> <name>AS</name> <name>JP</name> </participants> </contest> <contest> <i...

Problem with named range for a large spreadsheet in Excel 2007
Hello, I am trying to generate a large Excel spreadsheet apprx. 10000 rows and 40 columns. I am generating defined name section in the Workbook.xml part of the XLSM package. Here is a sample entry from that section <definedName name="_._44802_._0_._0_._0_._top_line" localSheetId="0" hidden="1">Sheet1!$B$2</definedName> Although the generation goes fine, I can not open the spreadsheet as the Excel throws an error message saying the package is corrupt. But this is not the case if the spreadsheet is small say, 200 rows by 10 columns. ...