# Link Sheets together



## woodland81 (Dec 27, 2010)

Hello

I think I want to use a Sheet as a Template, then link them all together and push the totals into a separate spreadsheet.
Im after some advice to make my work…well much easier. In theory I have an idea, not sure if it will be easy to do and make life simpler.

A brief description of what the task is, is that for outgoing post, every office has to complete a Sheet to say how many envelopes of each kind they are sending. We have to log it online with the Post company. To do this I group all the different kind of postal types together, merging in all the totals from every offices sheet and put the numbers in spreadsheet PPI Guide.

At a later date, I enter in another spreadsheet, the total postage cost per Office, this involves calculating the based on the Post Sheet, each day.
Now because my PPI Guide Spreadsheet has the 'Set' prices in (originally just for my info to see my totals match up), my idea is to copy the main sheet in PPI Guide into many separate sheets in the same excel, one for each office.

Then have one Main Sheet, which is again the same template, however has combined totals for each Sheet.
Is there any easy way to get a Main Sheet to Pick info from the same cell and multipe sheets. I know how to make it do it for 1 cell, but id imagine if I needed to do it 50times, formula would go on forever….maybe theres a simpler way.

Then from the Master Sheet, Id want this information Fed into the Recharge spreadsheet, that days Cost per office inserted automatically. I know how to do that, but when I send that sheet onto someoen else, the originalss wont be attached, is there a way to freeze the data so that it remembers the totals and doesn’t lookup?

Hope ive made sense and someone can push me in the right direction


Thanks


Lee


----------



## RSpecianjr (Jan 20, 2010)

LEE!!!

How ya been buddy?

So, my first suggestion would be to use a database instead of Excel. Access or SQL offers a lot of flexibility when dealing with multiple input points. You can store it all in one central location and have real-time updates as each office puts information in. Also can be done through a website or something like that for easy access.

That being said, I wouldn't know the slightest where to start on doing that. I've dabbled in Access and a little bit in Oracle and SQL, but nothing enough to be helpful.

Alright, so back to Excel. The best thing to do is plan ahead, especially on a project this big. Ideally you will want the layout of all the information to be uniform for each office, by using a template as you pointed out. This will allow you to know where the data you are looking for is, without having to actually open the workbook up and look for it.

Next, your main workbook will need to pull that information from the templates that your offices use. This may depend on how many offices you have and how much information you need to pull from each template. Pull as little information from the templates as you can, because this will slow down Excel quite a bit. 

Store all the templates in a central location, so in a single folder on a shared drive. Though not impossible to have them in different folders, creating the lookup formulas will be a lot easier if you do this.

Use password protection and lock down the cells with vital information for your database. Formulas and what not. Basically, only allow the offices to change what they need to change, last thing you want to do is take time on multiple workbooks just trying to figure out what happened.

Figure out the layout of your main workbook and have a single worksheet dedicated to looking up the data. Next I would have a worksheet for archiving data. Repeat data... meaning if you have two instances falling under one date, list the date twice. This will keep things very organized and easy to lookup. 

Ultimately, I would recommend having a macro do the archiving of the current days statistics into the archive worksheet. This macro can also easily distribute the data to your Individualized worksheets.

Sort the Archive worksheet by date, then by office. Again, this can be built into the macro.

Use dynamic named ranges. This will reduce the amount of work you have to do and allow you to reference data sets for particular offices, dates, whatever.

Last thing would be to have a Summary worksheet. This will allow you to look at the postal usage for the entire company, at a glance.

Now, if you really want to impress your bosses... You will already have archived data. You can do trending charts, comparison charts and cost analysis charts. If you have Outlook, you can integrate this into an email and send it automatically (again, macro).

Thats what I would do anyway. = )

If you have specific questions, let us know.

Regards,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

Hello,

Yeah I been alright thanks, just full of busy. i still havent managed to complete my last excel project due to 
time, plus realisation excel has a maxium column amount ha!

Your right a lovely online system

Ive been playing along this evening using my existing template and building it upwards. It may make more sense the way ive got it now to what im trying to find. A couple of things you mention I think im on the right track and covered them. especially with my ranges. if a price change happens, all i do is update master sheet and cascades down into the other worksheets : - D

Ultimately I was using this template to collate information, however it seems I can use it to do some work for me.

The PPI Guide spreadsheet, I would have to keep one of these per day. and each sheet in the workbook refers to an office. Ive decided to name them as numbers which makes the SUM ranges much easier to do rather than coding names?
For my reference ive coloured coded the Sheets, this allows me to know which Location they are. As I will have a sheet per office to manually input in. The master sheet then Collates them all.

As its for just the one day, again over a month I will want to extract the Total Price, per worksheet, with its corresponding office(same cell) for every day.

My playing around to see how things work is on the PPI Recharge Sheet, is 'Dragonville' Sheet- 31st May Entry line.

Again playing around, I think - =[1.xls]Master!$C$6+[2.xls]Master!$C$6+[3.xls]Master!$C$6 will do the trick
. Ahh just realised i need to link one cells info from one excel to another, rather than totals, as my recharge sheet will have a line per date.ooops.got ahead of myself whilst playing around

I think when I will have 1 sheet per day, I will have to have subfolders for Months as I will have to name the Sheet by Day and Month too. otherwise id be lost lol

I do like the sound of creating some Summary Sheet. 
Maybe to show Monthly which section has spent the most on Postage, comparison on their previous month etc


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

My biggest suggestion again, would be to house all your core data on one worksheet. Then you can have another worksheet dedicated to pulling informaton based on whatever criteria you want. It can have a layout for however you want it to display. The layout can even change based on the selections (ie month, week, day etc). It is just a much cleaner and sleeker workbook this way. The data will be pulled in individually. Once you have all the data in the main workbook, you can add or play with it however you need. = )

As far as coding goes, the worksheets all have a number associated to them, so a loop is really easy to do. What could be a decent or huge sized formula can be made much easier with a few (as little as 3) lines of code. If you are serious about Excel and want to get into how much it can do, VBA is where it is at. If you thought Excel could do a lot without knowing the power of VBA... just wait, you'll be amazed! = )


Regards,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

So you think rather than duplicating sheets. I should have that template grid, just 50 or so next to each other in one sheet?
Example attached

Im worried with the vba etc that because I dont know enough that i will get so far down the road and not complete it just like my other project


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

I'll put something together for you today to show you what I mean. Shouldn't take me long. Also, at this point, shouldn't need to use VBA. Can you provide a list of your offices? Morrison Busty... etc

Regards,

Robert D. Specian Jr.


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

I've spent a little bit of time on it. This is the direction I would go in. If you are using 2003, we will have to change the SUMIFS formulas.

