Excel help please.

Colbaker · 15 Jul 2014 at 21:25

Hi all,

Kind of hard to explain what I am trying to do but here goes....

imaging 2 sheets on an excel spreadsheet, sheet 1 and sheet 2.

Sheet one has a list of names across the top B1 John, C1 Steve, D1 Gavin, E1 Barry
Now in Column A there is a list of girls names A2 Jane, A3 Claire, A4 Carly, A5 Joanne, A6 Amy.
Under the boys names all the cells are filled in as far as A6 Amy with either, Married, Engaged, Dated and Not Met.
OK thats Sheet 1

Sheet 2 is where I want all the formula action, in Cell A1 I want to Put any of the boys names manually.

In Cells A3 downwards I want to list all the girls names where the boys name in A1 has either Engaged or dated them.

Now im thinking maybe an index and match but cannot get it to work.

please help.

Colbaker · 16 Jul 2014 at 12:12

tcfreer said:
Just working this through for you

If on Sheet 2 you have the persons name in cell A1
Then from a2 down to a5 your 4 catorgories

your match formula would be - =MATCH($A$1,Sheet1!$B$1:$E$1,0)

This only brimngs back the colum for that persons name - the next part would be to match the A colum on sheet two with a index against row a

Just trying to figure out the index and return part to bring the name in colum a back against each name

Kind of, No catagories though, i will use Cell A1 on Sheet 2 to manually enter a name, I then only want to list the names of the girls that this person has Dated or engaged, So for instance if i put Steve in Cell A1 on Sheet 2 then it would only list the Girls he has dated or engaged, So if on Sheet 1 Claire and Amy had Engaged and Dated under Steves name then on Sheet 2 it will list these names and only these names.

If may need an array formula but i have no idea how to do that.

Colbaker · 16 Jul 2014 at 12:57

tcfreer said:
You have to specify some were though which criteria you want to return on?

For example

A1 = Steve B1 = Dated

B1 = Name
etc
etc

Thats how you mean right?

Kind of.

A1 = Steve

A2 = name (Relates to the Girls name of sheet 1 that has Married and/or Dated next to them under the name Steve)
etc
etc

I don't really want to go into VBA yet and was hoping i could use a formula of some sort.

Maybe ill do it as if it was for my work, Might make more sense than the girl boy thing.

Imagine i have a sheet that has a list of say 100 town names in column A, i.e A2 London, A3 Harlow, A4 Norwich etc, , The along The top Row i have Employee names, I.e B1 Colin, C1 James, D1 John etc, In a type of grid, Now under the column with the names i enter weather the employee has Access to the Town branch on that row, So Colin has "FULL" access to London and "PARTIAL" access to Norwich but "NO ACCESS" to Harlow and all the others.

On sheet 2 i want to be able to Enter a name, so for this one Colin and then i want it to list all the Town Branches that Colin has "FULL" and "PARTIAL" access too only so i could quickly find out what Town Branches Colin has some sort of access too.

I hope that makes more sense, Can this be done without VBA?

Colbaker · 16 Jul 2014 at 15:36

tcfreer said:
It can - I'm still working it out though its not a short formula I don't think.

Let me dig about abit more and see what I can come up with - Failling that google a forum called MrExcel and post it there - those guys are unbelievable wizards, they may need more detail then this though - data layout, is it a fixed layout, is it dynmaic (e.g will new rows be added on etc)

Also how the data is constructed - laid out - all have effects on how simple this is

If it was laid out well, a vlook up, could in theroy work

So if you had the locations on the vertical, with the name on the horizontal (Again in fixed locations) you could then use a vlookup, with a seperate cell giving the rownumber

This almost works how you want it but its not there yet

=VLOOKUP($A$1,Sheet1!A1:H4,MATCH(Sheet2!$C$1,Sheet1!$A$1:$H$1,0),FALSE)

So sheet 1
Vertial is names
horizontal is location
Area has access

Sheet 2
A1 - drop down of names
C1 - drop down of laction
A3 - forumal above

Basically you pick the name and the loaction and it tells you what access they have - its not a perfect fit for what you want though but its close - though it is for singular, it needs adapting to read multiple - think I have a idea but let me run it out

Thanks for your help, im sure there is a workable formula that does exactly what i want but its far to advanced for me.

Colbaker · 16 Jul 2014 at 15:37

tcfreer said:
If its not sensative, how many branches are in your data - excluding duplicates?

there are exactly 115 branches but more could be added when they open, There are 9 employee's as well.

Colbaker · 17 Jul 2014 at 20:50

tcfreer said:
OK was up late working on this - still no near to a forumula based soloution

The VBA you have been given above fits what you need though, and its simple to drop in to a VBA project in the work sheet and will give far more flexabilty + easier to fix if errors happen

I'#ll carry on working on this, its been a year since I did this stuff though so I'll drop you a note here if I get any were soon

Thanks

, I have also been working on this late, I have never done any VBA which is why I would rather see if a formula can do it, I do intend going into VBA at some point though.