Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Is this even possible?
Hey all,
I've been playing around with Access and Excel today, and I'm starting to think that Excel is the way to go. But for this project, there are a couple things I need to do, and I'm not sure if Excel can do it. Issue 1: I have 3 different data sets all for the same person (each person has a unique key). Is there a way to use either a button or a radio button to choose which data set to display? Issue 2: Each person needs to be assigned to a group. A tally of each group would be kept on another sheet. If I use something like a drop-down list to assign people to each group, is there a way to have Excel automatically update the other sheet as soon as that selection is made? Or maybe a way to force a refresh? Thanks! Dave |
#2
|
|||
|
|||
You can do all this, but how exactly, depends on your design. An example
here (on fly): You have a sheet Persons with a row for every person PersonID, FirstName, FamilyName, Group, ..., Dataset1Field1, Dataset1Field2, ...., Dataset2Field1, Dataset2Field2, ..., Dataset3Field1, Dataset3Field2, .... with headers in row 1, and data starting from row2. There are no gaps (empty rows) in table. You have a sheet Groups with a table Group, ... (the group column must be, but you can have additional info on this sheet too) Create named ranges: PersonsTbl=OFFSET(Persons!$A$2,,,COUNTIF(Persons!$ A:$A,"<")-1,#) where # is the number of columns in Persons table. Groups=OFFSET(Groups!$A$2,,,COUNTIF(Groups!$A:$A," <")-1,1) (it's used as source for data validation lists to choose group on persons table (and on any other additional sheet, when needed). GroupsTbl=OFFSET(Groups!$A$2,,,COUNTIF(Groups!$A:$ A,"<")-1,#) where # is the number of columns in Groups table. You define this named range only, when you have additional info in groups table, and the range is used as VLOOKUP parameter to get this additional info according the group selected for person. Now create p.e. a sheet PersonsBySets Format some cell (p.e. A1) as drop-down using data validation list with source "Set1","Set2","Set3" Into row 3 enter column headers. Into A4 enter the formula =IF(Persons!$A2="","",Persons!A2) Copy the formula into range A4:D? (as much rows down as you like) I assume you had 2 columns of additional info on groups sheet, and want this info displayed now for every person Into cell E4 enter the formula: =IF(Persons!$A2="","",VLOOKUP($D4,GroupsTbl,2,0) Into cell F4 enter the formula: =IF(Persons!$A2="","",VLOOKUP($D4,GroupsTbl,3,0) and copy them both down for same number of rows as previous columns Now I assume that every set contains 5 columns of info, i.e. columns 7-11 are Set1, columns 12-16 are Set2, and columns 17-21 are Set3 Into cell G4 enter the formula =IF(Persons!$A2="","",OFFSET(Persons!G2,0,(MATCH($ A$1,{"Set1";"Set2";"Set3"} ,0)-1)*5)) and copy it into columns G:K for same number of rows as in previous columns. When all was OK, persons list with selected set of data will be displayed. -- When sending mail, use address arvil<attarkon.ee Arvi Laanemets "Dave Linsalata" wrote in message ... Hey all, I've been playing around with Access and Excel today, and I'm starting to think that Excel is the way to go. But for this project, there are a couple things I need to do, and I'm not sure if Excel can do it. Issue 1: I have 3 different data sets all for the same person (each person has a unique key). Is there a way to use either a button or a radio button to choose which data set to display? Issue 2: Each person needs to be assigned to a group. A tally of each group would be kept on another sheet. If I use something like a drop-down list to assign people to each group, is there a way to have Excel automatically update the other sheet as soon as that selection is made? Or maybe a way to force a refresh? Thanks! Dave |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|