I attached a xlsx and xls version just incase it removes the formulas.

Regards,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

Hi there,

As usual, your effort is so very much appreciated. im using excel 2003, not sure what it is at work

List of Offices.


Well its quite alot. Its based on locations & then the offices there.


Here it is and subject to additions


Location-Dragonville
Licensing
Env Health
Env Services
Clean and Green
Direct Services
Fleet
G. Pine
C.Crathorne
Durham City Housing


Location-Morrison Busty
Morrison Busty Garage
Care & Connect
Morrison Main Office
Bereavement


Location-Consett CAP
Consett CAP Customer Services
Consett CAP AAP
Consett Licensing
Consett Fraud
CAP Funding


Location-Stanley Front Street
Stanley Family Intervention
Stanley Front St Customer Services


Location-Claypath
Planning
Housing
Building Control
Heritage Design
Care Connect
Durham AAP
D City Vision
Development Control
Customer Services
Mayors Office
Payment Services
Rent
City Info
TIC
Revenues


Location-Seaside Lane
Street Scene
Housing Solutions
Benefits 
Revenues 
Building Control
Planning


Location-Seaham CAP
Customer Services
Revs & Bens
Benefits
CYPS Safeguard

Location-Spectrum
Housing Renewal
Strategic Investment
ENIT
Care Connect
East Durham AAP


Location-Teesdale
Planning
AAP
Building Control

Location-NHOOD C.Hall
N/House - Co/Durham Sport
N/House - Sport & Leisure
Business Support
Road Safety
Strategic Highways
Strategic Waste
Annand House - Licensing
Northern Area
Southern Area
Policy & Performance
EHCP
Annand Customer Services
Facilities



Location-RED C.Hall
Passenger Services
A. White
Design & Con
Dev Control
Econ Policy
Funding
G. Hall
Heritage Coast
H. Adoptions
Highway Design
I. Thompson
International
Landscape
N. Management
NPP
P. Policy
ROW
S. Timmis
S.Robson
Strategic Transport Planning
Sustainability
Traffic
Workforce support


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

Alright, I'll see about changing the formulas to sumproduct instead. 

For the locations, I'd make a two-step validation list. One for selecting location, one for selecting the office. If you add a column for location in the Archive woksheet, you can easily pull locational information together. So, you will have three display Worksheets...

Main - Overall Company Shipping
Location - Overall Shipping for a specific location
Lookup - Overall Shipping for a specific office

These are just suggestions though, so please tell us what you'd like. = )

Regards,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

thats a good idea, infact i thought of that as a drop down list scenario,location,to then show offices. when i was going to sleep last night, meant to get up and write it down but well.....fell back to sleep haha

From entering the data in each office template. I will need to have daily totals of envelope use types rather than the price total, but then once i get my Mail invoice will need to collate whatever the date range on the invoice is. e.g morrison busty-main office price total for each day totalled and stored somewhere. just like my PPI recharge sheet, not sure if this sounds like it will work for that, been on different excel projects all day, going bit boggle eyed.


----------



## RSpecianjr (Jan 20, 2010)

Hey, I adapted the workbook with sumproduct formula but it is on my computer at home. I'm on my way to ny right now, just didn't have time to upload it (and just forgot). I'll upload as soon as I get home, probably a week :/


----------



## woodland81 (Dec 27, 2010)

oh right ok thankyou for helping. enjoy NY


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

Here is the workbook I told you about. Sorry for the delay.

I didn't update all the formulas, but I did put one formula in there to show you how to. You will just copy and paste that formula and replace the SUMIFS. SUMIFS is just a 2007 advent, so, 2003 doesn't recognize it. SUMPRODUCT works though, on the lookup worksheet in yellow, you will find the SUMPRODUCT formula.

Hope this helps,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

great thanks very much. how was your trip to NY?

Ive been looking thru the sheet, but embarrassed to say I dont have a clue what its doing.

Ive selected different locations, then pressed the next drop down box which then gives me an error and stays on Morrison Busty.

"the value you have entered is not valid, user has restricted values". when i press cancel, it then seems to work allowing me to select the offices from that location.

The bit I dont understand is entering my daily data..in the lookup or main, where do I put it and where does it then get stored if im gonna do this each day for each location & section.

All the fields display to me #NAME? or #NA

:sigh:


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

It was for a funeral, so it wasn't the best of reasons, but it was good to see a lot of people I haven't seen in a while.

All data will go in the Archive Workhsheet. Lookup and Main are just to "display" the information.

The Data Validations are based on a range and a named range. Location is just AE1:AO1 and the named range is as follows:



> =OFFSET(INDIRECT(ADDRESS(2,MATCH(Lookup!$L$5,Lookup!$A$1:$AS$1,0))),0,0,COUNTA(INDIRECT(LEFT(ADDRESS(1,MATCH(Lookup!$L$5,Lookup!$A$1:$AS$1,0)),3)&":"&LEFT(ADDRESS(1,MATCH(Lookup!$L$5,Lookup!$A$1:$AS$1,0)),3)))-1)


Basically, it will find the name selected from the Location. Then it will produce a list of choices based on the offices listed under that Location (locations are on the Lookup AE to AO). If any of the formulas used in the Named Range are not available in 2003, it will probably produce the error you are seeing. My guess is COUNTA, but I don't have 2003 to verify it.

You will have to update the formulas with the SUMPRODUCT formula adjusted for the cell. I've given you an example of how it needs to be setup. The only thing you will have to adjust is the column letters based on which section it is for. You can lookup the column on the Archive worksheet. If you have any questions, please let me know or if someone else would like to adjust the formulas, please feel free. It's just a manual process that I don't have much time for at the moment.

Regards,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

Oh no im sorry to hear that, i guess its a time that does bring alot of people together who dont always have their paths cross.

So my daily data I have to manually input in Archive? should the location be a drop down list so that I allocate, which one I want to add my data too. also it doesnt include the date so how does it who the day to enter it to?

In Sheets Lookup and Main, each cell with SUMIF in I need to put the Formula that is present in L11?
Can I not auto filter it down and then manually delete out the blank boxes that were not being used?


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

Yes, the input worksheet is the Archive sheet. Just follow the format that is in there.

It doesn't really matter what order you input the data. You can put a validation in there for the location, but I don't really think it is necessary since you will be copying over the information from another workbook.

I'm not sure what you mean by:



> also it doesnt include the date so how does it who the day to enter it to?


Can you elaborate?

SUMIF should work fine, SUMIFS on the other hand isn't in Excel 2003 so you will have to duplicate and edit the formula in L11 for the SUMIFS cells. 

Thanks,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

