We have list of firms and employees for six years and we need to give size no 1,2 or 3 for each firm based on average number of employees as the following :
size 1 for 1-9 employee
size 2 for 10-999 employee
size 3 for 1000-above
I would like to know how to figure out the vacation accrual rate based on length of employment.
Lets say for years 0-5 the accrual rate is 3 hours per pay period.
for years 6-10 the rate is 5 hours per pay period
for years 11-15 the rate is 7 hours per pay period
for years 16-20 the rate is 9 hours per pay period
and for years after20 the rate is 10 hours per pay period.
I want a formula that will look at say cell C5 (which is the cell that includes length of service) and output the accrual rate. Example. The number 12 is in cell C5. I would like the cell with the formula (D5) to say the number 7 (which is how many hours accrued per pay period based on the table above.
I have data for various firms - such as revenues, employees, etc. - for multiple time periods. There is no uniformity, so for some firms I'll have data for 8 time periods, for others for 1, for others for 30, etc. I have hundreds of columns, but the three relevant ones are: in one column is firm ID, in another column in reporting date, and in another column is number of employees.
The attached sample data will make it easier to understand.
I'd like a summary table that tells me the most recent reported number of employees for each firm, and the date of that reported figure. If that firm never reported any employee figures, then the summary table should still have a row for that firm ID, but blanks in the 'reporting date' and 'employees' columns. So, the summary table should be 3 columns - firm ID, reporting date and employees.
The challenging aspect is that the most recent reported employee figure is not always the most recent date on which we got data from the firm (so I can't just do a sort followed by a 'remove duplicates'). It'll need either a multi-step formula solution or a VBA code (that's my guess).
The attached sample data will make it easier to understand. It has the form of data I have and the desired output form.
Excel help needed.xlsx
i have data on firms for six years as the following :
1
1
1
1
1
1
2
2
2
2
2
2
3
3
3
3
3
3
how can i select the data for first year for each firm.
I want to make a rectangle where the size of it is based on cell value.
View 9 Replies View RelatedI have created a speadsheet for a tournament that lists the team names down column A (leaving a heading row). I have a formula in the top row which copies each row heading to a column heading 2 columns wide (to be able to record a result)
Formula: =INDIRECT(ADDRESS(ROUNDUP((COLUMN()+1)/2,0),1))
This way it will be possible to keep the results of the tournament on a grid. I would like to put in conditional formatting that meets 3 criteria...
1, it must fill every second row but only fill cells that have column and row headings (ie. rows that will have content in them which will be determined by the number of entrants). eg. if i have 10 teams then every odd row from row 1 to 11 will be filled. If 40 teams register then every odd row from 1 to 41 will be filled. 2, the fill needs to only fill cells that have column and row headings eg. if 10 teams register then every odd row from column 1 to 21 will be filled. Then 3, If the row heading and column heading are equal then it need to gray out or fill a different colour (or lock cell contents to empty if possible) but again, only fill cells that have column and row headings.
I have 2 rules.
Rule 1: =(ADDRESS(1,EVEN(COLUMN()-1)))=INDIRECT(ADDRESS(ROW(),1)) PROBLEM:
It needs to only fill cells that have a column and row heading which it doesn't, it fills the correct cells untill there is no column heading then fills every other cell in columns beyond the last column with a heading.
Rule 2: so far this works:
=AND(MOD(ROW(),2)>0)
but when i try to restrict it to cells with row and column headings it doesn't work. This is what I had:
=AND(MOD(ROW(),2)>0,IsEmpty(INDIRECT(ADDRESS(ROW(1),COLUMN()))"")=FALSE) OR =AND(MOD(ROW(),2)>0,CELL("contents""",ADDRESS(ROW(1),COLUMN())))
I have an Excel based application that needs to run with a screen resolution of 1024*768 in order to display correctly. Is it possible to use VBA to detect the users screen resolution. Then: If the screen resolution is not set to 1024*768 then resize Excel's main window to run at that width and height?
View 2 Replies View RelatedI'm trying to come up with a histogram chart that adjusts Bin Size based on user input. I've come up with the formulas for Bins and Frequency, but am struggling when it comes to charting the data to dynamically update when the user changes Bin Size. How to make the x-axis dynamically adjust with the number of Bins.
View 5 Replies View RelatedI need to show the size of a project in a cell based on it's number of hours, e.g.
Between 5-80 hours = SMALL
Between 80-200 hours = MEDIUM
Over 200 hours = LARGE
How can I do this? I can't get the IF function to work like this (unless I'm doing something wrong) and I thought about using a pivot table but think there must be an easy formula to use?
I am trying to set up conditional formatting in cell C4 to change the font to a smaller number based on the value of C4. If C4 = 0 then Font Calibri 16, otherwise Font Calibri 24. However, the font size is not availabe, it is greyed out. Is there another way to accomplish this without using VBA? In the worksheet could I use an if() statement to change the font size?
View 6 Replies View RelatedI have a spreadsheet that will monitor payment schedules, in which both payment frequency and the payment start date are inputted by the user.
As such, to make filling out the column(s) fool-proof, I want to grey out cells in which data should not be entered.
For example, if the payment frequency is every 6th day, and the payments are to begin on day 0, then days 0, 6, 12 (etc) should be left white, whereas the remainder of the cells should be shaded.
I can achieve this using multiple conditional formatting rules in excel2007 with iterations of formulae of the type:
I would like to know if it is possible with VBA in Excel 2007 to select objects based on size (or above a certain size).
I would like to select all the picture objects but not the small checkboxes on my worksheet.
I know that I can select the items one by one with home -> find/select -> select object but how I could make this "find/select" conditional.
I'm looking to set up a spreadsheet whereby individuals answer questions and have to format their answer using a particular font, colour, font size and so on. The idea is that I can then compare their answer sheet to a pre completed one using an =IF function and get a total score. The only problem is =if and =exact only lookup cell text/numbers and don't look at how the text is formatted within them.
View 9 Replies View Relatedwhat size monitor do you use?
I'm thinking about upgrading my 17" to a 23" monitor so I can see more cells without zooming
Anyone currently use a 23" for Excel?
I'm looking for a way to use VBA to set the window size upon opening the workbook. When the document opens, there should be two windows, each taking up half the screen and each on a particular tab.
Currently, if I save the workbook with the window settings I want, when I open it, both windows are maximized. When I hit the restore button, they go back the settings with which they were saved, which is what I want it to look like as soon as it's opened. Also, a bonus would be for the code to determine the screen size, so it can fill the screen no matter what monitor/computer it's opened on.
I am having some problems with some charts that are being created via VBA.
If I create the chart at 1600 x 1200 screen resolution at 100% zoom then my chart is fine but if I am at a different level of zoom or a different screen resolution the code for position the top and left and width and height of the plot area does not work as expected.
I have the outside of the chart set at 600 wide by 600 tall and no matter what resolution i'm in it seems that it always stays this way. I just seems the plot area and the legend move around.
I am working on a Listview. Every time I open the file, the ListView doesn't appear to be of the same size I intend it to be. Although I kept the size of the ListView big enough to show all my data, the ListView still goes back to much smaller size.
I have a workbook which has a file size much bigger than I would expect. To test it I inserted one blank worksheet and deleted everything else. I have removed a macro that was embedded in the workbook. I have removed all protection etc. I would now expect the file to be similar to a new workbook (circa 13k) but it is still 782kb even though there is absolutely nothing in it.
View 5 Replies View RelatedMY resolution is 1024X768 , and the form is full size of the screen. i want that in other computers , in 600X800 resolution , the form will be also on over the screen/
View 3 Replies View RelatedI built a workbook that is used by our account team to summarize account status. Each worksheet has several cells that use conditional formatting to call attention to the status reported in that cell. Each manager sends their worksheets to a Senior manager who then consolidates into a single workbook containing approximately 50 worksheets. Once consolidated, the file size has grown to over 12 Mb and this needs to be reduced to a more manageable number, preferable 4 Mb or less. Prior to buliding the new version of this workbook, the file size was less than 2 Mb.
I have removed the Macros and believe the size is due to the conditional formatting in each worksheet.
Once the worksheets are compiled, is there a way to remove the conditional formatting and save the existing format - Similar to a "Paste Values" function? The senior manager does not need to change cell value to the formatting of the worksheet he receives could be saved.
To be specific - one cell turns has no color if the value is 10 or less, is yellow if 11 - 15, and Red if 16+. Once sent to the manager, the variance in this cell is not needed; he only needs to see the color associated with the value when sent. Next week he will receive an updated worksheet and a new value will be entered. The conditional formatting is only needed by the individual preparing the original worksheet.
Somehow my tab names (in 2003) have a very small font size. How do I return the size to normal?
View 5 Replies View RelatedI am new to Excel VBA - Normally, If u move your cursors to the corner of your userform,we will see the single-two pointed arrow pop-out and then we can adjust our own desired size of the user-form.
How to do that in EXCEL VBA?
I have my Excel set up for a default font size of 12 point.
I often download CSV data to insert into spreadsheets. It opens in a new spreadsheet, and it's properly displayed in 12 point size. When i copy and paste it into the ultimate target spreadsheet (which is also set to 12 point size) the pasted data shows up as 10-point and I have to change the font size back to 12-point every time.
Why is this happening and how can I avoid that re-sizing that occurs?
I had a data sheet that used about 20,000 rows, down to AF20000 or so. I deleted about 16,000 rows and now want to reduce the size of the worksheet so that when I hit Control+Shift+End, it takes me only to AF4000, not to AF20000. Can this be done without replicating into a new worksheet?
View 4 Replies View RelatedI am creating a statistics database for my personal use. I have been developing this for many, many months now, and I have learned a lot about Excel and its capabilities in the process. However, I am becoming concerned because my files occasionally crash Excel, and I am wondering whether this is because my files are getting too large. Typically, the crashes occur when the main file is calculating (but not always). It stops responding, and I have to force the files closed through Windows Task Manager.
Without getting into the specifics of how the files work (although we can do that if it is necessary), here are the names of the files and their current sizes:
Baseball Database.xlsx -- ~28 MB
Baseball Reports.xlsx -- ~5 MB
Statistics.xlsx -- ~4 MB
I will have all three of these files open simultaneously when I am working on them, and my laptop has 4G of RAM. I do not have much (if any) extraneous formatting in the files -- I know how to remove that -- but there is quite a bit of conditional formatting in the files that I want to maintain. I am considering partitioning (separating) the tabs in the Database file into separate files.
I am faced with the task of reducing the size of a group of userforms because of screen size limitations.Some of the forms are quite complex and I am wondering if it is possible to select the form and all its contents and reduce it proportionately. It is possible to do this with the form frame but I have not found a way to include the controls.
View 3 Replies View Relatedsome spreadsheets i open dont have 65536 rows , they only a 100 or 200 its seems the worksheets have been cut down. how can i do this based on the last row in the e coloum.
Ie last entry in the e coloumn is 500 the worksheet to only be that big
This probably sounds really weird, but does anyone know whether the size of the text in Cell A1 on Sheet 1 can be linked to the size of the text in Cell B2 on Sheet 2? Like, when I change the size of Cell A1's text, is there a way to have the size of Cell B2's text change as well?
View 8 Replies View RelatedI wish to autofit all cells in a database to a maxwidth. ie:
Code:
With Range("A1:F1250")
.MaxWidth = 100px
.AutoFit
End With
I have a spreadsheet which is currently about 10 MB, I would like to reduce this as much as possible.
I am using lots of Vlookups but on each I am only referencing the data I need, there are also quite a few macros.
I paste value all the formula I do not need.