Cell Grow And Shrink With It's Contents?
Sep 27, 2008using the wrap text option for a cell, is it possible to have the cell grow and shrink along with its contents?
View 10 Replies
ADVERTISEMENT
Shrink Picture For A Cell (use VBA)
Apr 17, 2009I want to insert a picture on a cell of Excel. But this cell is too small. How do I shrink picture for a cell (use VBA)
View 2 Replies View RelatedExcel 2007 :: Shrink / Trim Worksheet To Rightmost / Lowest Filled Cell?
Aug 19, 2014Assume I filled some cells of a worksheet (in Excel 2007).
Now I place the cursor in one of the cells and dragged the cursor to the right side. As a result more and more empty columns are automatically added at the right side even I do not enter something. Similarly rows are added at the bottom when cursor is dragged downwards.
Correspondingly the horizontal (and vertical) slider shrinks because the dimensions of the matrix grows.
Is there a menu/function which let me do the opposite?
Something like a trim/shrink function which cuts/deletes all empty columns beginning from the right side resp. rows from the bottom?
.... but not from the left and topmost side.
Does A File With VBA Grow In Size
Jul 9, 2009I created a new form for our field personnel to complete daily. They are telling me that over a few weeks the file grows in size. The file size is 335KB starting out end up in a few weeks being 3.5megs. Now they add on some other sheets and things which undoubatbly make the file size larger but I am wondering if there is anyway thet VBA could somehow make the file bigger as the open it each day over a few weeks? They are not adding in any VBA, they are only using the code which is provided, which is accounted for in the 335kb. It seems unlikely but i thought i would ask.
View 9 Replies View RelatedGrow Denominator Ratio By Fixed Percentage Until Equal Or Greater Than
Aug 24, 2006I have what may be a math question as much as an Excel question. See my attached spreadsheet example in which I grow the denominator of a ratio by a fixed percentage until it equals or exceeds the numerator. This results in the denominator being grown X number of times.
View 2 Replies View RelatedShrink Validation List As Selections Are Made?
Feb 19, 2012I have been working on this data entry and validation tool for work for a while now. I've got most of it down and works just as I want it to. However, I have made some validation lists for people to select and animal ID when entering some data. Once the animal ID is entered I want to remove it from the source data and the list to stop people entering a duplicate number for the same event.
I'm not sure how easy/difficult this will be but the whole reason for this project was to stop people entering the same data and only allow them to enter data that I pre-define. Its to cut down on mistakes where somone will enter the wrong ID and then finding the correct ID becomes difficult due to having over 4000 animals on site.
Compare Cell Contents With Given Range Of Contents
Dec 30, 2013I have attached a workbook stating my problem.
file1.xlsx
Return Contents Of 1 Cell Based On Contents Of Another Cell?
Mar 12, 2014I thought this was a pretty simple formula but I am having difficulty creating it. I am attaching a little test spreadsheet. Sheet 1 is where the data will be entered. The Reimbursed column has a drop down choice of yes or no. The next 2 columns are the cost of registration and the cost of accommodations. On sheet 2 is where I would like the formulas. So in cell A4 I would like a formula that says if B3 on sheet 1 is Yes populate this cell with the contents of Cell C3 only, B4 of sheet 2 would then be B3 if A3 on sheet 3 is Yes and so on with the Not reinbursed if sheet 1 the Reimbursed column is no.
View 3 Replies View RelatedCopy Cell Contents Between Tabs Provided Cell References Match (VLOOKUP)
Apr 29, 2014I have 2 inventory reports: what my store has and what my supplier has. I need to copy Tab1:K# to Tab2:T# provided that Tab1:A#'s contents match Tab2:A#'s contents. The A column represents the SKU of the item, but there is a difference in the amount of SKU's in each (my store sells ~6,000 items, supplier has ~10,000 items), so it's not as simple as sort by column A and copy pasting column K to column T.
For instance:
On Tab 1, A2's value is [1], K2's value is [9.38].
On Tab 2, A70's value is [1], K70's value is blank, but I need it to be [9.38], to match Tab 1's respective SKU.
I almost thought I had it figured out with VLOOKUP, but I cant seem to get it right... It doesn't reference the correct number.
Screenshots for reference
First tab, from the wholesaler: [URL]
Second tab, store's stock: [URL]
Workaround For Public Variable Bug: Creates Different Cell Colors Based Upon The Cell Contents
Oct 15, 2008I am in the process of reformatting an excel workbook to act as a review tool for different factors in a process. Part of my redesign includes the use of coding that creates different cell colors based upon the cell contents. The new workbooks will be used to handle existing data for this year. I have developed a process macro to open an existing workbook and copy and transfer the original data worksheet into the newly formatted workbook. The data gets transferred to the new worksheet and the resulting workbook is renamed and saved, Heres'' the rub... the newly saved workbook is missing all of the coding for the worksheets... apparently this is a MS bug.
Has anyone figured a workaround for this. One thought I had is to open both( new and old )workbooks and rather than move/copy , i would transfer the data using cell references.
Macro That Will Clear Contents Of Cell Based On Format Of Text In Adjacent Cell
Feb 18, 2009Been racking brain, searching through the forum here, and my Excel 2003 Bible all day trying to figure out this problem to no avail. I would like to clear the contents of any cell in a given range if the cell immediately to the right of is formatted as bold.
View 2 Replies View RelatedDouble Lookup To Retrieve The Contents Of One Cell And Put Into The Main Sheets Cell?
Apr 18, 2013I get a report each day with a list of issues. the "group" that works the issue and the "priority". Based on these two factors, i need to do a double lookup (vlookup?) to another tab or file to match the priority and group and see what value should be brought back for each lines results. For example, if group1 had a prority3 issue, the lookup would find the value from the other sheet or file and bring back the value and put it at the end of the row where the formula is.
Attached are examples of the sheets.
sheet1.jpg
sheet2.PNG
Excel 2010 :: Cell Summing Through Referencing Neighbor Cell Contents?
Feb 12, 2014we have a Excel 2010 worksheet containing multiple tables.
Table1
ColumnA ColumnB
1 Blue 12
2 Orange 14
15
11
3 Red 10
Table2
ColumnA ColumnB
1 Blue 11
2 Black 13
15
11
3 White 10
19
17
On a separate worksheet we want to calculate with the first worksheet's values.
For example: Calc1: Sum ColumnB IN Table1 WHERE ColumnA = Orange
How do we do such a calculation/formula?
Move Down Column And For Every Blank Cell Clear Contents Of Cell To The Left?
Jun 1, 2014i need a code that moves down a column and for every empty cell in the column the cell to the left is cleared and then it moves on to the next cell down. the column is not always the same and will start from a selected cell, and the column will contain no more than 5 rows
View 3 Replies View RelatedAutomatically Update A Protected Cell Based On Contents Of An Unprotected Cell
Apr 30, 2009I am trying to find some VBA that will allow protected cells to be updated when a user enters or selects data in other cells.
For example:
Cell 'A1' starts out blank.
Cell 'B1' has a drop down to select specific items.
Cell 'C1' is the date of the order and is a locked cell.
Cell 'D1' is the date of completion and is a locked cell.
The worksheet is protected to prevent direct changes to cells C1 and D1.
If a user enters any information in A1, then C1 displays the current date.
If a user selects 'Complete' from the drop-down list in B1, then D1 displays the current date. Both of these actions are independant of one another. This format is the same for every cell in the 4 columns indicated above. I thought I was able to do this in another spreadsheet I created a year or so ago but I have not been able to figure out what I did and I do not have the spreadsheet to look at.
Replace Cell Contents Based On Matching Data In Another Cell (On Another Sheet)?
Apr 28, 2014I am working with a spreadsheet that has our user data; User(First) Name, User(Last) name, Computer Name, MAC address, Phone Number, Seating Position and Network Port Number.
I have let this get horribly out of sync, moved computers around, and moved them while the MAC address column was hidden, As a result, I have a bunch of users listed as being at the wrong computer. I have a second spreadsheet I generated that gives me the logged in user for about 2/3 of my computers, with the computer name(Just 2 columns, 'A' and 'B'. I would like to have Excel match the computer name and then overwrite the user name. For example, if the first computer in my correct user list is DELL-99945ty2, and the user name is "Jimbo Jones", I would like it to search the first Excel doc(The one with all of the user data), and replace the user name with "Jimbo Jones". Is this simple and straightforward?
In my main spreadsheet, the user name is in column B and the computer name is in column G, So I want to use the values from Column B in my second document to find its match in Column G of the first spreadsheet, and then replace the data in Column B with Column A in the first spreadsheet.
Lock / Unlock Excel Cell Based On Contents Of Another Cell?
Sep 12, 2013How to Lock/Unlock an Excel Cell Based on the Contents of Another Cell?
View 1 Replies View RelatedSetup Dependent Cell Based On Contents Of Independent Cell
Oct 20, 2013I'm trying to set up a dependent cell (B) based on what is in the independent cell (A)
If A is blank, B should be blank
If A has any value other than X, B should display a set string Y
If A contains X, B should contain a drop down list with a range of choices.
I've tried setting it up as a dependent validation cell and I've tried using OFFSET, but the best I can do is to get three different drop down lists to populate cell B. One with only string Y, one with only a blank, and one with the range of choices. The problem is in the first two cases above, I want the cell to fill automatically with either blank or string Y -- I don't want them to just be drop down options, with the cell displaying the last value that was input.
Excel 2010 :: Add Comment To Cell Without Changing Cell's Contents?
Mar 13, 2012How, via VBA, would I add a comment from a userform (text box name: txtReason) into a cell that may already have a comment in it? I would like to keep the comment that is in the cell and then have the program add a "/" and the next comment from txtReason..
(using excel 2010)
Finding A Cell In A Variable Location Based On The Contents Of The Cell
May 2, 2012I am creating a macro to automate data analysis for work, but I've become stuck.
I need to find the coordinate of the first cell in a column with word "reserved" or "extended" contained in the cell, and then assign the row number of that coordinate as a variable to use for moving data around. Basically, I want something like VLOOKUP, but instead of returning a value, I want it to return the coordinate.
VBA To Clear Contents / Change Value Of A Cell Based On If Another Cell Has A Value And Continue L To R
Mar 9, 2013I am relatively new to VBA. I am creating an attendance calendar that tracks employees calling sick, late etc.. It is a point based system. What I am looking for is, a way to clear the point value that was manually entered in a specific cell (I3), if there is a Value manually entered in (CU3). Each column in my worksheet is for a specific date ie; I3 is the cell where I enter the points (1.00) for that employee by calling in sick on 3/1/2013, (Column "I" is for 3/1/2013). After 90 days, this point accumulated by the employee does not count against them, so I need that point entered in (I3) to either = 0 or the cell contents to be cleared if there is a value entered in cell (CU3) which is 91 days after, so my (A3) cell does not add that point acquired on 3/1/2013.
I need this to run in a range (I3:I450) so if any value is entered into (CU3:CU450) it has the same result and continue to for (J3:J450) so if any value is entered into (CV3:CV450) and so on..
Excel 2010 :: Delete Cell Contents If Cell Contains Numerical Value?
Oct 8, 2013Recently I have been practising writing my own macros in VBA. I am using Excel 2010.
I have been trying to write a macro to look at two columns in a worksheet (say, columns D and E) and to delete the contents (not the formatting, and I don't want to delete the cell itself) of that cell only if it contains a numerical value (e.g. 1, 2, 256 or any other number). If it finds a cell with letters, then it wouldn't delete the contents of the cell. The cells are currently formatted as "currency" cells.
I have been trying all sorts of things and I just can't get it to work. I can write a macro to clear a range easy enough, but I am stuck on getting it to delete the right stuff. I won't embarrass myself by uploading the code that I have been using..
Clear Contents Of Cell Based On Comparsion Of Cell Values
Mar 7, 2007I am a financial administrator & every month I have down load 4 bank accts as CSV, import into xl & code (CACode) the amounts for our accountant. I have VBA that formats, adds headings & formula etc but I have a problem/s. In H col I place CAcode & I use a sumif formula in I col to sum all the amounts with that have the same CAcode. For simplicity sake I copy the sumif down & then sort H col ascendindingly (this is done by VBA). Now I am trying write a macro to clear the contents of the cell in I col
if eg h60 = h59
then I60 clearContents, Select h59
Else select H59
Do until H3 is selected
Psuedocode
Select table (A2:I Xldown)
Sort Table by CAcode (H Col)
Set Range as H3:Xldown
Select Last cell with CAcode (Xldown) in H col
For every cell in Range (H3:Xldown)
Use If/thenIf Last cell = 2nd Last cell (H Col) then
Clear contents of I col (last row)
select 2nd Last cell (H Col)
Else 2nd Last cell (H Col)Next Cell
I have to use Xldown to select range as the range will be variable each month & for each bank acct. I need to clear contents of cell to verify that all the sub-totals of unique CAcodes of the CAcoded amounts = the totals Because I may have up to 120 rows X 3 bank accts I am slowly using vba to do my work.
Sub sort_And_delete_Sumif_amounts()
Dim r As Range
'Select range to sort
Range("A2:I98").Select
'Sort CAcode in H col ascendingly
Selection.Sort Key1:=Range("H2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
'select range for comparisomn of CAcode
Set r = Range("H3", Selection.End(xlDown))................
Combine Contents Of Two Cells Depending On Contents Of Another?
Jan 14, 2013I am trying to combine the contents of two cells depending on the contents of another, I have tried to use the If function but am coming up stuck!
I have provided a link to the example file below:
[URL]
Refernceing Cells: Formula That Looks At A Cell On A Front Sheet, And Then Returns The Contents Of That Cell As The Result If It Meets The Criteria
Apr 5, 2009I have a formula that looks at a cell on a front sheet, and then returns the contents of that cell as the result if it meets the criteria. So for example this formula would be in Cell A1 on Sheet2 IF(SHEET1!A1,"New",Sheet1!A1,"-")
This formula is always in the same cell (different sheet) as the cell that it is looking at, down 1500 rows. Instead of having the formula named for each cell is there anyway to ask excel to 'look at this cell but on this other sheet'.
e.g IF(Sheet1!"This Cell" etc). That way no matter what cell you put the formula in it is always referencing the correct cell for the formula?
Paste Copied Contents Of A Cell As A Comment In Another Cell
Sep 7, 2012I am working on a database and trying to remove duplicates. When there are duplicates, I want to take the contents of one cell and copy them as a comment on the cell above (or below).
I tried to work on a small macro to do so, but the contents of the cell cannot be copied (it comes out as a blank).
The long way is to 'double click' in the cell, Ctrl+A, cut, click the cell above, Ctrl+F2, paste. This way is very long as I have thousands of duplicates.
Clear Cell Contents If Another Cell Equals String
Sep 23, 2013I have a 700 line spreadsheet and would like to delete the cell next to another cell if it contains a certain string. I.e if A1 to A3 equals "Test" then i would like to delete the contents of B1 to B3, But if A2 equals "Completed" then only B1 and B3 should be cleared.
View 4 Replies View RelatedAdding Color And A Value To A Cell Based On Contents In Another Cell
Apr 28, 2014I have values in a number of cells. Next to them, if there was a value, regardless of what the value is, except blank or zero, i would like the cell to turn blue and if possible have a 1 in the cell.
I've used the conditional formatting, however, among the options, 'less than' is the closest to to getting the color right. The issue is that if the cell has nothing in it, it still colors the corresponding cell blue; because it is not less than the already blank cell. If it is blank, I don't want it to color the cell.
Find Row Max And Display Cell Contents Of Cell To Left?
Aug 12, 2014I have data arranged in cells A1 to Z1 as Name1, Amount1, Name2, Amount2, Name3, Amount3, etc. Is there a formula that can determine the name that corresponds to the maximum amount in the row?
View 7 Replies View RelatedAppend Contents Of One Cell To Another Cell With Text That Still Editing
May 9, 2014ie:
Cell A1 = Ref#01
Cell B1 = A1&(whatever I choose to type)
Result in cell B1: Ref#01whatever I choose to type
Looks like I would need a macro because a function will be overwritten as soon as I start typing in cell B1.