I dont know what it is but i must be totally missing something simple, i dont understand anything, totally bamboozled
The inputting and retrieving.
On archive I entered location claypath but obviously couldnt specify claypath- planning, added 5000 letters. then on lookup its only based on me calling it the location, rather than location-subgroup section.

Even messing around playing with the formula putting into other columns, cnt work out the references


----------



## woodland81 (Dec 27, 2010)

oh i think i made progress. on archive location entered really has to equal individual office.

Just realised an issue, for example with me having multiple offices called 'Planning' it scuppers it up, with Planning being at many locations.is there a way maybe for linking up Location and office on the archive-input otherwise id be combined all 'planning' together regardless of location, if you get what I mean.


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

Okay, what I was saying before is I think the issue with the Office selection is the named range. In the named range, there is a COUNTA. I believe that is what is tripping it up in 2003. If someone else can verify that, I'd appreciate it.

COUNTA is used to avoid blank cells in the dropdown. You should be able to adjust the named range:



> =OFFSET(INDIRECT(ADDRESS(2,MATCH(Lookup!$L$5,Lookup!$A$1:$AS$1,0))),0,0,COUNTA(INDIRECT(LEFT(ADDRESS(1,MATCH(Lookup!$L$5,Lookup!$A$1:$AS$1,0)),3)&":"&LEFT(ADDRESS(1,MATCH(Lookup!$L$5,Lookup!$A$1:$AS$1,0)),3)))-1)


To something like:



> =OFFSET(INDIRECT(ADDRESS(2,MATCH(Lookup!$L$5,Lookup!$A$1:$AS$1,0))),0,0,10)


Just change the 10 to however be equal to or greater than the maximum number of offices under one location. So if the biggest location has 15 offices under it, change 10 to 15.

Named ranges (I believe) can be found under Insert>Name. I cannot verify this though, since I don't have excel 2003 = /.

I hope that is what you are talking about. Otherwise I am lost too haha.

I think you are right about the Office not being on the archive. Sorry, my mind must be more out of it than I thought haha.

Select column D on the Archive Sheet and add a new column. This will be for the Office. You will have to adjust the Unique ID formula to use the office instead of the Location. Last thing you will have to do is adjust the lookup formulas found on the Lookup worksheet. Just change anything that says Archive Column C to Archive Column D. 

If this is just absolutely and utterly confusing let me know and I'll see if I can make the changes or find someone that can.

Regards,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

Hi, i cant seem to find any sign of that formula in any cell. this is how I learn. I see what is happening, then play around and see what my changes do and that way know what makes excel formula do what they are doing. im doing this with your yellow cell to try and make some of the others work and bring back data from the inputting sheet.
Each time I think im making progress and get the dreaded NA


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

The formula I keep referring to is a named range. It won't be in a cell but it will reference a range of cells. I'll see about changing it when I have a chance.

Regards,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

hmm changing the formula i now get a figure but not whats stored lol.so when i copying n pasting this formula into each and every cell

=SUMPRODUCT((Archive!C5:C65500=Lookup!L5)*(Archive!B5:B65500>=AC2)*(Archive!B5:B65500<=AD2)*Archive!E5:E65500)

I should only be changing the E5 to suit the area where im extracting the letter type? the others stay the same?


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

Archive!E5:E65500 will change based on what information you want to pull from the Archive Worksheet. If you added that other column, Archive!C5:C65500 will change to the actual Office Column in the Archive Worksheet (currently set to the location column).

Regards,

Robert D. Specian Jr.


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

I've updated most of the formulas, including the named range formula. I don't understand why the named range wouldn't work, all the formulas used in it are used in Excel 2003. Check this out and see if it works better for you.

Regards,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

Hi there, 

This looks amazing now the formula functions :grin:
ive been comparing the previous formula i dont see difference but i certainly couldnt get it worked.

Ive noticed when I action selections in drop down lists etc at bottom i see it 'calculating' which is quite slow before it updates the lookup page, not sure if thats an issue with my PC at home.

On the lookup forms at the very bottom I have a box for special,airmail, and also want one for misc. these are boxes i manually enter monetry value. If I add these columns into Archive BM, BN, BO. which formula do I need to adjust on the main & looku.

Have been thinking about the Inputting bit (archive), is there a way to use the existing grid thats on the others ,maybe dropdown list date,location,office. enter my envelopes for that selection and so that it doesnt mean the 'lookup and main' dont need redoing, somehow make it Post it into the Archive sheet. With the amount of offices I cover, the constant scrolling all the way to find the envelope type per entry would get annoying. I know for a quick fix how to freeze a pane but wouldnt solve the scrolling.

I hope i making sense, getting exciting with things i can see it doing and if its even possible. :laugh:


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

It's all possible. If you can think of it, you can do it! haha Just a matter of knowing how.

How are you inputting the data? I was under the assumption you were getting this data from the different offices. If you are, then you should be able to just pull it over from those workbooks, removing any manual changes to Office, Date, Location etc.

You can use Data Validation. Just copy the formulas from the Data Validation on the Lookup worksheet. You may have to specific the Lookup worksheet as the source within the formulas. (ie instead of AA1 it would be Lookup!AA1... or whatever)

This is likely your PC. It only calculates as fast as your PC. It takes about a second on my crappy home laptop and slightly faster on my work computer. Trying closing out unnecessary programs and see if it runs faster for you. Might also be 2003, but I doubt much changed between 2003 and 2007 on calculation speed.

I did add the Office column on the Archive Worksheet, that is probably the difference you are looking for.

Regards,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

Hmm well I get a paperbased sheet per office. This has marked on how many of each type of envelope, they have provided, some i have to manually weight to enable me to put it in the correct category. These sheets I was previously entering into one of those Grids as combined for the day, but now i will have to do it for each sheet.

So in archive I will manually have to write it many sheets per day and thats why if thought if the layout was like the grid its simpler to rattle through my sheets instead of the constant scrolling. So somehome I could select my dropdowns for offices and day, enter into the grid and then it posts it into Archive, which in turn works for the 'lookups'

The info in the lookups is what I need to pull into completely different spreadsheet at a later date to charge offices for their postage. not important now as it will just be extracing rather than manually calculating.

You lost me on the data validation formula, i looked at menu-Data-validation' incase something fancy excel does in there.

i just been fidding around to see how formula works so I can code in the 'AirMail' & 'Special' boxes, i think im going to at least achieve that :grin:


----------



## woodland81 (Dec 27, 2010)

excellent managed to code those two boxes to bring back the totals. I noticed on the lookup page, the daily and weekly totals work but monthly didnt, fixed that, the copied formula ranges in total just needed to be adjusted to suit, phew!


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

Look at you! Becoming even more of a power user everyday!!

If you post the updated workbook I'll add something I bet you will like.

