Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. JVFriederick

    I'm saying mostly goodbye

    Skip Your contributions have been outstanding over the years. Thank you very much for what you have done for me, and countless others. JVFriederick
  2. JVFriederick

    Excel- Sum only the values in column 3 which meet the exact criteria of colum 1

    I gave a star because the explanation why SUMPRODUCT function is advantageous is very good, I have not used it frequently in the past, but now plan to change. Particularly the part about > < operators within the formula, something that is otherwise very difficult to do. Thanks Skip!
  3. JVFriederick

    Create an array string from range.

    A formula only technique would be: Assume your 3 values are in A2,A3, A4 In cell B2, enter the following formula =B1&"'"&A2&"', " Copy this formula down for all desired records, don't forget to remove final delimiter on last row. You may experience problems trying to concatenate 5000 cells...
  4. JVFriederick

    Using a cell as a parameter in an OLE DB Connection using an SQL Statement to MS SQL Server

    KCUSHING Previously I did not notice you were pulling the date parameters from a query result. This may be cause of troubles. To troubleshoot, I would suggest manually typing each date in cells O2 and P2 and testing your query again. As mentioned in my previous remarks, Excel is problematic...
  5. JVFriederick

    Using a cell as a parameter in an OLE DB Connection using an SQL Statement to MS SQL Server

    KCUSHING Just a hunch here, but I noticed the database fields are datetime, which are often problematic when pulling dates from XL sheet. Would suggest you try parameters as (DATETIME > ?-1) AND (DATETIME < ?+1) to get past XL and database compatibility issues. I would not use the BETWEEN...
  6. JVFriederick

    Problem with PDF Viewing

    Caz Did you test sign the PDF yourself and save, then open and view in both Kindle and desktop? Would check the default settings for viewing signatures on desktop. In Adobe Reader (Edit Preferences Signatures) there are quite a few settings to check...... JVF
  7. JVFriederick

    Excel 2010 Re-assign MsQuery Connecton to Database

    Salut39 If you want something faster, please post before and after SQL statements, and before and after connection strings. Your original request stated the server location changed, which I incorrectly assumed meant just the IP address. Obviously more than just one thing changed, which...
  8. JVFriederick

    Excel 2010 Re-assign MsQuery Connecton to Database

    Salut39 There is a space after the = sign in your new connection string, would check that first. .....DATABASE= dstores;PORT=3306;......... Would then suggest manually creating new connection on another sheet and comparing the strings. Since you need changes to DSN, IP address, and DATABASE...
  9. JVFriederick

    Excel 2010 Re-assign MsQuery Connecton to Database

    Salut39 If you have a few connections (<10) the answer by Skip is the way to go. If you have tons, or the data source changes frequently, then some VB code can be handy. The following example uses the "connection description" to store a search / replace string used to make changes to ALL...
  10. JVFriederick

    Select all cells not previously selected from a list (range of cells)

    Gone Johnson.......
  11. JVFriederick

    Creating sequential count of transactions by Client ID in Excel

    Just when I thought I knew a little bit, Skip teaches me more! The created table is also automatically named (which can be changed to suit), the big payoff for me is that formulas will propagate as data is added (similar to db query object). Thanks for taking the time to mention this. JVF
  12. JVFriederick

    Creating sequential count of transactions by Client ID in Excel

    albop Assuming the range begins in cell A1, the formula in cell D2 would be =IF(A2<>A1,1,1+D1) Also attached example file http://files.engineering.com/getfile.aspx?folder=72cf05f0-2759-4a98-a9cd-8d3f539bfc71&file=TransactionFormula.xlsx
  13. JVFriederick

    Select all cells not previously selected from a list (range of cells)

    Just a hunch / suggestion...... Why not add single row with formula SUMPRODUCT((YEARSOLD=$B$2)*(STORE="RED")*(SALETYPE="RETAIL")*1)-SUM(B4:B36) That would calculate the number not shown in the fixed list. JVF
  14. JVFriederick

    Excel 2010 formula withing Formula problem

    Yes, that is the takeaway. If needed, I will use the Round function to 2 decimal places to remove annoying parenthesis. However, this should only be done on final values (not used elsewhere). Otherwise you will introduce rounding errors. JVF
  15. JVFriederick

    Excel 2010 formula withing Formula problem

    The important part of the kb article..... Another confusing problem that affects the storage of floating point numbers in binary format is that some numbers that are finite, non-repeating numbers in decimal base 10, are infinite, repeating numbers in binary. The most common example of this is...
  16. JVFriederick

    Excel 2010 formula withing Formula problem

    I think this long winded article may help explain : https://support.microsoft.com/en-us/kb/78113 XL sometimes 'displays' numbers out of sorts with 'reality', but you should remain confident that it will perform the math correctly when using the numbers. I have not found an instance yet where...
  17. JVFriederick

    Excel data from one sheet to another

    Matt Have been out for past week, unavailable to reply. I will post final results back to here so others may benefit. JVF
  18. JVFriederick

    Excel data from one sheet to another

    Matt M I may have missed in the original post where you wanted to transpose the results? I had to learn how to do that ! Thank you for the challenge, now I can use that for someone else. Since it's your birthday, I went ahead and added a sheet named 'Vertical'. I took a guess at the group...
  19. JVFriederick

    Excel data from one sheet to another

    Matt There are MANY ways to do this. If you are comfortable using a file with VB code today is your birthday. The attached file is designed to : 1) Browse to most recent XL file exported from PHP process. 2) Opens export file, finds last row and copies data into array. Closes export file. 3)...
  20. JVFriederick

    Excel data from one sheet to another

    Is the original data in XL format or something else (csv ?) If it is, would you also post this file? We could accomplish what you need in a single step.

Part and Inventory Search

Back
Top