Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Displaying data on a different worksheet.
I have a worksheet called 'Data' which contain data in
cols A-K rows 1-2000. Is it possible to display every 20 rows of this data in a summary worksheet called 'Summary' so when I click a button called 'previous' or 'next' it displays the previous or next 20 rows of data. TIA Chris |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Displaying data on a different worksheet.
Chris,
This is one possible way that uses mostly formulas to accomplish the data display. I've made an assumption that the same columns on worksheet Summary will be used to display the columns from worksheet Data. I'm also assuming the first row of information on Data is in Row11. If these are not the case, then the formulas below will need to be changed. On the worksheet Summary, place the following values in the cells: A1: RowOffset B1: 0 Place the following formula in each cell A11:K30 on worksheet Summary: =INDIRECT("Data!"&"R"&ROW()+$B$1+0&"C"&COLUMN()+0, FALSE) Then change the value in cell B1 from 0 to 20, to 40, to 60, etc. The values in cells A11:K30 should change accordingly. You can use your Next and Previous commandbuttons to adjust the value of cell B1 up and down by 20. If my assumptions about the relative position of the data are incorrect, then you can adjust the INDIRECT formula. Currently, I'm adding zero to the Row() and Column() positions with the "+0". Change this accordingly to something like: "Row()+10" and "Column()+3" Troy "Chris" wrote in message ... I have a worksheet called 'Data' which contain data in cols A-K rows 1-2000. Is it possible to display every 20 rows of this data in a summary worksheet called 'Summary' so when I click a button called 'previous' or 'next' it displays the previous or next 20 rows of data. TIA Chris |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Displaying data on a different worksheet.
Worked like a charm.
Many thanks for your time and expertise Chris -----Original Message----- Chris, This is one possible way that uses mostly formulas to accomplish the data display. I've made an assumption that the same columns on worksheet Summary will be used to display the columns from worksheet Data. I'm also assuming the first row of information on Data is in Row11. If these are not the case, then the formulas below will need to be changed. On the worksheet Summary, place the following values in the cells: A1: RowOffset B1: 0 Place the following formula in each cell A11:K30 on worksheet Summary: =INDIRECT("Data!"&"R"&ROW()+$B$1+0&"C"&COLUMN()+0, FALSE) Then change the value in cell B1 from 0 to 20, to 40, to 60, etc. The values in cells A11:K30 should change accordingly. You can use your Next and Previous commandbuttons to adjust the value of cell B1 up and down by 20. If my assumptions about the relative position of the data are incorrect, then you can adjust the INDIRECT formula. Currently, I'm adding zero to the Row() and Column() positions with the "+0". Change this accordingly to something like: "Row()+10" and "Column()+3" Troy "Chris" wrote in message ... I have a worksheet called 'Data' which contain data in cols A-K rows 1-2000. Is it possible to display every 20 rows of this data in a summary worksheet called 'Summary' so when I click a button called 'previous' or 'next' it displays the previous or next 20 rows of data. TIA Chris . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Displaying data on a different worksheet.
Troy,
Additionally, you mention one possible solution using formulae. Would there be another solution available that achieves the same display but would also allow changes to the data, which would in turn affect the data on the main Data sheet? Regards Chris -----Original Message----- Worked like a charm. Many thanks for your time and expertise Chris -----Original Message----- Chris, This is one possible way that uses mostly formulas to accomplish the data display. I've made an assumption that the same columns on worksheet Summary will be used to display the columns from worksheet Data. I'm also assuming the first row of information on Data is in Row11. If these are not the case, then the formulas below will need to be changed. On the worksheet Summary, place the following values in the cells: A1: RowOffset B1: 0 Place the following formula in each cell A11:K30 on worksheet Summary: =INDIRECT("Data!"&"R"&ROW()+$B$1+0&"C"&COLUMN() +0,FALSE) Then change the value in cell B1 from 0 to 20, to 40, to 60, etc. The values in cells A11:K30 should change accordingly. You can use your Next and Previous commandbuttons to adjust the value of cell B1 up and down by 20. If my assumptions about the relative position of the data are incorrect, then you can adjust the INDIRECT formula. Currently, I'm adding zero to the Row() and Column() positions with the "+0". Change this accordingly to something like: "Row()+10" and "Column()+3" Troy "Chris" wrote in message .. . I have a worksheet called 'Data' which contain data in cols A-K rows 1-2000. Is it possible to display every 20 rows of this data in a summary worksheet called 'Summary' so when I click a button called 'previous' or 'next' it displays the previous or next 20 rows of data. TIA Chris . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Displaying Worksheet name in cell using formula | Excel Worksheet Functions | |||
displaying worksheet instructions | Excel Discussion (Misc queries) | |||
Formula result not displaying on worksheet | Excel Worksheet Functions | |||
Displaying worksheet name in cell | Excel Discussion (Misc queries) | |||
Displaying a specific worksheet on opening application | Excel Programming |