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!

Recent content by xlbo

  1. xlbo

    Need to Capture MIN CLAIM ID - SQL Server

    This would be more for the sql server forum and I would imagine they will want a bit of detail about table structures etc Rgds, Geoff We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in...
  2. xlbo

    Excel SQL query not working with dates

    Phv has it but please note that you should never use select * as if the table changes at all it could wreck whatever is relying on it. For the sake of a fee seconds to script out the field names, it is well worth it to abou future issues Rgds, Geoff We could learn a lot from crayons. Some are...
  3. xlbo

    How can I do a rolling 12 months calculation

    F1 is your friend ;) Offset( Base position Rows down to move Columns across to move Range depth Range width ) Counta(f:f)-13 is the start cell in your Rolling 12 range 0 is the number of columns to move across 12 is the depth of the range 1 is the width of the range Rgds, Geoff We could learn...
  4. xlbo

    checking field length in CSV file

    If you have access to a sql database, and you have a lot of rows to deal with, a quicker option would be to import from your semi colon delimited text file into a sql table which is set to be char rather than varchar values for each column. This will automatically pad the loaded data to the...
  5. xlbo

    Left Padding data

    Cat .skin. Many ways! =rept("0",13-len(a1))&substitute(a1,".",".") Untested Rgds, Geoff We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box. Please read FAQ222-2244 before...
  6. xlbo

    How can I do a rolling 12 months calculation

    =sum(offset(f1,counta(f:f)-13,0,12,1)) Untested Rgds, Geoff We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box. Please read FAQ222-2244 before you ask a question
  7. xlbo

    Vlookup not working #REF error

    Also - unless you intentionally want to change the lookup range, use $ to make the range relative rather than absolute source!$A$2:$C$10470 Rgds, Geoff We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but...
  8. xlbo

    VBA conditional Formatting Excel 2003

    Just as an fyi - this is probably the best thread discussin conditional formatting in VBA (>3 conditions): thread68-223068 Rgds, Geoff We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in...
  9. xlbo

    ERROR [07002] [Microsoft][ODBC Excel Driver] Too few parameters. Expected #

    SQLScholar - It's an excel driver thing... ebell1 - what happens if you try: SELECT [Task_Type], [Status_Change_Date],[SourcingEvent] from [ContractsWorkflowReport$] Rgds, Geoff We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and...
  10. xlbo

    import excel sheet1 to access table

    Loading to an access table using Microsoft OLE DB Provider For Sqlserver ??? Rgds, Geoff We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box. Please read FAQ222-2244 before...
  11. xlbo

    What can anyone tell me about InfoPath?

    Not used it in a fair while (4 or 5 years) but the version I used was horrible. It's not intuitive and difficult to configure. If you are looking to go down the route of eForms I would suggest just using ASP.NET instead Rgds, Geoff We could learn a lot from crayons. Some are sharp, some are...
  12. xlbo

    Get rid of NaN for percentage calculation

    You need to filter out the morethan1day values where TN = 0 on both sides: =sum(IIF(Count(Fields!TN.Value)= 0, 0,1-(Sum(IIF(Count(Fields!TN.Value)= 0, 0,Fields!MoreThan1Day.Value)))/Count(Fields!TN.Value))) Rgds, Geoff We could learn a lot from crayons. Some are sharp, some are pretty and...
  13. xlbo

    Access 2010 Iif Query

    Not sure if the syntax would be different for Access (probably) but this is how I would write it in SQL: UPDATE SampleCombined SET [Not on Product Look-Up] = 'Yes' FROM SampleCombined a INNER JOIN (SELECT UniqueRef FROM SampleCombined WHERE excl_prod != 'Yes' and no_lookup = 'Yes') b on...
  14. xlbo

    Excel 2010 using ISNA in and IIF to do something

    another thing to note is that if you have a large data set, you may be better off testing for NOT(ISNA) Due to the way excel calculates, you are better off setting the true part of the IF formula to the most common occurence If you expect more NAs than matched values then leave as is. If you...
  15. xlbo

    Excel 2010 using ISNA in and IIF to do something

    just FYI, you do not need the =TRUE part =IF(ISNA(VLOOKUP(B80, Base!D:AZ,44,FALSE))=TRUE,PERSONAL.XLSB!FindName(B80,"BASE"),(VLOOKUP(B80, Base!D:AZ,44,FALSE))) can become =IF(ISNA(VLOOKUP(B80, Base!D:AZ,44,FALSE)),PERSONAL.XLSB!FindName(B80,"BASE"),(VLOOKUP(B80, Base!D:AZ,44,FALSE))) as...
See more

Part and Inventory Search

Back
Top