How many sheets might come from a single office for you to input at one time? 
Do you do them all at once or do you get them in spurts?
How will the billing work for your offices? Will you be sending them daily, weekly or monthly bills?

Regards,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

I someone times get multiple sheets per office in a day, however i usually collate and add them up myself to one sheet. so that should solve that issue.

I usually do them 1day behind so would enter them all at the same time, technically the mail company want us to use some of this info and give to them on the day but its impossible, so far havent kicked up a fuss.

The billing of offices happens based on when mail company send invoice, unfortunately, its never the same range of dates,
e.g 1st-31st. sometimes its 7-16th 21st-25th. ive no idea how they do it.
Its as and when the official invoice arrives, that i would then go ahead. In my other spreadsheet i then add the date ranges and add in the total spend per day per office.
So using this sheet I could go through select the date, office,location, gain the total for that add and put it in my other spreadsheet heet

In my attachment, the other sheet i put totals in is ppi recharge.


----------



## woodland81 (Dec 27, 2010)

haha oh dear i take a few days off then check my work emails to find lots and lots of data to be entered for post boo!

Hope they appreciate effort looking into this out of work:wink:


----------



## woodland81 (Dec 27, 2010)

what is it I will really like? what does it do


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

I started working on it at home. It's a custom form that you will be able to use for input control.

Regards,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

oh that sounds interesting. Probably more automated than by original template design
On the current input form- archive. when I first enter date and try to move to the next field, the calculate comes up at the bottom with percentage and takes about 10seconds to work its way up to 100%, the other fields dont take this long but there is a noticeable delay


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

SUMPRODUCTS are pretty calculation intensive. We can work around that a few ways, but when I am done with the form let's see if you like the way it acts. I'm pretty busy today, but I will post the layout when I get home for lunch (around 1230 central time). I can add in checks as well, so once you see the layout, just let me know what you want.

Regards,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

ok great really appreciate everything your doing.
I am learning little bits too:grin:


----------



## RSpecianjr (Jan 20, 2010)

Bah, didn't have time to upload it. Too many errands, I'll post it tonight = )


----------



## woodland81 (Dec 27, 2010)

no worries, looking forward to seeing


----------



## RSpecianjr (Jan 20, 2010)

Alright, as promised, here is the workbook with the form. The coding still needs to be put in, should be able to get to it tomorrow.

Thanks,

Robert


----------



## woodland81 (Dec 27, 2010)

huh I dont see a form? is it just the same as before "archive"?
I tried the button but it comes u pp the macro cannot be found. i tried assigning a macro to the button.

I found it in the View Code bit the form? :smile:


----------



## RSpecianjr (Jan 20, 2010)

Lee,

Hit Alt+F11 then on the left hand side, there is a folder labelled Forms, in that folder there is a form called UserForm1. That is the form I am referring to. = ) I am coding it as I send this to you, so it shouldn't be too much longer before I get a working beta.

Regards,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

oh yes i see thats a shortcut to the VBA coding area.


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

I finally got around to it. Went through and fixed it once and didn't save before someone else came in and shut everything off (bah, silly peoples!!). Oh well, got it done.

On the Archive Worksheet, you will see a button. Click on the button and it will pop up the form. Before submitting, it will check to make sure you enter the Date, Location and Office. If any of those three are not entered, it will not proceed.

You are entering a lot of data so I setup a 2 tiered tab system (easier to see than explain).

All default values are set to 0 to prevent you from getting #Value errors on the lookup formulas. This also prevents you from having to manually enter 0.

Play around with it, let me know what you want to change. I am sure you will find something .

Regards,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

Hello Robert,

Haha havent we all done lots of work and then before had the chance to save i, it disappears off into cyberspace without trace.

I noticed a few things whilst playing around.No matter what Location is  selected, the office lists stays the same, it seems to default and only list what would appear if location 'Claypath' was selected.

On the form too, I need to have a couple of boxes where rather than entering a total number of envelopes it would be a monetary value. This is for Air Mail, Special Delivery & Misc, on the main and lookups, this at the bottom of the table. On the Archive tab - BM, BN you can see how i was fiddling on and coded the money value entered in them cells to appear in the Grid pages, in the correct boxes, this was when I was learning what tells it to go in which box : - )

I thought the date format in the date box with it being M/D/Y seemed to be causing an error on the lookup daily grid, it wouldn't find anything when i tried to look for something i could see in the stored archive, tried to be clever and change the code to d/m/y in 'Y' format but caused more trouble haha oops.
Submitting the data too, leaves it to calculate for a good 15seconds on me home pc, will have to try work incase my pc is too weak


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

The RowSource was just referencing the wrong place, I've changed it and added code to make sure it changes.

I've also added another tab with the Air Mail, Special Delivery and Misc for you.

I fixed the date problem as well.

The SUMPRODUCT formulas are what is taking it so long to calculate. When new data gets entered, they have to recalculate. I can look into putting everything into VBA for you, it should speed it up, just takes a bit more time to code. (if you had excel 2007, you could use sumifs which calculate much faster )

Regards,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

Thanks very much. unfortunately at home and work we are on excel 2003, god knows when we will be upgraded as nobody wants to spend money.

15 seconds between each enter will be a problem for me if its gonna cost me time at work, hopefully its a home problem.

Hmm now when I do input form, it just defaults 2's into every field rather than the info entered.
My mind will be racing with the spreadsheet now : - )


----------



## RSpecianjr (Jan 20, 2010)

haha oops, sorry i did that as a test. Let me fix it for ya...


----------



## woodland81 (Dec 27, 2010)

haha to see if i was awake and taking things in I bet!

The Main tab, the first grid, which is probably for dialy totals, this isnt adjusting when I select the date at the top, even if i select something with old style date stored in archive still doesnt like it.

Is it possible to have date format as dd/mm/yy in all the dropdown dates

Lee.

p.s I was wrong i didnt dream about spreadsheets but infact the post that comes with all the inputting, oh dear ha!


----------



## RSpecianjr (Jan 20, 2010)

Lee,

I've change the information on the Main page. Good catch, The formulas were off by 1 column and I had to edit it for the date change.

If you want to change the dates, you will have to edit the formulas that start with 

=text(reference,"m/d/yyyy")

to

=text(reference,"dd/mm/yyyy")

This can be done with a find and replace. You will then have to update the Weekday formulas to work with the new format.

Last thing you should have to change is the data itself, located in the Archive worksheet.

I don't have time right now to update it and will be gone all weekend.


Regards,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

Have been playing around with this whilst at work for a change .

After I press submit of the form, it takes about 15seconds for it to do so and then let me move onto the next.

