I am building up a formatted invoice, line by line all of which is working well from the data point of view but no quite so well from the presentation point.
My text is all in Arial 12pt and I have the row height set to "Auto" and cell alignment to wrap text (at least one cell per row often has 3-4 lines of text so I need "text wrap" on)
My issue is that the printed output from a multi row invoice looks "crowded" and as this is the view the client gets to see (and hopefully pay ) the look is important. I tried to set the row heights to say .71cm but that causes the text wrap to stop working correctly.
I think I am trying to get some form of "inside top" and "inside bottom" adjustment / fill / padding (which is what I would do if I was using some form of documentation tool like Madcap Flare for example) but I cannot see how to do it in excel.
It involved opening a new workbook and changing the number of worksheets in a new workbook, cell sizes, font, and font sizes... and then saving the workbook in one of the system folders under the name "workbook" to change the default workbook attributes, or "worksheet" to change default worksheet attributes.
I generated my spreadsheet in using vb6. Everything is working except that when I display the display (making it visible), some of the data in the rows hides underneath the row lines. I have to physically go into the spreadsheet to expand the rows to see the hidden data. Also, if I print the spreadsheet, the hidden part of the spreadsheet is cut off unless I mannually expand the rows. How can I programmatically expand the rows to fit each amount of records in the row. Currently, I have a module that is mannually calculating the rows
Also, I have attached the spreadsheet so that you can see my output.
Below is the code that calculates and formats the row presently:
w = 0 For Each R In xlWksht. Range("A5:N5"): w = w + R.ColumnWidth: Next
rht = xlWksht.Range("A5").RowHeight
With xlWksht.Range(xlWksht.Cells(ii + 1, 2), xlWksht.Cells(ii + 1, 14)) .HorizontalAlignment = xlLeft .VerticalAlignment = xlTop .WrapText = True .Orientation = 0 .MergeCells = True .RowHeight = .Font.Size * (Len(xlWksht.Range("A" & ii + 2).text) - Len("Comments:")) / w + rht + (rht - .Font.Size) ' + newlinecnt * .Font.Size End With
Below is my entire module:
ii = 5 w = 0 For Each R In xlWksht.Range("A5:N5"): w = w + R.ColumnWidth: Next
rht = xlWksht.Range("A5").RowHeight
'Do Until M.qBW.EOF = True Do While Not M.qBW.EOF ii = ii + 2 xlWksht.Cells(ii, 1).Value = M.qBW![Req No] xlWksht.Cells(ii, 2).Value = M.qBW![Description] xlWksht.Cells(ii, 3).Value = "" xlWksht.Cells(ii, 4).Value = M.qBW![ClientName] & Chr(10) & M.qBW![Status] xlWksht.Cells(ii, 5).Value = M.qBW![P L] & Chr(10) & M.qBW![TotalProg1Hrs] ...
Finding the Column with the MAX Height in comparison with other Columns of UNEVEN Height
I need two things :
1. I have several columns starting from Column B till Column F, each column having values starting from the third ROW.
Ex:-
Lets say Column B contains two values in B3 and B4, Col C three values in C3 C4 and C5, Col D four values D3,D4,D5 and D6, Col E two values in E3 and E4, Column F five values F3,F4,F5,F6 and F7.
So, the answer is F3:F7.
2.I am using 10^{4,3,2,1,0} in a particular portion of a formula, Now the number of elements in the array or in the Curly Braces depends on the number of Columns filled from Column B as explained in the Point 1.
Now, since I have five columns under consideration I have this order as mentioned here {4,3,2,1,0} , I would like to know whether I could make this dynamic, as in if there were only four columns then this would be {3,2,1,0} and if more this array could self-fill and expand..
If that's possible, then how do we use it in the formula, Is it by the virtue of the INDIRECT function?
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.
Vitamin B1 capsules come in three sizes - 100mg, 500mg and 1000mg
Our doctor prescribes dosages from 100mg up to 5000mg a day
I want to update our stock of tablets for each dosage level.
For example 600mg is 1 x 500mg capsule + 1 x 100mg capsule 0 1000mg capsules,
4900mg = 4 x 1000mg caps + 1 x 500mg + 4 x 100mg
We give out a kit which has in total 100 x 100mg capsules,60 x 500mg and 75 x 1000mg capsules
I want a set of formulas to calculate the nubers of each tablet at the daily dosage level between 100mg and 5000mg
Cell A1 = Daily dosage in mg
Cells A2 = number of 100mg capsules in that daily dosage - what is the formula ? Cell a3 = number of 500mg capsules in that daily dosage - what is the formula ? Cell a4 = number of 500mg capsules in that daily dosage - what is the formula ?
I use these stock sizes - to cut custom sized pieces. Example - if I have a piece that calls for a 15x18 - I will take a 16x20 piece and cut it down. I have all different kinds of size combinations that come up and am looking for a way for Excel to calculate the most efficient stock size to start with. Trying to do so using a series of Vlookups and if then's - but seems to be overly complicated and not always accurate.
I have a report with graphs and slicers and I have a 22 inch monitor. All of the graphs and slicers fit on my screen size but when others open it everything doesn't fit because they have a smaller monitor. Is there a way to auto adjust the report to adjust to individual screen sizes. I want the data from A36-V36 to fit on the screen at the same time. Not sure if this is possible though.
I have 2 colums that represent the height and width of a paper edges. And now I would like to find a formula (somekind of an IF?) which would calculate automatically (taking account the values in these colums) which of these 2 measures make together ISO paper sizes A0 (84,1x118,9), A1 (59,4x84,1), A2 (42,0x59,4), A3 (29,7x42).
Sorry for the question. Normally I find answers to my excel questions by going through the help tab or by searching on Google. However, I don't even know what question to ask on this one!?!
Basically I have created a spreadsheet with several columns, but I have one column that lists the shirt size (YS, YM, YL, AS, AM, AL, XL, 2X, 3X) of each person. Is there a formula that I can create that will tabulate the number of sizes (i.e. AS=2, AM=7, etc.)?
In previous years I made a column for each size, and simply placed a "1" in the correct column, and had excel just add the 1's from each column. However, that takes more time and space. I was hoping to streamline it this time around.
I have a file with a lot of tables of different sizes. The table ranges have not been named. I would like to use a macro to insert 20 blank rows between each of them.
Is there a way for me to have combo boxes, labels, and command buttons to be moved to a set position rather then just moving by intervals? What I need this for is every time one of my worksheets opens it refreshes the data and does this via lookup formulas and a macro that searches for values and deletes the rows with the matching values, it also adds rows to the top after it is done deleting. This causes all my controls to move randomly and I need some code to reset them to their original sizes and positions after the code that adds and deletes rows is done running.
I am working with the attached sheet and it is made up of groups of rows that alternate with a 6 digit NAICS code (industry code) then an aggregation of those codes into a more general 3 digit NAICS. I need to have the row with the 3 digit NAICS code calculate a number that multiplies a column called "% of industry" by several columns of of numbers and then sums the result into a cell in that 3 digit NAICS row. Different groupings have different numbers of rows. I would like to know if there is a 1 cell formula that can achieve this and be easily copied down the sheet to the other 3 digit NAICS rows.
You can see in row 37 this formula being executed for a simple 2 row aggregation. But that would take forever to replicate down the sheet, especially for the larger aggregations.
An example for row 32 that achieves the desired result and could be easily copied down the sheet.
On attached Spreadsheet, we are using Vlookup to create Team pages for coaches. Works great for giving coaches a team sheet when they leave draft. I have been aasked if we can cultivate the info a little differently for ubiform screenning. Company wants a count of sizes by position, that is everyone in position 1 will get number 40. How many #40 AS, AL, AM do we need.
There are 157 teams. SO I need to loop throgh each team, pull each size for each position and put it on Size sheet. I was thinking a vllookup/countif or sumif combo would work but have not been able to come up with it.
I have a workbook that has multiple tabs with data already entered. I have also built a series of tables/forumlas to display summaries of the data. My desire is to be able to copy this table with all formulas/formatting/cell sizes to the various existing tabs.
I have a line that is commented out in the bottom - that is an example of what types of filters I want to do but dynamically. Users choose multiselection from a list box then it filters the sheet for them. It is pulling the data from the listboxes but when it is more than one item it is Putting the whole thing in as if I said text = "ABERTI", "AMALON", "BCASTE", "BGELLE", "CFRANC" - No I want to filter by each of these (it believes this is one long string)
I am using Excel 2010. I have a spreadsheet where column A is for Quarter, column B is for Employee, and columns C-R are for Codes (Code1; Code2; Code3; etc. through Code15). One line might have no values in the codes columns, another might have values in only Code1, another might have values in Code1 and Code2, and another might have values in all 15 columns.
A B C D E F --> R 1 Qtr Emp Code01 Code02 Code03 Code04 --> to Code15 2 2012.1 Liz CER02 INS12 WKH15 3 2012.2 Jim PIN55 WKH12 WKH19 WKH23 4 2012.2 Jon 5 2012.2 Jim WKH15 WKH23 6 2012.2 Jon PIN55 WKH15 WKH12 CER08
The worksheet is named "ALLAUDITS" and the named ranges are as follows:
On another worksheet I need to be able to count how many times WKH15 appears in the CodeData range within a certain Quarter. I tried
=countifs(Quarter,"2012.2",CodeData,"WKH15")
But that didn't work, and from what I can tell in Excel support, it's because the ranges are different sizes. I have over 6500 rows, so I'm trying to avoid having to name all 15 columns and then do a sumproduct on all of them, such as
I could just create a pivot table based on the "allaudits" worksheet, but I can't figure out how to get the actual codes as the row headers (instead of Code1, Code2, Code3, etc.).
I'm an excel novice. I am trying to create a chart that assigns a range of foot measurements to each shoe size, but the data I am working with has only one measurement for each shoe size. Example: The data I have says:
size 7 = 250mm size 7.5 = 255 mm size 8 = 260 mm
So I want to create a formula, so the chart reads something like:
i want to paste a worksheet with amended cell sizes and also some clipart pasted...to another workbook in same style(excel 2007). when i do that, it says 'data on clipboard may not be of same size.......' and paste in different size.
I am using excel 2003. I have copied and pasted text from microsoft word onto excel.
The text is already split up into rows and columns in word. When i past it into excel and select all thousand or so rows and then double click the row to engage the automatic row height it doesnt work. It cuts off the last line or last two lines of the text in most but not all of the cells. What can I do other than one by one change the row height?
- each cell in row 1 represents the width of its column (i.e., if D1=3, the width of column D should equal 3),
- each cell in column A represents the height of its row (i.e., if A4=5, the height of row 4 should equal 5).
Question1. I need the widths and heights to adjust automatically every time, when values of corresponding cells change. How could that be accomplished?
Question2. I read that the width/height are measured by number of characters (i.e., if width=3, it means that 3 '0' characters of normal format can fit in that space). Will the values with fractions work correctly (i.e. will width=3.5 correspond to 3.5 '0' characters)? If no, how to adjust for that?
Note. In the current situation, additional rows and colums will have to be inserted, so the solution should work with new rows and columns as well.
I have a spreadsheet in which double clicking on any of the cells in the range A4:C17 changes the contents of cells F4:I33. The following code tells me which is the active cell and then pulls the values for F4:I33 from a lookup table based in this.
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Range("B100") = ActiveCell.Address() Cancel = True End Sub
code to stick in this (probably after the third line) that autosizes rows 4 to 33. There are no merged cells involved.
I would like to have a Macro to go from the first tab called "Start" and end up with the second tab "End" automatically. BAsically I need to take the product on each line under Tab Start and reproduce it for sizes 35-41 always ending with "-(size)". Then this new Product with Size needs to be multiplied one below the other for as many times as I have pictures (number shown under column B of the tab Start). Next to this value in column B of tab End I'd like the same name listed again but with the number 1, then 2, then 3, etc. as needed and the extension .jpg.
I have a vlookup into a row of cells. Sometimes the new text is so long it has to word wrap. How can I get the ros to expand with out me manually doing each row, and then go back to one line with the data changes.
inability to set a default row height and still allow the occasional row that requires more space (i.e., one of it's cells has multiple lines of wrapped text) to AutoFit if necessary.
Say you have the data shown in NormalSettings.png in an Excel file (see attachments) and you want to have a little more room between the shorter rows, but not have them all set as big as the expanded rows. One way to do this in bulk is to select the entire spreadsheet and set a fixed row height, but this chops off any cells that require more space than your default height (see FixedRowHeight20.png). The only way to fix this is to manually scan through your sheet and individually select all rows with cells that need more room than your preferred row height and AutoFit them (good luck if your spreadsheet is any size...) . If you try selecting the entire sheet and choose Format-Row-Autofit, you're back to the crowded display shown in 'NormalSettings.png'.
My workaround to this annoying problem is this: choose a column that you're not using (I just select the very last column in the sheet by holding down CTRL and pressing the right arrow until the screen stops moving) and highlight the entire column by selecting the column header. Then increase the font size for that column...voila, even your blank rows will now AutoFit to the new font size rather than the font size you are using for your data...effectively increasing your default row height without sacrificing AutoFit capabilities. I find that using 16 as a dummy font size makes my data (which is font size 10, Arial) look nicely spaced out, but experiment and try stuff until you find what you like!
