Search results

Mike's should be more efficient than text processing, too.. It didn't work for me as-is though, I had to truncate the digits after the decimal with this modification for it to work: =SUMPRODUCT(IF((TRUNC(A1)/(10^(ROW($1:$6))))=TRUNC(A1/10^(ROW($1:$6))),1,0)) Ctrl-Shift-Enter this array...

A nice manual method: Open datasheet view in access, select records (or ctrl-A for all), copy, go to excel, paste. If you have the spreadsheet that you want to emulate, I would just try to adapt the existing code. The ways I know of in VBA executing in Excel are: 1. Use ADO to open a...

The MakeUseOf blog focuses on free services... some interesting things on their collaboration index: http://www.makeuseof.com/dir/cat/web-services/collaboration-2/

Hi John, Not really my expertise, but -- - Zoho Sheet - http://sheet.zoho.com supports VBA (only online spreadsheet to do so I think) but may not yet support conditional formatting... here's a sample sheet that uses the VBA Worksheet_Change event to implement conditional formatting "the hard...

Hi Persol, If you incorporate ABS() around the reference to $A2 the sign will be eliminated for formulas that don't already handle negatives. For Mike's version you could handle any number of places by replacing ROW($1:$6) in his formula with...

Hi Busyman -- to insert a blank column to the left of the table, shifting it all to column B onward before the new worksheet is finally placed in your target workbook.

If your existing formula works except for the negative numbers and false values, here's the problem: IF() should have three parameters IF(condition,truepart,falsepart) If the third parameter is missing, then when your condition is false, it will display "False". If you want it to be blank...

The fonts should be the same size printed as they are on-screen, as a proportion of the printed area of your worksheet. The cells will all fall inside the margin on a regular worksheet, so unless you chart falls just inside the edge of cells on the right and bottom, you will get extra space...

By the way, I see I mispoke and gave some misinfo earlier when I said If you type rate_str = rate_str & "_" & Tobacco as a Watch expression, it will never be true, but not for the reason I listed. It will never be true because nothing can be itself plus something else. If rate_str...

Awesome! It's often easier in the moment to just give someone the exact solution they need in the moment, or on the other end, just have someone else solve your problem for you.. I'm glad I was able to teach you some new skills in this arena! Asa

When it skips back to the beginning, I think that's where the code is broken. It skips back because it errored out, and then the function was called again by the next cell in the worksheet for calculation. F8 is "step into" which continues your code as usual, for one line. "Step over"...

I shortened my pointless version .. =StringConcat("",MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1)) It will also be more efficient since it won't build an array any larger than the source text (or any smaller, as the old version was limited to 256 characters).

I got the ROW() trick from http://stackoverflow.com/questions/350264/how-can-i-perform-a-reverse-string-search-in-excel-without-using-vba after checking the 5.7 mil + google results you mentioned :) That link is actually about a formula to retrieve the last word in a sentence. It was an...

I think it means Excel didn't find a table of data to do entry in. For no errors or popup messages, try this: * Make sure the first row of the worksheet has column headings, * Add at least one row of data * Convert the range to a Table if you are using Excel 2010 (don't think this is...

Hi infinitedrifter... My pleasure to help you. Your gratitude is reward enough. I can't delve into the file right now (I can look at it later if you are still stuck), but based on what you've said, some comments: will never be True; it is a text string. It's value will be something like...

magbo, I'm not an expert with some of the high-tech formulas, but could this be adapted to a single-cell formula? {=if(ROW($1:$256)>len(a1),"",MID(a1,LEN(a1)+1-ROW($1:$256),1))} This array formula if entered in a vertical range (or horizontal if you add TRANSPOSE) of cells will...

Hi! Yes, you can use View Code just as you describe, or you can: Hit the "Visual Basic" button on the left side of the Developer ribbon, or press ALT-F11 which will bring up the Visual Basic Editor. Then in the Project pane, find the name of the worksheet you want this to happen with, and...

Hello Auto, Perhaps you want: me.showdataform However, your macro will be paused until the user closes the data entry form. The data entry form is application modal - the user won't be able to click on another sheet, and your macro will pause execution until they close the form themselves...

Hi again, ok, found the culprit. The problem is not the code -- but the data in the AREA 1 RATES cell on the Constitution sheet (Constitution_Area_1 range). It contains: which looks fine, but the dash between "300" and "303" is not a normal minus sign/dash. I didn't track down exactly...

Hi infinitedrifter, One problem seems to be in the factor_search() function called by the above code. When the above code calls factor_search, code execution never returns fro the factor_search call! Possibly due to the code generating an error and something in all the workbook code preventing...

My pleasure. The topic interests me! I have a typo in the above... Where it said Asa

Hi Brian, Eliminate the quotes around $B38, or else Excel will expect column F to contain the literal text "$B38". Asa

I've updated the macro to be a little more flexible and sophisticated, and tested it :) A problem with the macro I first posted is that there was no way for YOU to save the workbook with blanks in it. This revision among other things, allows the user to save the workbook if they insist...

You're welcome, SAP... and welcome to Chandoo.org, by the way. If you need help with the Data Validation or Conditional Formatting (i.e. to highligt blank/required cells), check out these articles by Chandoo on how to "Make Awesome Data Entry Forms by using Conditional Formatting + Data...

Hello Srikaanth, To limit the values to A or V, you can use Data Validation. However, Data Validation cannot prevent blanks. To prevent blanks, you probably have to use a VBA macro. The following (untested) should out and out prevent the workbook from being saved if that cell is blank...