On 2002-05-02 02:29, Nobby wrote:
Col A - Custom Format - mmm - Month date entered here
Col B - Custom Format - ddd-mmm-yyyy - the column is blank at the moment and will now be deleted from the sheet.
Col C contains the account number, which is a mixture of letters and numbers e.g S342ABC. This is formatted as General.
All other columns contain text and are formatted as General.
Does this help?
Nobby
OK.
I'll assume that the labels like Account Number, etc. are in row 6 and the real data start at row 7.
Activate Insert|Name|Define.
Enter
Nrecs as name in the Names in Workbook box.
Enter as formula in the Refers to box:
1) if column A is numeric, which means that =ISNUMBER(any-cell-in-A) returns TRUE,
=MATCH(9.99999999999999E+307,'Main Data Table'!$A:$A)
2) if column A is not numeric,
=MATCH(REPT("z",40),'Main Data Table'!$C:$C)
Note. It must be either (1) or (2).
Activate Add. (Don't leave yet the Define Name window.)
Enter
PivotRecs as name in the Names in Workbook box.
Enter as formula in the Refers to box:
=Nrecs-(ROW('Main Data Table'!$A$6)-1)
I expressly called this one PivotRecs, because Pivot Tables must have a range which includes the row with the labels, that is, Pivot Tables needs to know the labels heading the data.
Activate Add. (Don't leave yet the Define Name window.)
Enter
MainDataTable as name (a name that you already have) in the Names in Workbook box.
Enter as formula in the Refers to box:
=OFFSET('Main Data Table'!$A$6,0,0,PivotRecs,11)
Since you're going to delete column B from the current data area, the area will now cover A thru K. That's 11 columns, whence 11 in the formula. This number can also be computed dynamically if the sheet Main Data Table doesn't contain anything but the data you want to feed to the PivotTables. Anyway, if you change the number of consecutive columns, you'll need to adjust this number.
Activate OK.
You're done.
Back to the original formula, as I promised:
COUNTA is risky, because it's unable to count blanks which leads to an incorrect range.
It did not include every column: it uses 1 at the end.
It will not be able to include any row of data beyond row 28.
Aladin