Is this even possible?

[Guest]

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

 

[Arvi Laanemets]

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?
(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.