Copying certain cells into a list based on criteria

[Mummabee]

I have a struggle with listing some info from one workbook to another.

On one workbook I have a totals pages of all the works codes. So its a list of S1-S77 (excluding S56-S65 as we no longer do that type of work) and it totals how many times this has been entered throughout the entire workbook, listed in BS:BT.

On a separate workbook I use for billing, I need to manually input the works code for it to automatically lookup the cost, quantity etc.

Is there a way that I can get the billing workbook to list only the codes that have a total next to them from the totals page?

The totals page is on each workbook, I have around 50+. On the billing workbook there is a worksheet for each one of these (to be able to bill the correct people). Its quite time consuming.

Many thanks

[Pete_UK]

It would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon, as this does not work on this forum.

Hope this helps.

Pete

[Mummabee]

Unfortunately, I can't due to the information contained within them. I would get into a lot of trouble!

I'll try to explain as best I can.

I have Workbook 1, this is one of the counties that we cover. Within Workbook 1, there are many tabs for each town within the county. We cover several counties so I have a fair few of these, but for this explanation I'll just stick to Workbook 1.
Then I have Workbook 2, this is where I do the billing from. There are also many tabs in this as it covers all the counties.

For works we do on our assets within these counties, they are given a code (S1 to S77). Each code is noted on the individual towns Tab within Workbook 1. To make this easier I have added in a Totals Tab and the beginning of Workbook 1.

In column BS it lists all the Works Codes (S1 to S77) in column BT it will total all of the codes used throughout Workbook 1, as below.

BS BT

S1 2
S2 0
S3 10

I have to go to Workbook 1 Totals Tab, take note of any Works Code with a value next to it. So using the example above, I would note down S1 & S3, ignoring S2 as its 0. I then go into Workbook 2, find the correct county, in column A I write in the Works Code (S1 & S3).

It sounds easier than it is but, there is a lot of codes and a lot of counties, it takes a very long time.

Is there a way I can in Workbook 1 Totals Tab, highlight only the codes that have a value next to them (S1 & S3 in this example), then in Workbook 2 it lists only the highlighted cells, from Workbook 1 Totals Tab, down column A.

Hope this is a bit clearer

[JeteMc]

Here is a representation of the two workbooks as I understand from the description.
The array entered formula* in book2 that populates the codes from book1 which have a count of greater than zero is:

Please Login or Register  to view this content.

If the formula won't work for your situation, please modify Book1 to show the setup as closely as possible without including sensitive information. Also modify Book2, if necessary, to show the desired output so that we will know what the formula/code should produce.
Let us know if you have any questions.

[Mummabee]

Good morning,

Thank you for your reply. Unfortunately I couldn't get it to work (probably just me)


I have attached an example.

The CountyBook has the tabs off all the different areas and totals all of the codes within this area. I use that total as the quantity for billing with a vlookup. Each area (generally) have their own billing sheet (there are maybe a handful of parishes that are billed under the same account number, but they would still have their own tabs in the CountyBooks)

The BillingBook, nearly everything is auto filled now, the only thing is the work codes. One a work code is entered, everything fills in but there are 67 work codes and many areas/account numbers.

If there is a way to be able to auto-fill anything with a value over 0 on to the BillingBook, under the correct account number for that area, I would really be so grateful for any help.

Hopefully I'm not wishing for the impossible, if I am I still appreciate you taking the time to look.

[JeteMc]

I feel as if I am missing something, and may need to see manually typed representation of your expected output, especially the "parishes that are billed under the same account number, but they would still have their own tabs", however...
To fill the Code column try:

Please Login or Register  to view this content.

To fill the Qty column try:

Please Login or Register  to view this content.

I am only uploading the updated copy of the BillingBook as no changes were made to the CountyBook.
Let us know if you have any questions.