Hi All, I'm a bit of a novice when extending out from the normal set of formulas you can use in excel. I have a project underway being built under Office 2021 - to then be migrated to SharePoint 365.
As I have typed out the title and checked some of the suggestions, much the same for the wider web, I am struggling to find a solution. In my head, it's pretty straightforward, but obviously, my skill stops there.
Here goes.
In one sheet called [Page4], I have an array of particular data, but I want to pull some common data from other pages (User, Group, and Location). So far I have been successful in drawing this information from one searchable sheet [Page1] using:
Name Manager:
Group = 'Page1'!$A3:$A999
User = 'Page1'!$B3:$B999
Location = 'Page1'!$C3:$C999
>> Page4
Cell A3, { =IF(B3="","",INDEX(Group,MATCH(B3,User,0))) }
Cell B3, Validation > List { =Matrix } [Matrix = (All users from all pages, remove duplicates)]
Cell C3, { =IF(B3="","",INDEX(Location,MATCH(B3,User,0))) }
So far so good... It is working great. However, other pages have been added, Page2 and Page3.
Now, I've tried multiple suggestions, and they all seem to work just fine - but nothing I can use.
Name Manager:
Group = 'Page1'!$A3:$A999
Group21 = 'Page2'!$A3:$A999
Group22 = 'Page3'!$A3:$A999
User = 'Page1'!$B3:$B999
User21 = 'Page2'!$B3:$B999
User23 = 'Page3'!$B3:$B999
Location = 'Page1'!$C3:$C999
Location21 = 'Page2'!$C3:$C999
Location23 = 'Page3'!$C3:$C999
Each page has a different purpose, so through the Matrix Page/Column which brings all the Users together, I would like to search all three pages for the relevant user data (Group and Location) as laid out in the Page4 Formula
I've tried, as an example:
Cell A3 { =INDEX(A3,((Group)*(Group21)*(Group22)),MATCH(B3,User,0)) }
Result: #SPILL!
The formula is in A3 and C3... doesn't need to be coming back as a range. The formula needs to be drawing information back to the same single cell [(A3) {Group} 03, belongs to (B3) {User} Carl and in (C3) {Location} Parkland].
The original formula under Page 4 works, I just need to expand on it to test more areas?
Personally, It does appear I am missing something, such as how to factor in the new search areas of the extra Names. All the data is physically there, the aim is to cut down the repetition through selecting one peace of data (B3) from a drop list (=Matrix), then the rest (A3 and C3) is then auto populated.
Any workable suggestions would be great and appreciated in advance.
As I have typed out the title and checked some of the suggestions, much the same for the wider web, I am struggling to find a solution. In my head, it's pretty straightforward, but obviously, my skill stops there.
Here goes.
In one sheet called [Page4], I have an array of particular data, but I want to pull some common data from other pages (User, Group, and Location). So far I have been successful in drawing this information from one searchable sheet [Page1] using:
Name Manager:
Group = 'Page1'!$A3:$A999
User = 'Page1'!$B3:$B999
Location = 'Page1'!$C3:$C999
>> Page4
Cell A3, { =IF(B3="","",INDEX(Group,MATCH(B3,User,0))) }
Cell B3, Validation > List { =Matrix } [Matrix = (All users from all pages, remove duplicates)]
Cell C3, { =IF(B3="","",INDEX(Location,MATCH(B3,User,0))) }
So far so good... It is working great. However, other pages have been added, Page2 and Page3.
Now, I've tried multiple suggestions, and they all seem to work just fine - but nothing I can use.
Name Manager:
Group = 'Page1'!$A3:$A999
Group21 = 'Page2'!$A3:$A999
Group22 = 'Page3'!$A3:$A999
User = 'Page1'!$B3:$B999
User21 = 'Page2'!$B3:$B999
User23 = 'Page3'!$B3:$B999
Location = 'Page1'!$C3:$C999
Location21 = 'Page2'!$C3:$C999
Location23 = 'Page3'!$C3:$C999
Each page has a different purpose, so through the Matrix Page/Column which brings all the Users together, I would like to search all three pages for the relevant user data (Group and Location) as laid out in the Page4 Formula
I've tried, as an example:
Cell A3 { =INDEX(A3,((Group)*(Group21)*(Group22)),MATCH(B3,User,0)) }
Result: #SPILL!
The formula is in A3 and C3... doesn't need to be coming back as a range. The formula needs to be drawing information back to the same single cell [(A3) {Group} 03, belongs to (B3) {User} Carl and in (C3) {Location} Parkland].
The original formula under Page 4 works, I just need to expand on it to test more areas?
Personally, It does appear I am missing something, such as how to factor in the new search areas of the extra Names. All the data is physically there, the aim is to cut down the repetition through selecting one peace of data (B3) from a drop list (=Matrix), then the rest (A3 and C3) is then auto populated.
Any workable suggestions would be great and appreciated in advance.