I tried changing the dates to the nice format. putting d/mm/yyyy in all of look (Y) fields, then AA. I think i managed it. the only issue i can see is the input form displays it different, which then inserts it into the archive table, and means when you try to do a search on lookup, it cant find properley that date.


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

There are two columns of dates. One on Lookup and one on Main. Looks like you missed the Main fields (which I remember correctly, is what the form is based on). Change those and it should work for you.

As far as calculation goes, it will take me some time to work on it, since it will have to be written into a macro instead of formulas.

Regards,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

ahh yes I found it now, didnt see that before. dates in good format :wink: now after i made the view percentage smaller.

I dunno why but on lookup, when got a date selected, it shows the daily total, but the weekly n monthly is just full of 'value'
Its the same on the Main tab, first table works but rest show value. I even redownloaded your version icase i messed something up whilst changing the date format.. I sure wonder if the date format change means it cant read from the archive table


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

I can't replicate it, can you post your newest workbook with the #Value error?

Thanks,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

hmm thats odd, i lost my saved version to reapplied it to an older version, and i think its ok now. it could have been that because id not deleted out entries in archive which still held the old date format it got confused.
Tha main sheet doesn update daily or weekly when i select a date i know there is data for but monthly does.
Ive also tried to code the Air Mail,Special Boxes on Main & Lookup, but only managed the monthly one, i wonder if its related.


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

Looks like the dates don't register as excel dates when in that format. This prevents them from following logical addition subtraction etc. So 31/8/2011 will be larger than day less than 31.

I'd recommend changing the dates back, or we could do a workaround for this. We can base it off of a different set of dates and add a format formula to the sumproduct formulas. To do this, we need to change the format on 8 cells back to "m/d/yyyy" and we need to change the format of any data you already put in on the archive sheet back to "m/d/yyyy". Last we will need to change some code.

The 8 cells are the Beginning of Week, End of Week, Beginning of Month, End of Month on both Lookup and Main sheets.

Open up the VBA Editor ALT+F11 and goto the UserForm1. Double click on the Input button. Find the code that says:



> Worksheets("Archive").Range("B" & rw).Value = UserForm1.ComboBox1


and replace it with:



> Worksheets("Archive").Range("B" & rw).Value = Format(UserForm1.ComboBox1, "m/d/yyyy")


I also noticed you set a default value in the Date Input field. I wouldn't recommend having it set to a static date since it will need to change.

Also, you can change the range on the sumproduct formulas to something a bit more realistic. I didn't know how much data you panned on having, so I made the formulas look in every row on the archive worksheet. If you do a find and replace throughtout the workbook for the following:

Find:65489
Replace:5000

This should decrease the calculation time by a lot.

Regards,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

I think ive changed them things.

Oh yes reducing that rang worked a treat reducing the time to like a second! ray:

I changed the 8date formats too. and that code in the form, i cant tell if its done anything lol

Not sure what you mean about I set a date in the input box, i dont even know how to do that because I was thinking it would be good for it to default itself to the current date each day.

No luck on the main and lookup sheet when you select days to view.


----------



## woodland81 (Dec 27, 2010)

Everything seems to run much quicker now to enter at work and at home : - )

Been fiddling away but Lookup - Daily section updates when the filterss are on, but the Weekly and Monthly dont adjust, its the same in the Main one.
I make sure i select a date that has entries in the lookup table too.

I notice the formula in daily is iNDIRECT and in Weekly monthly is Sumproduct. In Main is sum product dauly and sumproduct weekly monthly.
Found one of my old excel guides from college, nowhere near as advanced as this stuff though haha, they neva teach you the right stuff!


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

I had fiddled with it a bit. It's really hard to get it to apply take dates as text and as excel dates. Basically what is happening, is it is looking up either the text based date against the excel based date and not matching, or the opposite (i don't remember off the top of my head). I'll take another shot at it here in the next little while.

Regards,

Robert D. Specian Jr.


----------



## RSpecianjr (Jan 20, 2010)

I'll have something for you tomorrow


----------



## woodland81 (Dec 27, 2010)

oh dear i hope its not my playing around with formats etc to try and get the dd/mm/yyyy thats destroyed it


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

Let me know if you find anything wrong with this.

Regards,

Robert D. Specian Jr


----------



## woodland81 (Dec 27, 2010)

Thankyou,

When I select date in the Input Form, there is a number defauled "40778" and unable to select one. I got it working just by keep selecting a date, but the amount of 2nd class letters, gets posted in Recordeds.

On The main Tab, the daily section doesnt update when there are entries for Air Mail, Special, 

Each page that has the Air Mail, Special Boxes, if i want to add the box that is from the Form, MISC, is it simply a case of me copying the formula thats in Airmail, and changing, the letter Reference that refers to the Column, i think i know how to do that : - ) see learning


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

Yeah, i was having that problem too. It seems it will only display the excel date version of the cell. I'll take a look at the macro to see if it can be adjusted a little to prevent this and the need to put select the date multiple times.



> but the amount of 2nd class letters, gets posted in Recordeds


I'm not sure what this means, can you elaborate?

I'll take a look at the air mail and special.

Yes, you are exactly right, you just have to edit the range. = )

Regards,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

oops sorry. what i mean is, in the form, what you enter in there for 2nd class letters, the number gets entered in the archive sheet in 1st class Recorded Letter, which then is displayed in the lookup table etc as recorded


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

I think i know what you mean, but can you be a bit more specific so I don't have to go through a lot of testing?

like the input from Form - Regular - Second gets put into Archive - Recorded - First.... or whatever it is

Regards,

Robert D. Specian Jr.

(1000!)


----------



## woodland81 (Dec 27, 2010)

Ok,

Input Form -
1st letter, 1st large letter0-100 - correct

2nd letter - entered into Archive Recorded Letter 1st (Col T)
2nd large letter - entered into Archive Recorded Large Letter 1st (Col U)

Recorded 1st Letter - entered into Archive 2nd Letter (AI)
Recorded 1st Large Letter - entered into Archive 2nd large letter (AJ)

Recorded 2nd Letter - correct
Recorded 2nd Large Letter - correct

Ive not tried every cell but i do think its just them two areas. i looked at the code to see if it was a case of simply changing a couple of ranges, i couldnt work it out sorry. looked into the forms code but got bit baffled :laugh:


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

I found it, just didn't double check the order things were set in on the archive page. Can either switch the columns on the Archive Sheet, or change the form (should just be copy and paste). I'm pretty busy this weekend, but should be able to do it monday if work doesn't kick my butt.

Regards,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

The order of columns in Archive to change is fine, I should need to refer to that as much, with the lovely grids setup to nicely display information.

:smooch:


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

I'm still having a problem with the data showing up as the way you want it. Trying different work arounds though. Let me know if you find any other problems or if I haven't fixed the "aligning" issue with the data.

