Worksheet Design
Jul 28, 2006
I've been reading some threads lately about worksheet design and wanted you advice on something.
I have a worksheet that is about 7.5mb. The details of the worksheet are:
A. 19 total worksheets within workbook, three are visible
B. 1 master worksheet (raw data) that is updated monthly
C. There are 13 worksheets (for each outer office) that pull data from the master.
D. A couple of "Summary" sheets that summarize totals, profits, losses for each area
Situation is this....All code runs fine, but when it's time to save the worksheet it is kind of slow doing so b/c of size.
Would you suggest making a seperate WORKBOOK for the outer offices? I gave this a try yesterday and it worked fine, but I also added code to save once the data was extracted and that part took about 5-6 seconds (I know that's not horrible, but I'm comparing it to previous times).
View 3 Replies
ADVERTISEMENT
Mar 26, 2009
I have created a design in excel and now need to shrink it to fit on 1 page. Is there any way to do this?
View 2 Replies
View Related
Dec 17, 2009
I am going to attempt to design a production spreadsheet which does the following:
1.Orders are put on one page of the spreadsheet (Order Tab) The order is put on listing, Customer, Sizes, Qty etc
2.The user is then able to select an order and if possible I would like a pop up calendar to appear in which the user can then select a production date.
3.One the production date has been selected I want it to open up a new tab if one doesn’t already exist and then cut the information from the order tab and then paste it into the production day.
I have provided a spreadsheet of what I want to achieve but I am unsure if this is possible.
View 14 Replies
View Related
Apr 29, 2008
For some strange reason the workbook I have been using on a daily basis has suddenly decided to go into design mode and stay there. No matter how many times I click the design mode button in and out it always stays in and I can't use my buttons.....it just selects them?
View 9 Replies
View Related
Aug 4, 2006
how to customize the userform with the background like Ivan's userform:
View 3 Replies
View Related
Aug 10, 2007
I have been tasked with performing a work measurement study to improve productivity in our organisation. I managed to adapt a paper based report to an excel file; with the view of creating a model. I also intend adding the cost of employing people in specific positions to determine the cost per hour for each specific activity.
View 2 Replies
View Related
Jun 24, 2014
How can I format a Pivot Table with 5 levels. I try to use the "Design" but it only format the first 2 levels of the Pivot Table. I need to distinguish each level with different colors. I think manually is a bit difficult.
View 6 Replies
View Related
Nov 5, 2008
I have a production report card spreadsheet with numerous checkboxes. Originally I also had text boxes for user input - which I have since removed all of and simply unlocked cells for user input (I protect the spreadsheet). My problem is that now that I have removed ALL of the textboxes - I get the error message "Can't exit design mode because Control 'TextBox3' can not be created." everytime I go to lock the spreadsheet after tweeking or upon opening of the file. I also cannot use the checkboxes now because I am stuck in design mode.
Any thoughts on what might be causing this...or better yet...how to stop the error msg altogether? I googled and no answers...some theories that it was a bug with excel2003...but I find it hard to believe as I found posts for the same message of users of WORD2003...so I am thinking it might be VBA related?
View 7 Replies
View Related
Apr 16, 2009
I am in the midst of developing a Userform with a purpose of entering/retreiving and amending data. A rough approximate of about 50 sets of 3 text boxes is required for user to type in data. Is there a kind of limit or performance issues if there are so many text boxes available? What other ways would make it work? Would appreciate some advice from the veterans in this field.
View 5 Replies
View Related
Jun 11, 2009
I've created a spreadsheet that has a number of command buttons acting as hyperlinks navigating to other worksheets within the workbook and other files. The buttons only function when in design mode and no matter what I try the document will not open in design mode. For ease of use I wanted to avoid training people on how to go into design mode, anyone aware of a work around?
View 3 Replies
View Related
Jul 31, 2008
im trying to make a few things work at different stages.
1. Im TRYING to create a not so simple programme with Excel for My Wifes Creche - She would like to have a programme that, when she gets a phone call from a parent to check availabilty in lets say - 3 months time, it reports back with a number of availabiltys for that date in a specific room.
2. Rooms are broken in 4 categories ( up-to 12 mths old "Baby room" 12 mths to 2 yrs "Wobblers room" 2-4 yrs "Toddlers room and finaly Playschool,)
3.So far what i have done is this =IF(G2<$A$1,"",IF(G2<$A$1039,"Baby",IF(G2<$A$1404,"Wobbler", IF(G2<$A$1770,"Toddler",IF(G2<$A$2290,"Playschool",
which gives me a room from inputting the Date of Birth.
4. ive then used the form button to give me the option to add edit and delete entries from the headings (First name Last name Date of Birth Room -but this will show once DOB is entered.
5. i have tried to run the =COUNTIF(A2:A7,"Baby") to try and get the total baby spaces taken up at a given date however this wont work for me and isnt giving any error message, just a blank cell.
View 11 Replies
View Related
Apr 8, 2012
I have been writing VBA code in Excel for about 4 years, but I am self-taught, so I only know what I have had to learn. Recently, I learned that I could run a macro from a cell formula. I created several hundred cells (on multiple worksheets) that include calls to various macros. I only want to run these when I know they need to be updated, not every time I change a cell value.
I turn on Design Mode and try to modify my worksheet (adding or removing columns, rearranging stuff) and some macro(s), somewhere(?) execute every time. This can sometimes take 15-20 seconds for everything to update.
Did I hit some magic key sequence, somewhere? How is it that some of my macros, somewhere are running with Design Mode enabled (and turning it OFF, by the way)?
View 2 Replies
View Related
Mar 21, 2007
The spreadsheet I have designed is to be input offshore by one group, and the values that get put in this shared workbook will feed into another shared workbook (for reference and ease, I will call one offshore. book and the other uk.book).
It seems that I have to save the offshore.book (so could I use some autosave code, for every 5mins or so? I dont have the plugin so would have to write it in), then hit update links in the uk.book (again, I presume I could write this so it triggers off every 30mins or so, but can be done manually if required). It seems abit fiddly doing this, but this is the way they wanted it done (my idea was to put into a database, but got out voted).
The figures in the uk.book would simply be a vlookup. However, we do not want the uk team to be able to look at the other book so would the links still work properly? (ie there would only be one open workbook at a time - direct access to each others spreadsheets shouldnt occur.)
View 9 Replies
View Related
Mar 11, 2014
I have a Spreadsheet with various tabs.I want to :-
1.A Userform to add records into "Material Indent"tab.
2.Secondly,transfer rows button on Userform to shift particular rows on entering Reel no. and date to "material Usage"job desired.xlsmtab.
View 6 Replies
View Related
Jan 20, 2013
I'd like to make a conditional design for a row. If a certain cell equals "x" I'd like to color the whole row with grey My version of excell is 2003
View 7 Replies
View Related
Jun 15, 2014
A typical Design Matrix is shown in the attached Workbook. There are two domains of Merged Cells that make up the Headings of the Matrix; FRs (Functional Requirements) and DPs (Design Parameters). Given a Hierarchical List of FRs specified by the User, the User would like Excel to bulild the Matrix Hierarchy of FRs automatically (going down the Worksheet). The DP Hierarchy is the same hierarchy, except transposed and reflected across the Worksheet. The attached Workbook has up to seven (7) levels, but the ability to go create up to 10 levels is desired.
View 8 Replies
View Related
Mar 17, 2014
I am a happy user of Office 2007 and a casual coder. I am familiar with most of the basics of coding and enabling macos. However today, when I attempted to add some code to a workbook, I noticed that Excel would not actually enter design mode. Even though the design mode button will toggle and stay in the "on" mode, I cannot access VB property panel for any controls, even standard contols. Also, I am forced to CTRL-CLICK objects to select them and then am only presented with standard "Format Control" option, not object properties.
One other odd sympton... I can use the macro recorder to add code to a control (a checkbox for example), but when I try to manually add a line of code referencing that object (using the default name), the VB editor does not recognize the object. For example the IDE would not recognize and code a line for the "checked" property of a checkbox control when I check the box while in macro record mode.
These symptoms suggest to me that there is something wrong with the design mode "switch".
This all used to work fine, but it's been a while and many MS updates to my Windows 7/64 machine (now about 4 years old).
View 1 Replies
View Related
Jul 27, 2009
I'm trying to determine if a rangeName is defined so that I can delete it without a programming error. I've used the idea that if I try to goto a non-existant rangeName, I'll get an error. I've tried to design a function that will return true or false if the rangeName is defined on the basis of that idea. Can you take a look at the code and see where I went wrong?
View 4 Replies
View Related
Mar 20, 2012
How to design a form in Excel and populate data into the combobox, using existing sheet name?
View 3 Replies
View Related
Feb 11, 2013
I need to query table contents where some general items are met, and item id's are not contained in an array.
Example:
Code:
SELECT LOOKUP.*, LOOKUP.Var FROM LOOKUP WHERE ((LOOKUP.Name)="test" Or (LOOKUP.Name)="test2" AND ((LOOKUP.Type)=3) AND ((LOOKUP.Var) ("array", "of", "items")))
I need to know how to add an array to a query since I can't hard-code the list each time, and the array can be a different size each time, so using array variables doesn't make sense here.
View 9 Replies
View Related
Feb 9, 2009
I'm trying to design a spreadsheet to allocate an amount to different bands.
For example - I have the figure 1750.
Band 1 : 0-500 (1)
Band 2 : 501-1000 (0.8)
Band 3 : 1001-1500 (0.6)
Band 4 : 1501-2000 (0.4)
Each unit in each band has a value (in brackets). So as you can see, the value of a full Band 1 would be 500. But the next band is only worth 400 when full, and Band 3 is only worth 300. I want band 1 to fill up first, then band 2, then band 3, then finally band 4.
I would like to be able to allocate the figure 1750 to all the bands automatically - starting with band 1, then 2, then 3, then 4.
View 9 Replies
View Related
Jul 17, 2009
Compatibility mode, in 2007, is frustrating me like crazy. I thought it was supposed to 'limit' functionality to what was in 2003. But what it seems to be doing is letting me use features that only work in 2007and then tell me when I save that 'hey, these won't work after you save'. Ugh! I can't even pick a highlight color without getting a warning and wondering what it will look like to 2003 users!
Is there a 'theme' or anything that will truly limit the features I use to those available to 2003 users?
View 5 Replies
View Related
Jun 4, 2006
I am working on a workbook that contains drop-downs in various cells. The source for these drop downs are several Named Ranges on a tab named 'Drop Downs'.
I am locking the whole spreadsheet down so that people are not messing with the format, etc. (which is the current problem). So I don't allow people to add/delete rows, etc.
I am trying to think of a good interface to allow users to modify the entries in each drop down list (i.e. they may need to add a person to the "Resources" Range which feeds into a drop-down box elsewhere).
So, people should be able to add/delete items. I was thinking I would put a little 'X' next to each entry, such that when pressed - it would delete the corresponding entry. (Using the Assign Macro on a Text Box). However, I have to assign each 'X' a method to call - and I can't pass in an argument - so if I assign them all the same macro name - I won't be able to figure out which one was pushed. And the number of 'X' will be dynamic, so I can't hard code a bunch of functions. I don't really have a good idea for allowing additions yet (other than a button that prompts for the name and then adds it in).
I guess I could just use a Form that allows editing of the data (that would be easy and powerful to do), but I don't really want to do that - I want it to be editable within the excel spreadsheet itself.
View 6 Replies
View Related
Jan 17, 2013
Can you change the height of a TextBox in a workbook when not in Design Mode? Like just grab the bottom and resize.
View 2 Replies
View Related
Mar 26, 2014
Is it possible to use design mode to create a button for multiples macros?
What i want to do is create like a list, and every selection run a different macro. I have a report that i want to use macro to make different filter selections, it would be about 20 different filters combination, so 20 buttons would be a mess... Is that possible or can i do it in another way?
And is possible to link the filter value to a different cell?
View 2 Replies
View Related
Jan 9, 2007
Is there a really good tutorial for laying out multiple database queries in excel? Or even just for automating reports between Excel and Access? Here is my situation: Monthly, Quarterly & Annually I report on performance standards such as: duration, best practices, cost-benefit analysis, etc. The information is pretty standard and is pulled from Excel Reports. Access is used to filter this information by appropriate month, quarter, year, and/or data type. In access I calculate the performance averages, max and min. Then this is in linked back to Excel to update the charts and summaries. My concern is how much time I spend rebuilding this system every month (about 4 business days)
View 3 Replies
View Related
Mar 21, 2014
I'm trying to design an if statement that refers to a range of cells on a calculation sheet to perform one calculation on TRUE; another on FALSE
=IF(A22='Calculation Sheet'!A27:A36,B22*100/16,B22*100/8)
When I view the formula in Function Arguments window, Value_if_true = 1043.75 & Value_if_false = 2087.5 <- these are correct
IF Func Arg.jpg
When I view the cell in the spreadsheet, system displays #VALUE!
When I view Evaluate Formula window, it seems Excel doesn't like the text.
IF Form Eval.jpg
View 5 Replies
View Related
Jan 29, 2009
I have a dataset storing 6 numbers in a row (where each number range from 1 to 50 and no duplication of number within each row). Totaling there is 1000 rows ....
View 9 Replies
View Related
Feb 27, 2013
I am trying to design a generator in excel that generates algebraic expressions.
For example
In cell B4 the formula is RANDBETWEEN(1,12)
In cells C4:29 I have letters a - z listed in a column one letter per cell.
In a separate cell from which excel reads is the formula:
=B4&CHOOSE((RANDBETWEEN(1,26)),$C$4,$C$5,$C$7,$C$6,$C$8,$C$9,$C$10,$C$11,$C$12,$C$13,$C$14,
$C$15,$C$16,$C$17,$C$18,$C$19,$C$20,$C$21,$C$22,$C$23,$C$24,$C$25,$C$26,$C$27,$C$28,$C$29)
For example one possible result would be 3f.
However if the result was 1m, in a separate cell I want excel to realise that B4=1 and then display it as m.
I've attached the spreadsheet as well : algebra.xlsx‎
View 1 Replies
View Related
Jul 28, 2009
i need some code to be able to toggle design mode, i am creating embedded flash games in excel and i want to be able to change the game using a drop down and this code:
View 3 Replies
View Related
