User Profile
SqueakySneakers
Brass Contributor
Joined Apr 19, 2020
User Widgets
Recent Discussions
Re: EXCEL VBA
Belo85 Hi Belo, Not sure these require VBA. If your chart begins in A1, to concatenate in E2 you can put =CONCATENATE(B2&" "&C2&". "&A2&IF(D2="","",", ")&D2). You will need to give examples on "How to auto populate two textbox values based on two combobox values." This can be done with a formula as well but we will need to know what combobox choices will result in what textbox answers.802Views0likes1CommentRe: Record macro range
Sibujoy Hi Sibujoy, Since I cannot run this macro it is difficult to determine which cells you are referring to. You start out selecting Column U, shifting it to the right, then labeling the new U. Later, you do the same for column V. Where is the "blank" column at that you want to select? Assuming I understand what you are looking for, if, starting with Column U, you named the range and then put the named range in your macro, it will follow the named range when performing the next tasks. To name a range you can select U to highlight the column, then in the upper left box (right above column "A") where it says 'U1' click in that box and type in a name like "START" then press enter. (If it does not take the name, it will remove it and put U1 back in.) In your macro where you have "U:U" you can now use "START". As another column is placed before column U, the START range will now be in column V and so forth. To see how it is working, you can highlight the START row in some fashion and then when in VBA, under debug, STEP INTO - F8 and run your macro one step at a time to see how this works. Let me know if this helps.1.1KViews0likes1CommentRe: Reduce excel size due to excess formatting
Stacy7438 Hi Stacy, What worked for me is I went down to the last row of data on my sheet, that was row 6200. I clicked on the row number to highlight the whole row, then held the shift button as I scrolled to the bottom row by click-holding the slider and dragging it all the way down the the bottom (row 1 million and something), still holding the shift key, then click on the last row so all everything from 6200 to the bottom of the spreadsheet are highlighted, (you can release the shift key at this point) then delete those cells by right clicking on the row number and select delete. This took my spreadsheet from 41 meg to 7.3.4KViews0likes0CommentsRe: Conditional formatting for entire row if one of multiple words appears in a column
BobbyM23 Highlight all the cells down F1 you want to format. Click on Conditional formatting and select New Rule, Use Formula. put =OR(F1="Neurology",F1="Intervention",F1="Ophthalmology") then select Format, and choose your format. Say ok until you are out.43KViews0likes1CommentRe: Writing Macros in Excel
Bigtoes Hi Bigtoes. First thing I noticed was there is a "space" at the end of the word MAIN on the MAIN tab. I removed it from my copy. Spaces like that make formulas not work if they are not accounted for. On the Earnings tab I put a sumif equation in the first cell for you to show how it will add all of the 1/1/2020 amounts, and all the other amounts are for examples. The formula will drag and drop. On the Earnings tab, row 40, I added a year (a40) and the numbers represent the months. These are used to make the formulas work. You can hide this row. For February 29 I had to create a special formula that accounts for leap year so do not copy over that one. Let me know if this works. There are circular references in this spreadsheet so you will need to fix those. Ex. J35 has a formula that sums itself. (=sum(h35:j35)). Others have this same error.1KViews0likes1CommentRe: Writing Macros in Excel
Bigtoes Hi Bigtoes, I am not sure I understand fully. By "Row A" you meant row 1, correct? I am assuming you did. For sheet 1, you put the date in the A column, and based on the fact it has a date, when you press "go" you want it to take the amounts in different cells and add them to the cell in F, then transfer that to a cell on sheet 4. Correct? Are all the amounts to be added in the same row? After the macro would run, what happens to the date on sheet 1? If you run the macro again, where would the next total be placed? Below the last one? On sheet 1, is there a column that has unique entries, like a part number or a name that is used only once? If there is a unique identifier column on sheet 1 you may be able to do this with formulas.1KViews0likes5CommentsRe: Excel question
Stephanie0412 Hi Stephanie, 3% x 667 = 20, so you have 20 "a" answers. 5% x 667 = 34 (rounding up). The difference gives you the answer of 14. If you want to vary the "Goal" percent, you can format a cell to be %, say cell b1. You can use algebra minus a countif equation, =(b1*667)-countif(a1:a667,"a") or just plain math =(b1*667)-(b2*667), placing the 3% number in b2. You can use a counting formula to get to the 667 if that varies as well.924Views0likes0CommentsRe: Excel 2016 creating button functions without using VBA / Macros
Stuart_Bean Hi Stuart, In the Active Controls the toggle button can be linked to a cell to give you a true/false entry in the cell. Aside from a formula giving an answer when true and a blank when false, there is no way to make a formula perform "tasks" of a functional nature without macros. Hans is correct.57KViews1like0CommentsRe: Excel Columns Organization
Jbisca Count the total number of responses before deletion. Lets say there are 500. Then, setup a formula that will count the existing responses on the spreadsheet using count, counta, countif, etc. Whichever will work best for you. Then, put =500-counta(b1:b500), or whatever count formula and range you have, and label it "Responses Deleted". Assuming you delete the entire row, the formula should self-adjust.857Views0likes0CommentsRe: Adjusting a sheet sorting macro to sort every sheet except for one
PaulOlsen Hi Paul, This is off the top of my head without getting into the VBA too much. Instead of trying to exclude the table of contents, can you let it sort it, then add a script that will move it to the position desired? Worksheets("TOC").Move _ before:=Worksheets("Sheet1") - or use "after" in place of "before" Then add more code to select that sheet (and a cell within it?). sheets("TOC").select 'Range("A1").Select <if desired, remove the ' > If the sorting causes the first page to get moved around, then create a false front page that will always appear first and keep it hidden to use as an "achor".2.4KViews0likes0CommentsRe: Please Help: How to find all cells those value are resulted from other cells
Khanh_Mai Highlight all of the cells you want to check. Select Conditional Formatting on the Home tab. Select New Rule... then select Use Formula to determine... In the box put =isformula(b3) Make sure B3 is the upper left corner cell of the ones you are wanting to check. If it is not, place that cell reference in there. Only put in the upper left cell, without any $. Then select the Format button, select the Fill tab, and select a highlight color. Say ok until you are out. all of the cells with formulas should now be highlighted the color you chose.1KViews0likes0CommentsRe: Border control on a expandable worksheet
GreatDay Try this trial to see if it can help. Open a blank sheet. On any given row put a dark border, write something, or shade in a column for a reference point. Select a large range, say from A1 to P23. Now go to conditional formatting and create a formula that says =row()=23. Select format... go to the border tab and put a bottom line in the sample cell. Say ok until you are out. On your spreadsheet you should have a line going across the page from a to p on line 23. If you add or delete any row, the line will stay on row 23 as the reference cells will move up or down. You can format this line or lines in any way you see fit. Helpful?2.2KViews0likes0CommentsRe: Besoin d'aide pour les formules
Affouda_93 To protect, go to the Review tab and select Protect Sheet. Check the items you want to protect or just let it default. Put in a password or don't. For hiding formulas, you can go to the View tab and deselect the formula bar under "show", or you can hide an entire column or row, if that's what you meant.1.1KViews0likes2Comments
Recent Blog Articles
No content to show