Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Table Data Missing
I'm using Excel 2007.
I have created a table with customer numbers, names, status, and several other columns. I named this table including the header row 'Mortgage_Pay_Table'. Excel automatically named the table EXCLUDING the header as 'Table4'. This table has 37 rows excluding the header numbers from 100 to 136 in the first column. This table is formatted correctly for visual appearance, ease of data entry, and printing. I am trying to make a near 'duplicate' of this table on another sheet that would be hidden to generate raw data for analysis with a pivot table. This new table is called 'Table5' by Excel. In the first row & column of the table I have put in '=Table4[Mortgage No]'. In the second column, first row I put in '=Table4[Mortgage Name]. All of the rest of the columns in the first row I entered the same format for the data I wanted from Table4. Excel automatically copied these formulas down the different rows and all the data transfered correctly except I'm missing the first row from Table4. The first row in Table5 returns a value of 101 rather than 100, and the next column also shows the data from the second row of Table4 rather than the first. Why doesn't it show the first record? JIM |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Table Data Missing
Why not reference the table by cell reference? E.g. =Sheet1!A1
BTW, why duplicate the table? Why not base your PivotTable off the orginal data? -- Regards Dave Hawley www.ozgrid.com "Jim" wrote in message ... I'm using Excel 2007. I have created a table with customer numbers, names, status, and several other columns. I named this table including the header row 'Mortgage_Pay_Table'. Excel automatically named the table EXCLUDING the header as 'Table4'. This table has 37 rows excluding the header numbers from 100 to 136 in the first column. This table is formatted correctly for visual appearance, ease of data entry, and printing. I am trying to make a near 'duplicate' of this table on another sheet that would be hidden to generate raw data for analysis with a pivot table. This new table is called 'Table5' by Excel. In the first row & column of the table I have put in '=Table4[Mortgage No]'. In the second column, first row I put in '=Table4[Mortgage Name]. All of the rest of the columns in the first row I entered the same format for the data I wanted from Table4. Excel automatically copied these formulas down the different rows and all the data transfered correctly except I'm missing the first row from Table4. The first row in Table5 returns a value of 101 rather than 100, and the next column also shows the data from the second row of Table4 rather than the first. Why doesn't it show the first record? JIM |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Table Data Missing
Excel 2007 Tables
Structured References http://www.mediafire.com/file/m5mnzy...03_29_10b.xlsx Pdf preview: http://www.mediafire.com/file/zz5nwyt5w4z/03_29_10b.pdf |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Table Data Missing
Dave,
Thank you for your reply. Herbert below answered my original question but I would like to answer your questions. The original reference table is dynamic and it's also easier to reference a table and column name. Also the original table has the option to sort and filter the data. For your second question, the reason I'm not using a Pivot table on the original table is that this table does not have enough data in it. Let me explain. The original table is a loan data sheet with just the basics (Name, Value, Rate, Start Date, etc). The second table imports this data and then calculated up to 60 future payment dates, imports actual payment dates, amounts, calculated days late and a few other items. I haven't reached the skill level in Pivot tables yet where all the future payment dates calculation, actual payment importation for another sheet, days late calculation, etc could be calculated if this is even possible. However I can do this on another sheet and can work a pivot table to produce what I want. I suppose that I could have hidden columns or move set the print area to exclude the columns I don't want shown but I didn't want the data entry person working on the same sheet that I'm working on. This way they can continue to enter data whilst I'm working on the analysis sheet. I understand that access is the correct program for what I'm doing, but I don't have any experience in it yet, hopefully find time this year. I just had a quick peek at your site, I see you develop and sell XL software, have you ever produced a mortgage/loan servicing type product? Thank you again, JIM "ozgrid.com" wrote: Why not reference the table by cell reference? E.g. =Sheet1!A1 BTW, why duplicate the table? Why not base your PivotTable off the orginal data? -- Regards Dave Hawley www.ozgrid.com "Jim" wrote in message ... I'm using Excel 2007. I have created a table with customer numbers, names, status, and several other columns. I named this table including the header row 'Mortgage_Pay_Table'. Excel automatically named the table EXCLUDING the header as 'Table4'. This table has 37 rows excluding the header numbers from 100 to 136 in the first column. This table is formatted correctly for visual appearance, ease of data entry, and printing. I am trying to make a near 'duplicate' of this table on another sheet that would be hidden to generate raw data for analysis with a pivot table. This new table is called 'Table5' by Excel. In the first row & column of the table I have put in '=Table4[Mortgage No]'. In the second column, first row I put in '=Table4[Mortgage Name]. All of the rest of the columns in the first row I entered the same format for the data I wanted from Table4. Excel automatically copied these formulas down the different rows and all the data transfered correctly except I'm missing the first row from Table4. The first row in Table5 returns a value of 101 rather than 100, and the next column also shows the data from the second row of Table4 rather than the first. Why doesn't it show the first record? JIM |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Table Data Missing
Herbert,
Thank you, that was the exact solution to my problem. "Herbert Seidenberg" wrote: Excel 2007 Tables Structured References http://www.mediafire.com/file/m5mnzy...03_29_10b.xlsx Pdf preview: http://www.mediafire.com/file/zz5nwyt5w4z/03_29_10b.pdf . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table missing data item | Excel Worksheet Functions | |||
pivot table - two colums of data are missing | Excel Discussion (Misc queries) | |||
missing data from table | Excel Discussion (Misc queries) | |||
missing data from table | Excel Discussion (Misc queries) | |||
missing data on Pivot table | Excel Discussion (Misc queries) |