Regards,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

Excellent Thankyou,

Im not sure if you fixed it or if I was being an idiot and hadnt realised the form had defaulted onto the Recorded Tab to start with 

The Drop Down lists that are on the Form and Lookup, For place and Offices.
If i need to add a new office to a location, simply add to the lists on the right hand side.
For a new location, can I simply create this list at the end, after RED C. Hall and it automatically be included in the drop downs?


----------



## woodland81 (Dec 27, 2010)

Awesome Ive just learned more and solved my own question
How to add New Locations and the subfilter of offices per location

Changing the Code in the Form to - Rowsource=Lookup!Z7:Z18 and adding my new Location into Z18 box.
I then checked the Row source on the office bitof the form to see if i needed to tweak something there, but found the range was already sufficient
=OFFSET(INDIRECT("Lookup!"&ADDRESS(2,MATCH(Lookup!$AA$7,Lookup!$A$1:$AQ$1,0))),0,0,23)
Am I right that as I add more in the Columns, id just change the $AQ to however far my column range stretches.
I


----------



## woodland81 (Dec 27, 2010)

I changed a reference in Data-Validation- on the Lookup-Location Cell so it made my new location appear.
However when I do a search - date-location-office-it doesnt match in the Daily Weeky Monthly.

Must be something else I need to tweak?


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

That all sounds right, I think there is one other place we will have to edit the range. Let me take a look at it and I'll get back to you as soon as I find it.

Can you post the newest workbook to work with?

Regards,

Robert D. Specian Jr.


----------



## RSpecianjr (Jan 20, 2010)

You should only have to change the range in the custom form and the data validation range.

Then you will have to add the location name to:

Lookup!Z18
and
Lookup!AN1

Under Lookup!AN1, you will list your offices.

You are correct about this formula:



> =OFFSET(INDIRECT("Lookup!"&ADDRESS(2,MATCH(Lookup!$AA$7,Lookup!$A$1:$AQ$1,0))),0,0,23)


If it isn't matching up, let me know. Just make sure you have data in there for the new office/locations.

Regards,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

My newest version is at work, as it was only when I started entering data that I realised my lists had increased of offices since, hence working out how to accommodate :tongue:

I did notice a problem but i think i worked out what it was.

If I did the Lookup
e.g Claypath- Planning,
The daily numbers matched correct, but then the weekly and monthly were much greater, baring in mind I only entered data on one date.

I think what it is, is that the lookup searches for all Planning, regardless of what location is selected in the drop down.?
I know what I need to do, just go through and many sure the Office Name doesnt match any others, calling them Planning & location ( Planning-Claypath, Planning-Seaside) etc.
Does that make sense


----------



## RSpecianjr (Jan 20, 2010)

Yup, exactly = )


----------



## woodland81 (Dec 27, 2010)

Im working my way through renaming items to stop the duplication error.
Something else ive noticed, if i make two Records for the same day, for the same section. On lookup, the Daily box only seems to show details of the one, the earlier one in the Table, weekly n monthly combine these. Is there a reason this happens.
Im just wondering if need to only make one entry per office per day


----------



## ryan96 (Sep 1, 2011)

i got an answer


----------



## ryan96 (Sep 1, 2011)

sum1 plz help mefgdfg


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

You the daily for an office is a lookup. It will only lookup the first instance. If you want to use the same type of formula as on the Main worksheet, you can just add the office limiter. This will allow you to have multiple records for the same day.

Regards,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

Hmm sounds simpler if i just collate my paper sheets and merge totals for one office into one Enty per day. I usually do that myself to cut down on the paper surrounding my desk.

Successfully entered whole load data, totals are out but think its down to my data entering rather than spreadsheet issue : - )

When thinking of my next stage, which is extracting Office total Costs per day, I was thinking of maybe, on the archive page, somehow link in the Unit prices from the other sheet, and in formula, multipy by whatever is in that cell, for it to do to each and add up to give a total itself.

I know I can use the lookup to find it manually, but that is variable. If i can get it to calculate and store it in a Cell itself (BN?), then I can simply Link that Cell up to my other Recharge spreadsheet where the Total is the requirement for that day.

Haha does that make sense. i think i know what im needing 2do and how 2do it. my ways is probably.


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

If you are wanting the total cost per day, per office... and you only have one office instance per day... then you could add it to the macro. Since the day's data will either be all correct or thrown out, it makes more sense to have it calculated in the macro, then have a static value put in the archive worksheet. Prevents more calculations from having to be done.

You can have it lookup the price values or hardcode them in the macro. If they migth change in the future, it might be easier to have them in a lookup.

Regards,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

Haha i wish I understood a word of that :wink:

Yep, if im gonna make sure I only make 1 entry per day per Office, then I will just need to store the total price per office per day.

My other spreadsheet where I currently enter this data manually has about 1 Sheets , so I guess, I could copy these into all in one spreadsheet, would be easier to reference rather than extracting out into another sheet

Not sure whats easier


----------



## woodland81 (Dec 27, 2010)

Oh dear, I cant for the life of me remember how I did it before.

On Lookup, Under the RED CHall List(AM), ive got lots, but not all appear on the drop down list. It stops at I think 24(sustainability)
How do I expect it so that it covers the offices underneath. I tweaked a few things but i did more damage than good to reverted back to a different version.


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

It's going to be a named range that does it. I believe you will have to change it in the form as well as the name manager. The last piece should say something like... 23, just change that.

Regards,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

yeah thats what i tried, i found that 23,in the Form Code and changed it to 27 as it would take the rest of my list. saved it

Tried in the Rowsource for that column in the form too changing to 27 but got message could not set rowsource property, invalid value


----------



## woodland81 (Dec 27, 2010)

i dont know what the name manager is :-(


----------



## woodland81 (Dec 27, 2010)

I found where named ranges are - insert-name.
I tried there changing the number to 27, and then in the form. but either stops the drop down list working, or if i do the change the opposite way around i cant change the 27 without it totally changing the formula argh!


----------



## woodland81 (Dec 27, 2010)

Managed to get it working, i think me manually trying to change the 27 on the named range bit was making me select another cell. copying out, changing number and pasting back in seems to have done the trick.
Not tested yet, but the drop downs are listing everything


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

Awesome job! Yeah, I forget what it is called in 2003... it is the name manager in 2007. Let me know if you run into any other problems. = )

Regards,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

Thankyou :tongue:

You know that you changed something, the bit that does the calculation, which was really slow before. do u know at what point as the table fills up it will start getting to snail pace waiting for the update?


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

It should be a while. I changed it from those 200+ formulas from looking at 65k lines to looking at something like 5k lines. So unless you have more data than that... you should be fine.

Regards,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

