Need all cell values from a given row based on their column header

**mcmcspam** · 02-23-2024, 05:13 PM

I've been fighting with this for days and I'm pretty sure I'm doing everything wrong at this point.

Sheet Names

Users

Perms

Users

Column B, beginning at Row 3, has non-unique IDs that correspond to Perms!A:A

Row 2, beginning at Column H, has the unique Column Headers that correspond to Perms Row 1

Perms

Column A, beginning at Row 3, has unique IDs that correspond with Users!B:B

Row 1, beginning at Column E, has the non-unique Column Headers that correspond to Users Row 2

The cell values I'm looking to get at are TRUE / FALSE

What I Have
Mostly a headache...

What I Need
When a value of a given cell in Users Column B has a corresponding value in Perms Column A, check all Cells in Perms Row 1 that match the value of a given cell in Users Row 2, and return "Yes" or "No" if any intersecting cells are TRUE.

Example of what I have

Users Cell B3 = "wsiwaa" & Perms Cell A160 = "wsiwaa"

Users Cell H2 = "Red" & Perms Cells Y1, AX1, HP1 = "Red"

Example of what I need
If any of the values for Y160, AX160, or HP160 are TRUE, then say "Yes". Else, "No".

**Bernie Deitrick** · 02-23-2024, 06:43 PM

You should add an example workbook with some non-private data that shows your data structure and where you want the value returned... on Users, do only column B and row 2 have values, and the rest should be formulas?

**mcmcspam** · 02-23-2024, 10:09 PM

That's an excellent point - very sorry. What I have here is one row of data for each tab.

Users!B3 matches with Perms!A3

Users!H2 matches with various cells across Perms Row 1

Users!I2 matches with various cells across Perms Row 1

Users!J2 matches with various cells across Perms Row 1

For every Column in Perms that has a Row 1 value that matches Users!H2, I2, or J2, if any of them is TRUE I need Users!H2, I3, J3 to say TRUE, else FALSE.

I hope this helps!

sampledata.xlsx

**JeteMc** · 02-26-2024, 03:10 PM

In the sample data file cell H2 on the Users sheet displays Develop code, as does cell Y1 on the Perms sheet yet cell Y3 displays "FALSE".
Based on the description in post #3 rather than what is displayed in the sample file:
1. Populate cells E3:JS3 using: =AND($A3=Users!$B3,COUNTIFS(Users!$H$2:$J$2,E1))
2. Conditionally format E3:JS3 using
For red: =Cell Value=FALSE
For green: =Cell Value=TRUE
Let us know if you have any questions.

**mcmcspam** · 02-26-2024, 03:37 PM

I'm not sure I follow. Maybe my explanation wasn't as clear as I had hoped. Let me try again.

Users!B3 should match Perms!A3
-In the original dataset it's actually Perms!160, so there has to be some sort of lookup from Users!B3 to Perms!A3

Users!H2 should match multiple cells in Perms
-Perms!Y1, Perms!AX1, Perms!HP1
-These are essentially ways to group each column based on their Row 2 value beginning at Column E

Users!I2 should match multiple cells in Perms
-Perms!AB1, Perms!AE1, Perms!AG1 as examples, but there are more
-These are essentially ways to group each column based on their Row 2 value beginning at Column E

Users!J2 should match multiple cells in Perms
-Perms!F1, Perms!J1, Perms!K1 as examples, but there are more
-These are essentially ways to group each column based on their Row 2 value beginning at Column E

Users!H3 should be FALSE because none of the cells under "Develop code" in Perms!1:1 has a value of TRUE where they align with Row 3

Users!I3 should be TRUE because at least 1 of the cells under "Configure application" in Perms!1:1 has a value of TRUE where they align with Row 3

Users!J3 should be TRUE because at least 1 of the cells under "Perform Business User (BU) tasks (not read access) in the production application." in Perms!1:1 has a value of TRUE where they align with Row 3

**mcmcspam** · 02-26-2024, 05:02 PM

Ended up using a helper columns and a COUNTIFS I had to get someone I work with to help me put together. Ugh. Thanks all who took part in this debacle of mine.

Need all cell values from a given row based on their column header

LinkBack

Thread Tools

Rate This Thread

Display

Need all cell values from a given row based on their column header

Re: Need all cell values from a given row based on their column header

Re: Need all cell values from a given row based on their column header

Re: Need all cell values from a given row based on their column header

Re: Need all cell values from a given row based on their column header

Re: Need all cell values from a given row based on their column header

Thread Information

Users Browsing this Thread

Similar Threads

Sum row values based on a column header date

macro to lock cell by column header based on value in another cell by column header

Count the values in a particular column based on header name

Count Row values based on Column header

Changing column values based on header values

[SOLVED] Returning all the values in a column based on the column header

Returning a Column Header based on max values in a row, that update when values change

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions