Excel formula help

[Hazra Hadee]

Hello,

I have a sheet with data like first name, last name, mobile, and email. Plus I am entering yes and no for each person/row, e.g. is this person an admin? is this person a volunteer.

If the answer to "is the person an admin", Yes, then in another sheet, I want to grab the name, mob and email address. What is the best formula to use to grab the data for these fields? Please help.

I tried using the IF formula, it gets me the information, but it does leave empty rows in the new sheet.

Appreciate it much

 

[PaulF]

Hazra Hadee,

Take a look at the file I uploaded for you. I added a helper column and used a ROW() MAX() combination to sequence your Admins for lookup. I named the table and added a named range of cntAdmin (look in name manager). On the lookup page there is a combination of index/match to retrieve your data that runs down to line 500. I used some conditional formatting to pretty up the data on the lookup tab. If you don't want the helper column/data visible just hide the column.

Respectfully,
Paul

 

[Hazra Hadee]

Thank you very much. I understood the formula for the source sheet. However, I am not able to get understand what you did in the lookup sheet. Column C in the lookup sheet has a different formula and the D:G has another one. Is it possible for you explain it briefly? I appreciate it very much. Thanks a million.

 

[PaulF]

I sure can... Once you have the sequence set on the Source page, we can use the formula in "C" to start:
We start by looking at: ROW(A1) - ROW(celREF) will return the ROW# Portion so A1 = 1, A2 = 2, we can use this in formulas to get a sequence of numbers to lookup. We use this ROW(A1) = 1 as the ROW input to the INDEX/MATCH Combo. INDEX against the LAST Name Column, and MATCH the A1 ROW to the HelperColumn to return the Last Name that is in Helper Column #1. D, E, & F could work the same way, I just switched and used the Last Name as the ROW match in the INDEX/Match combo function.

I hope this help, but please ask more questions to understand... This is a common problem that you will need to solve in Excel.

Option B = Quick Pivot Table with a filter. I attached it. I set layout to Tabular and turned off subtotals and grand totals.

 

[Hazra Hadee]

Thank you so much for explaining. I am going to work out the formulas again to see if I have understood them. Will come back and confirm once done. And the pivot suggestion was great. However, I would love to do it with formulas so you don't need to refresh the sheets then. Thanks a million.