ok great thankyou. I guess if it gets top heavy I can start with a blank one and use the old ones as an archive.

At the moment Im trialling it. Only problem Ive come across, is my data input is a shocking, cant put numbers in right boxes,


----------



## woodland81 (Dec 27, 2010)

I need some more assistance.

Its to do with the Air Mail Special and Misc.
These are on the Form and when entered successfully post it into the archive table

On Main Sheet, Air and Special are entered into the correct boxes in 'Weekly' and 'Monthly' but not the Daily Box, Ive looked at the code but cant figure out how it works to fix it myself.

and Also on Lookup sheet, Air and Special enter into the correct boxes when a dates selected where there was one....but the Total at the top of the Daily box, doesnt include the Special....even though the formula G11:G49 covers the range.
In the archive table the last Item has Special Delivery entry so you can see what I mean - 14th Sept Claypath Housing

I bet its something simple


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

Can you post the newest workbook?

Regards,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

thats odd i thought id attached it


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

We must have missed it before, but the formulas need to be changed to SUMPRODUCT formulas on the Main worksheet.

For the other, it's reading it as text so not adding it to the total. At the end of both the formulas on the Lookup worksheet, add *1. Then it should work.

Regards,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

hmm i dont get you?

Do you mean simply to change formula in air mail and special box so its
=SUMIF(Archive!D,L10,Archive!BN:BN)*1

I tried that in Lookup and it adjusted the totals


----------



## RSpecianjr (Jan 20, 2010)

On Lookup in cell G48:



> =INDIRECT("Archive!BM"&MATCH($J$3&$L$5,Archive!A:A,0))+1


On Main, you need to change G45:



> =SUMPRODUCT((Archive!B5:B4926=$J$3)*Archive!BM5:BM4926)


Hope this helps,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

:smile: I did that G45 change myself at work when comparing formulas in different cells across the sheet.


----------



## woodland81 (Dec 27, 2010)

Everything seems to be going ok so far :tongue:

One thing ive come across today, is that I want to use the date drop down list to select 1st September, but with that daylimit earliest i can get is 2nd.
Is there a way to allow us to still select earlier days than the month limit?


----------



## RSpecianjr (Jan 20, 2010)

Hey lee,

I don't think I set any restrictions on it, so you should be able to type it in. It's a data validation, so it just runs off the list on the right... If you wanted to increase the list, just change the values to the right and then change the validation range to include all the added values.

Regards,

Robert D. Specian Jr.

ps Glad its been working well for you!


----------



## woodland81 (Dec 27, 2010)

Hmm, well in the archive table, theres data for 30th August, earliest entered, but the drop down list of dates on main and lookup, only reach back up earliest to beginning of Sept, i think its due to it counting 32days minus present day, so as each day passes, i cant select one day.

Played around with the dates in Column Y, to change the range, but it ended up changing the collated data in the Daily and Monthly info on Main, i guess because it started to apply a totally new date range, if that makes sense.
Does the dates in column Y determine how it counts what and how many to store in the Weekly & Monthly Tables? or is that done by AB & AC on their own, meaning me doing whatever I want with Column Y wouldnt matter, i could take out the formula =TODAY()+ROW()-32, either enter a manual date and copy downdown almost forever. or would it be ok for me to simply change that number to I dont know maybe 3 or 4months (-90).

Dont wanna play around too much incase, it messes something up which I then only find out after loadsa data been entered and cant be retrieved lol

As ive been using it more and more and more people have seen it and what it does to make work easier, they think its fantastic, i feel terrible for not coming clean that I didnt build it. they think im wasted in my usual job, gonna have to come clean soon before they redistribute me haha


----------



## woodland81 (Dec 27, 2010)

Ive just been playing around I think I might have done it.
You can see ive changed the date formula to =TODAY()+ROW()-120
Just so I know it covers all the old data, changed the reference so that the drop down lists know where to take the dates from.

When the time comes that the same thing happens again, that old data exceeds the date in the drop down, i could simply go back in and change these ranges, then put them back, rather than having a year of dates on the drop down.

Oh wow checking out the cell references to do with beginning of weeks and months, i understand alot more on how those tables know what to count :grin:

Have been getting a message about running out resources to display


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

You are right about the dates, all you have to do is change the list of dates. It shouldn't affect the data outside of you choosing a date from the validation list.

If you wanted to change it, you could simply have a static date in there. Then, you could use something like:

Y1 is set to a static date
Y2 is =Y1+row()-1

This will keep it growing, so all you should have to do is change the validation list. 

Though, if you are going to have a long history of data in there, you may want to consider a different method. Perhaps an Activex form control or you could have a Month selector in combination with a day selector.

As far as the resources to display, that I believe is going to be a limit to the computer itself. What kind of system do you have? Ram? 

For Office 2003, it is recommended that you have at least 4GB of ram on a newer OS (windows vista or 7) and 2GB on older operating systems.

If they like the work so much, get them to upgrade you. Say there are limitations to what you can do because you are using antiquated software/computer hehe = P

Regards,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

Ive really impressed myself, working out what makes it tick. I thought I would, just not come up with the functioning device myself.

If I put a set date in Y1 and then Y2 (=Y1+ROW()-1)
Do I only have to complete two fields, or do i need to then extend, that all the way down my column, then the data validation

Im not sure why, every now and again,, after some data entry, its probably something were doing. when u flick back to other pages rather than archive, one of the cells in table gets VALUE written in and makes it broken. All i do is then remove one by one the last entries in archive until value disappears, then resubmit that form. Do you know what could be causing it?

I doubt I will manage to get our people to add extra memory to our PCs. Do you think it would help if I limit the amount of entries in the archive, and create additional ones, mayb 1 per month.


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

You will need to extend it.

As far as the VALUE error... I'm not sure what is causing it. Could be the macro is still running when you change the worksheets.

Well, with something the size you are talking about, I would use Access. It is more geared towards databases. Excel is really more... data manipulation.

Robert


----------



## woodland81 (Dec 27, 2010)

I would say over the course of a month, approx 1000entries would be made and stored in the Archive. Noticed the more thats stored, the slower the posting option has got and then this message pops up about resources.


----------



## woodland81 (Dec 27, 2010)

On one of my Location lists of offices, ive added more.
But now it doesnt appear on the drop down list of the Lookup table

I cant for the life of remember how.
I know i click data validation whilst selected on, the office drop downlist. it then shows settings-source =Location_offices.
Now when i press the button next to it it takes me to the Location and highlights all the offices, but the bottom one, obviously this is the one that's being missed off. How do i include that in the =location-offices. when i press and drag the highlighting, it then puts cell ranges in the source. that's not correct is it.



