Gathering and listing data from another sheet

I have a sheet in my workbook containing daily records of input and production data. The records are each 31 rows deep, and are identical in format. They are also linked to each other to produce running totals. I set up more records by simply duplicating the 31 rows in a continuous manner down the worksheet.

I also have another sheet on which I want to draw up "Load Out" reports. On these reports I want to amalgamate certain data from the records out of a single column, and list it within a designated space on the report. The records I want to extract the data from will be determined by date parameters on the report. So, I want to create a single list of data from the various records according to set date parameters. Please note that there will be blank cells between many of the data cells in the records, and I want this process to ignore these. (Actually, the cells all contain formulae, but some will not be displaying any data - these are the 'blank' cells.)

I don't know what sort of operation can do this - is it a database function? I think I will attach a copy of the file so you can see what I am trying to do. The column in the record sheet that the data comes from is 'CH'. The Load Out sheet shows two sections highlighted in yellow - the place where the date parameters are set, and the panel for listing the data from the records. I have only put one record in the sheet because of space limitations on uploading files to this site, but you can duplicate them down to get more if you need to. If need be, you may reply directly to my email address jonathan@gloriavale.co.nz

If you don't understand anything, just holler!

Thanks very much.
Jonathan

I am not sure what you are trying to do, friend. Can you explain it very simply, ie I am trying to add up all the XXX that we received between date1 and date2, and put the answer in cell nnn of sheet sss

OK, let's try again!

I will assume you have looked at my attached file. The sheet marked 'CLIP' contains the daily records. In the file I have only put one record because of uploading limitations, but these records would be duplicated down the sheet for each day. The sheet marked 'Loadout' is the report sheet, and that is where I am trying to transfer the information to.

So, to put it simply - I want to transfer the data from the panels marked 'ED Numbers' in column CH of sheet 'CLIP' to the panel marked 'ED Numbers' in column L of sheet 'Loadout'. However, understand that the panels in sheet 'CLIP' which I need to access data for any given report will be determined by the dates set in the report. So, each report will need to access the ED Number panel in column CH from several different consecutive records.

I hope this is better!!
Jonathan

Hey Robert111 - are you still out there? I haven't heard back from you! Is there still something you can't understand?

Jonathan

Still a bit confusing

I think what you are trying to say is that you want to pick up a list of any of the "ED numbers" from the CLIP sheet that are associated with orders where the supply dates fall between the production start and finish dates.

Is that correct?

Hi Mark,

You're not confused at all - top marks - that is exactly what I am trying to do! So the production dates control which ED numbers are collected from the CLIP sheet.

Looking forward to your reply!
Jonathan

I had a look at your sheet, and (after fiddling for some time) have come to the conclusion that the only sensible way is via a VBA macro.

I will have a go since I only just started VBA recently and the practice will be good for me, but I can't promise a quick response.

Mark.

OK, that's fine, I'm just glad that somebody is able to help me with it! I thought a macro of some kind would be needed, but I have not got into that stuff yet.

Thanks for trying!
Jonathan

I looked at this afresh this morning, discarded all I had done and started again.
I think that I have managed to solve your problem without resorting to VBA.

I have introduced an extra sheet to do the various lookups etc involved, I suggest that you "hide" this sheet when you have satisfied yourself about it's workings.

I'm not entirely sure that I have got exactly what you want (am I using the correct dates etc?).
However, you seem pretty EXCEL literate so I expect that given this starting point you should be able to mould it into the form you require.

Let me know if you have any difficulties

Mark.

Dear Mark,

I got your reply by email, but I can't find it on this site! Have you made some attachment? If so, I can't see anything!! What is going on here?

Jonathan

he has put in an extra work sheet called select, this contains his look up tables.

Sorry I couldn't help, but I could not understand what you were trying to do, it is hard to describe in words something that you are very familiar with, eg the jargon such as ED Numbers, to someone totally unfamiliar......