Table Data Missing

[Jim]

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

 

[ozgrid.com]

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?

 

[Herbert Seidenberg]

Excel 2007 Tables
Structured References
http://www.mediafire.com/file/m5mnzyonmwy/03_29_10b.xlsx
Pdf preview:
http://www.mediafire.com/file/zz5nwyt5w4z/03_29_10b.pdf

 

[Jim]

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

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

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

 

[Jim]

Herbert,

Thank you, that was the exact solution to my problem.