Formulas to extract data from one sheet to multiple sheets within an excel workbook

[LukeFerry]

Hi i have extracted raw data for an event i am organising and i am looking to use that data to create several lists on separate sheets. I have attached an example of the information I am using. Specifically i am looking to formulas that calculate what activities each individual is doing with in each age group. Please refer to spreadsheet attached for more detailed information .

Thanks
Luke

Excel Formula Question.xlsx

[humdingaling]

Q:
is your layout of the sheet literally how it appears in the example? or have you put them all onto one sheet for the example?

also
for Slow Reflex in primarys
it shows as monday day activities but I dont see it on the exported data?

[LukeFerry]

Oops for got to include that in the exported data. It should be included. See below

Attachment 423096

[LukeFerry]

I have put it all on the one sheet for the example to try and show what I trying to do. Each will be a separate sheet within the wookbook

[humdingaling]

hmm unable to download latest file
can you try re-attach it?

[LukeFerry]

The attachment is just a copy of the where the name should be (Slow Reflex) included in original spreadsheet

Screen Shot 2015-10-06 at 1.35.00 pm.png

[humdingaling]

oh ok
see attached to see what i have done with your sheet

i put primary into a seperate sheet as you would have done it

using a combinations of if and vlookup formula and a helper row which is in effect FULL name which you have in the export data
(if this is going to be problem it can be worked around but it will make the formula much more complicated than it needs to be)

conditions:
if actipass is yes then all days are "R"

=IF(ISNA(VLOOKUP(C4,Sheet1!D41:D46,1,0)),"","R")

by default if no actipass then it should be day activities, so

=IF(D4="","R","")

next
formula checks if actipass...

=IF($D4="R","R",IF(ISNA(VLOOKUP($C4,Sheet1!$K$42:$K$46,1,0)),"","R"))

if "R" then "R"
if not...do vlookup on COLUMN K which is where Monday Activity Name1 is .....change this vlookup column based on the day
ie Tuesday Activity Name1 is column P...Wednesday Activity Name1 = column U
(not sure how your name2 works.....i assumed it just doesnt)

only did primary because...its time consuming to do them all but once setup it should auto update if the shape of the data is the same

[LukeFerry]

Thanks. Is it possible to adjust the formula so that it does the following:

Screen Shot 2015-10-06 at 3.12.32 pm.png

Trying extract names from multiple purchases of the same ticket

Luke

[humdingaling]

so it can appear both ways? side ward and downwards?

use countif formula instead

=IF(COUNTIF(Sheet1!$D$42:$G$46,C4)>0,"R","")

see attached formula in action

i left steph the normal downwards way and the other 2 i added in sideward

[LukeFerry]

They need to be able to be added in both ways.

Thanks for you help i think you have solved it! Does the countif formula allow me to pick up names either direction?

Regards
Luke

[humdingaling]

in its current form

=IF(COUNTIF(Sheet1!$D$42:$G$46,C4)>0,"R","")

yes countif works as it counts any occurrence of the "full" name within the set size of the array (area of your formula)
if it counts it more than once then it will make it an "R"