Excel help please.

Colbaker

Colbaker

Colbaker

Associate
Joined
8 Dec 2004
Posts
1,970
Location
Paignton, Devon
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.
 
You're wanting it to return more than one result, so I think you need an array formula.

Unfortunately, while I (think I) know you need one, I know nothing about how to use them, so I can't help any further.
 
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 retunr part to bring the name in colum a back against each name
 
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
Click to expand...

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.
 
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?
 
I asked a very similar question on another messageboard a while back.

Quartz said:
I have a range and want to find the intersection of the column name and row name. Something like =ARRAYVALUE("Employee Data", "Weekly Salary", "Joe Bloggs") where ARRAYVALUE is the name of the VB function, Employee Data is the name of the range, "Weeky Salary" the name of the column, and "Joe Bloggs" is a row. It would give me how much Joe Bloggs earns per week from the range (e.g. $5000).

An example of how to do this is given in the help, but it's rather complex and not very readable, so I'm wondering if someone has written a VB function to simplify it all up in the manner of the example above.
Click to expand...

And I got the answer:

CookingWithGas said:
Here is the VBA code to do this within the same workbook. It uses the same solution that others have probably noted but encapsulates it in VBA. Two things to note:

1. This code must be placed in a module. That is true for any user-defined function to work.

2. The first argument is not a string giving the name of the range, it's the name of the range itself. So in your example above, you would instead omit the quotes like this:

=ARRAYVALUE(Employee Data, "Weekly Salary", "Joe Bloggs")

On edit I note that Employee Data is an illegal range name because it contains a space; you would have to use something like EmployeeData.
Here is the code, I have tested this. It will work no matter where your range is (that is, it does not have to start in A1):
Code: 
Option Explicit

Public Function ARRAYINDEX(indexRange As Range, columnValue As Variant, rowValue As Variant) As Variant

   Dim row_num As Long
   Dim column_num As Long
   
   With indexRange.Cells(1, 1)
   
      row_num = WorksheetFunction.Match(rowValue, .EntireColumn, 0) - .Row + 1
      column_num = WorksheetFunction.Match(columnValue, .EntireRow, 0) - .Column + 1
      
      ARRAYINDEX = WorksheetFunction.INDEX(indexRange, row_num, column_num)
   
   End With

End Function

If you are not familiar with VBA or creating user-defined functions, write back. :)
Click to expand...

So what you'd need to do is something along the lines of If (ArrayValue (Names, Name1,Name2)="Dated",...
 
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?
Click to expand...

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?
 
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
 
Last edited:
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
Click to expand...

Thanks for your help, im sure there is a workable formula that does exactly what i want but its far to advanced for me.
 
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
 
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
Click to expand...

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.
 
You must log in or register to reply here.
Back
Top Bottom