Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Watch cell value
I have a sheet which needs to display data in a specific
manner. I have data in the following format Store Code : Store Name : Region AA : Bromley : 3 AB : Croydon : 3 etc.................... DE : Newcastle : 12 DG : Morpeth : 12 etc ................... There are a total of 33 stores split into different regions. I have a full list off screen and want to populate the print area with only those from the region the spread sheet is using to compare data sets. On the cover the user selects the store code and the rest of the sheet is populated with data from stores in the same region pages 3 - 27. What I wanted was on the second page to dynamicly show only the stores in the region being examined. I have named each region as named areas. i.e 'region3' 'region12' etc and named the cell where I want the region pasted to or whown as 'regionpaste' What I need to do is monitor Cell A70 to decide what region to include in the printable area. This may sound confusing but ask away if you don't understand or mail me direct and I will send a copy of the sheet to explain what I mean. Any help would be usefull. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Watch cell value
Nigel,
If I understand correctly, you have a data list, and you want your user to click (double?) on a store, and then have that store's data, along with the data for only those stores within that Region displayed elsewhere. Is there a specific reason why you need to past the data elsewhere, as it would appear to me from what I read, that you could simply apply a filter in place. There are a couple of worksheet level events that will do this for you, BeforeDoubleCLick would be my pick, BeforeReightclick and SelectionChange would also work. You can then use activecell.row to return the row(r), and look for the value in R(r)C3 assuming your Region numbers are in column C. Once you have found that value, combine it with the string "Region" to identify your predeifned Area and copy the area to your paste range. Helps? Steve -----Original Message----- I have a sheet which needs to display data in a specific manner. I have data in the following format Store Code : Store Name : Region AA : Bromley : 3 AB : Croydon : 3 etc.................... DE : Newcastle : 12 DG : Morpeth : 12 etc ................... There are a total of 33 stores split into different regions. I have a full list off screen and want to populate the print area with only those from the region the spread sheet is using to compare data sets. On the cover the user selects the store code and the rest of the sheet is populated with data from stores in the same region pages 3 - 27. What I wanted was on the second page to dynamicly show only the stores in the region being examined. I have named each region as named areas. i.e 'region3' 'region12' etc and named the cell where I want the region pasted to or whown as 'regionpaste' What I need to do is monitor Cell A70 to decide what region to include in the printable area. This may sound confusing but ask away if you don't understand or mail me direct and I will send a copy of the sheet to explain what I mean. Any help would be usefull. . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Watch cell value
Use the Autofilter under Data=Filter=autofilter to make only those rows
from the specified region visible. You can use the change event in the worksheet to react to a change in Cell A70 to change the criteria in the autofilter. Turn on the macro recorder and apply the filter, select the dropdow for region and apply a criteria. Then turn off the macro recorder. Adapt the code to use the value in A70 for the criteria. -- Regards, Tom Ogilvy "Nigel Graham" wrote in message ... I have a sheet which needs to display data in a specific manner. I have data in the following format Store Code : Store Name : Region AA : Bromley : 3 AB : Croydon : 3 etc.................... DE : Newcastle : 12 DG : Morpeth : 12 etc ................... There are a total of 33 stores split into different regions. I have a full list off screen and want to populate the print area with only those from the region the spread sheet is using to compare data sets. On the cover the user selects the store code and the rest of the sheet is populated with data from stores in the same region pages 3 - 27. What I wanted was on the second page to dynamicly show only the stores in the region being examined. I have named each region as named areas. i.e 'region3' 'region12' etc and named the cell where I want the region pasted to or whown as 'regionpaste' What I need to do is monitor Cell A70 to decide what region to include in the printable area. This may sound confusing but ask away if you don't understand or mail me direct and I will send a copy of the sheet to explain what I mean. Any help would be usefull. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add Watch | Excel Discussion (Misc queries) | |||
Watch Window | Excel Worksheet Functions | |||
what does watch do? | Excel Discussion (Misc queries) | |||
[Click the star to watch this topic] finding 1st populated cell on a vector with va | Excel Discussion (Misc queries) | |||
What is the purpose or why would I use watch cell? | Excel Worksheet Functions |