Even went onto the form properties-changed rowsource- =OFFSET(INDIRECT("Lookup!"&ADDRESS(2,MATCH(Lookup!$AA$7,Lookup!$A$1:$AQ$1,0))),0,0,30)
increased that last number to what would cover my last office, for example 30. made no difference. i then changed it in the form code to 30

ComboBox3.RowSource = "=OFFSET(INDIRECT(" & Chr(34) & "Lookup!" & Chr(34) & "&ADDRESS(2,MATCH(Lookup!$AA$7,Lookup!$A$1:$AQ$1,0))),0,0,30)"

I was sure thats how I did it last time


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

There are two places you have to do it if I remember correctly. 

The Named Range needs to be changed, and the form rowsource needs to be changed.

It should be basically the same formula for both.

To Access Named Ranges: How to create a dynamic defined range in an Excel worksheet

Regards,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

Thankyou i found the Define bit.
Funny enough, before I went into there to adjust the range. I checked the issues I was having and behold that the drop down lists were displaying the office that it wasnt yesterday..even though i still needed to increase the Defined Name range. :4-dontkno

Hopefully my proper one at work has sorted itself out too :laugh:


----------



## woodland81 (Dec 27, 2010)

hi there, ive been protecting cells to stop users fiddling with formulas.
I unlocked certain cells needed for the user, and then did Protect Sheet to lock everything else. I noticed that when i launched the form, complete date, location, when I did office it come back with an runtime error thingy


----------



## RSpecianjr (Jan 20, 2010)

Hey lee,

can you be a little more specific about the error? haha 

Did you protect the worksheet with all the data in it? You would have to add a line to unprotect then protect again after it is edited. The code would go at the beginning of the "Okay" or "Submit" button click from the form. (forgot which i put it as).

Worksheets("Database").Unprotect Password:="secret"

then at the end:

Worksheets("Database").Protect Password:="secret"

Change Database to the worksheet name and secret to the password.

Regards,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

On my Spreadsheet, ive increased the amount of Locations. So adjusted the Form Row Source to cover it to enable all locations to appear in the Drop down list.

For some reason, when i select teesdale in the form it comes up with a Bug Error. Ive managed to add new locations before no problem. Do you have any idea what it coudl be?

Highlights this line in the Code too - 

ComboBox3.RowSource = "=OFFSET(INDIRECT(" & Chr(34) & "Lookup!" & Chr(34) & "&ADDRESS(2,MATCH(Lookup!$AA$7,Lookup!$A$1:$AQ$1,0))),0,0,40)"


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

The match formula is coming up with an error and then passing that error into the row source, giving you an invalid rowsource.

Just expand your column range to include the column that Teesdale's center list is under.

Currently set to AQ and Teesdale is in AR. Change it to:



> ComboBox3.RowSource = "=OFFSET(INDIRECT(" & Chr(34) & "Lookup!" & Chr(34) & "&ADDRESS(2,MATCH(Lookup!$AA$7,Lookup!$A$1:$AZ$1,0))),0,0,40)"


Regards,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

Oh no i seem to be developing trouble this week, i made the change to enable teesdale to work correct. Ive entered the days worth of data, works fine in the Lookup tab. 

On the Main tab, none of the data displays when I selected 12/12/2011 in the Daily or Weekly columns. not sure if ive broken one of the formulas.

I copied and pasted the archive data into a previous version without the changes and it collated some totals, im not 100% if its accurate though


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

The range is too short on those formulas, you can do a find and replace. 

B4606 for B10000

Regards,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

Oh yes it got shortened to speed things up at the beginning. 
oh dar it will mean what I had down as 11th wont have been the correct totals if it wasnt reading anymore lines.


I did that range change on the Daily table, but each box came back with N/A


----------



## woodland81 (Dec 27, 2010)

think i sorted it by searching for the number rather than B4606


----------



## RSpecianjr (Jan 20, 2010)

Be careful searching through the entire column... It has to calculate each cell... in excel 2007 thats over 1 million calculations. In 2003 thats just over 65,000.

What we should really do is setup a named range that will autoadjust to the length of your archive.


----------



## woodland81 (Dec 27, 2010)

What I meant was, I higlighted all the cells in the Daily Table, then did the Replace.
so that wont have caused trouble. ?:neutral:


----------



## RSpecianjr (Jan 20, 2010)

Oh, okay. You did right. 

I was just referring to what we orginally had happen. The slow down because of it going through all the rows in a column. = )

How is the workbook working for you? Any complaints? Praises? What ifs? lol


----------



## woodland81 (Dec 27, 2010)

Yeah i remember restricting the number to increase its speed, as i have more entries it does get slower i got IT to give me an extra 1GB of memory in the pc as i did get memory popup boxes.

Its working great, everyone is very pleased with it, certainly makes my life much easier inputting and extracting data.


----------



## RSpecianjr (Jan 20, 2010)

Wohoo! Glad to hear it!


----------



## woodland81 (Dec 27, 2010)

Hi there, i still seem to be going well, mucked a few formulas up and managed to figure them out. and adding new locations offices :smile:

I was wondering if its possible.

On the main sheet, as it has 3 grids daily weekly monthly. Could one of them be amended somehow where you could make it provide totals for a date range. What I need is combined totals of letters per type from January up to present date.

Is it something simple telling when it searches to just look for those between two dates?


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

I was wondering when you might ask for a quartly or ytd system haha. The way it was setup is pretty dynamic, but you will need to set up another Beginning/End set.

Once you have that just copy the Month and change the AB4 and AC4 values to the new Beginning/End set.

To copy it easily, I usually select the range I want to copy and replace the "=" with something jibberish... actually I usually use the word "tootles". lol. Then you can copy and paste somewhere without having the formulas auto-correct. Then just find and replace "tootles" with "=" and you should be good to go.

Regards,

Robert D. Specian Jr.


----------



## woodland81 (Dec 27, 2010)

Thats so wierd, I was flicking through the spreadsheet and spotted AA4 etc and thought hmm i could just change the date rang myself and it would do the job. thought id log on to see if you had posted any pointers and you said the same.

Im not sure why but usually the Month grid doesnt work, at work it just has I think NAME in all fields and doesnt count. At home the Weekly and Monthly grids dont give any totals at all, they used to though.

I typed a date in those two cells - 01/01/2012 and 14/02/2012, but i think because the week and month grid isnt funtion i didnt get a change :-(. Will have to try that at work, is that all i need to do?


----------



## RSpecianjr (Jan 20, 2010)

Hey Lee,

If you put those dates in the cells I mentioned, they have to be in the same format as the ones above them. Also, you will have to edit the range as I mentioned = ).

If for some reason it still isn't working, just repost a copy of it and I'll see if I can get it to work.

Thanks,

Robert


----------

