Compiling a list from a long set of data

**dbs179** · 03-30-2007, 08:22 AM

Hi,

This is my first post on here, but I have looked over alot of posts yesterday to try to figure out the problem I am having. I have a workbook set up to compare a list of numbers from one sheet to three lists on three other sheets. I have worked thru that by doing some research here as well as a few other sites, but now I would like to add a new worksheet to give me a compiled list of all of the numbers that show up as "closed". I tried using one of the examples on here that I saw but was unable to get the offsets to work properly. The biggest problem, is that this list when it is done can be over 1000 rows per sheet, and it is a hassle to scroll all the way thru just to see which numbers on the "Master" worksheet are closed.

Any help with this, or suggestions would be greatly appreciated.

Thanks

Dave

**NBVC** · 03-30-2007, 09:48 AM

Can you attach a workbook that includes the "new" sheet and shows example of what you're after?

**dbs179** · 03-30-2007, 10:20 AM

Originally Posted by NBVC

Can you attach a workbook that includes the "new" sheet and shows example of what you're after?

NVBC, thanks for the quick reply. I have attached an updated version, with the new sheet added. I have manually input the numbers that are closed out from each agency. I would like for the "Close Out" sheet to automatically populate these numbers in the correct place. I hope this explains things a bit more.

Thanks
Dave

**NBVC** · 03-30-2007, 10:34 AM

Try this formula in A3 copied down and across as far as necessary:

=IF(ROWS($1:1)>COUNT(INDIRECT("'"&A$1&"'!$B:$B")),"",SMALL(INDIRECT("'"&A$1&"'!$B:$B"),ROWS($1:1)))

**dbs179** · 03-30-2007, 11:12 AM

Originally Posted by NBVC

Try this formula in A3 copied down and across as far as necessary:

=IF(ROWS($1:1)>COUNT(INDIRECT("'"&A$1&"'!$B:$B")),"",SMALL(INDIRECT("'"&A$1&"'!$B:$B"),ROWS($1:1)))

That worked great in my generic test, but once I put into the live sheet, it doesn't work. I renamed the tabs to generic names, and have substituted numbers to generic numbers on the one I uploaded. Can you give me a quick break down of what exactly that does, and where it is looking? If you can, I might be able to edit it to work in the live sheet. Thank you so much for your help.

Dave

**NBVC** · 03-30-2007, 11:52 AM

The "new" tab names must match the column headers in the Closed out sheet. That's what the Indirect() function does... it matches the column title to the corresponding sheet to look in.

I have also assumed, per your example, that the entries in each of the Agency tabs are in column B.

Also, I have assumed your entries in each the Agency tabs are numbers. If they are text, the formula will change to this:

Please Login or Register  to view this content.

and must be confirmed with CTRL+SHIFT+ENTER not just ENTER...you'll see {} brackets if entered correctly...then you can copy down and across...

Also notice that the formula now contains fixed ranges - $B$3:$B$100 (necessary for this type of CSE formula)...adjust those ranges to suit what your range in each of the Agency tabs might be.

**dbs179** · 03-30-2007, 12:46 PM

Originally Posted by NBVC

The "new" tab names must match the column headers in the Closed out sheet. That's what the Indirect() function does... it matches the column title to the corresponding sheet to look in.

I have also assumed, per your example, that the entries in each of the Agency tabs are in column B.

Also, I have assumed your entries in each the Agency tabs are numbers. If they are text, the formula will change to this:

Please Login or Register  to view this content.

and must be confirmed with CTRL+SHIFT+ENTER not just ENTER...you'll see {} brackets if entered correctly...then you can copy down and across...

Also notice that the formula now contains fixed ranges - $B$3:$B$100 (necessary for this type of CSE formula)...adjust those ranges to suit what your range in each of the Agency tabs might be.

First, let me start by thanking you again. The help you are giving me is awesome. I should have put in more representative data in the beginning, and for that I am sorry. Here is an updated sheet with all data that is similar to what I am using. I have put in the formula that you gave me and it only returns what is actually in the lists for each agency, not the ones that match the master list. If I only use numbers it works great, but if I use a combination of letters and numbers it does not. Is there a way to correct this? Thank you again for being such a huge help.

Dave

**NBVC** · 03-30-2007, 01:02 PM

Okay....perhaps I misinterpreted your expectations based on the original info....

Try this formula in A3, again confirmed with the CSE key combination....

Please Login or Register  to view this content.

again also adjusting the B3:B31 range to suit your real range in the Master sheet.

**dbs179** · 03-30-2007, 02:12 PM

That got it. Thank you so much for your help.

Dave

**NBVC** · 03-30-2007, 02:15 PM

That's great!

You're welcome!

Compiling a list from a long set of data

LinkBack

Thread Tools

Rate This Thread

Display

Compiling a list from a long set of data

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions