Results list based on lookup criteria

anthony85

New Member
Joined
Jan 17, 2014
Messages
22
Hi,

I was using a pivot table for a dashboard summary but due to a change in code, there are some certain fields that won't display correctly as it's now text format and not date and time etc.

Anyway, I'm good with the basic lookups but not with index and match etc which is what I think is needed for this...

Basically I want a list to be populated based certain criteria.

Example, I have a spreadsheet of 39 columns and on going rows. Column E is what I want to be displayed in this list, but only to be shown if both columns T and AA are both blank. At any point in the day there could be between 10 and 15 cases that meet this criteria so I don't mind having a column full of formula on my dashboard.

Hopefully I've explained this well enough for you good people to help me.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Basically I want a list of column E if it's relating cells in columns T and AA are blank. From an example I saw, if only a few results are shown then the formula error is returned. I don't mind this as I can just conditional format that to white.

Case Refs where cols T and AA are Blank
Testcase1
Testcase2
Testcase3
#NUM!
#NUM!

<tbody>
</tbody>
 
Upvote 0
If your dashboard column is on Sheet 1 and your data columns are on Sheet 2, place this formula in A1 (example only, you can place it where you want):
=IFERROR(IF(Sheet2!T1&Sheet2!AA1="",Sheet2!E1,""),"")

Copy down as far as needed to cover the number of rows on your data sheet2.
 
Upvote 0
If your dashboard column is on Sheet 1 and your data columns are on Sheet 2, place this formula in A1 (example only, you can place it where you want):
=IFERROR(IF(Sheet2!T1&Sheet2!AA1="",Sheet2!E1,""),"")

Copy down as far as needed to cover the number of rows on your data sheet2.

Hi Ken, the only problem with that is that I would need just as many rows on the dashboard as the actual data. And could possible show no results until way down the list.

I would want the formula to find the 1st record that matches the criteria, then list the 2nd and so on. I think it may be an array formula but I'm not sure.
 
Upvote 0
Try placing the formula: =IFERROR(IF(T1&TT1="",E1,""),"") in a helper column on your data sheet and copy down. Then, on your dashboard sheet, set up a PIVOT TABLE that references that column as your source data. Place the named column in the pivot table Row Labels section. You will need to "Refresh Data" when you wish to see the pertinent information, but it should only show the rows that have data from column E and nothing in columns T and AA.
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top