Posts by teknovision

  • Link to data from multiple closed files

    Re: Link to data from multiple closed files


    Hi there!


    Thanks for your post unfortunately, the add-in is only available for Win machines, I use Mac OS X.


    Ideally, if I could have a column A with number 1 to 150 which would then be used to populate the formula in B which retrives data that would work however, I don't know whether it is possible to do that?


    A: 13
    B: ='ADMIN:Excel:Item 000013.xls'!date


    With 000013 being obtained from Cell A. bit tricky for and, any help would be muchly appreciated!!


    Many thanks,


    Philippe

  • Link to data from multiple closed files

    Hi All!!


    I would like to link data from files stored on my local drive.


    As there many files I would like to link to all with same filename convention, I would like to drag the first cell and go from (row 1 to row 150)


    ='ADMIN:Excel:Item 00001.xls'!date


    to


    ='ADMIN:Excel:Item 00150.xls'!date



    Any ideas on how I can do this? Also, I have some files 113 to 150 that do not exist as yet, how do I stop '#NAME?' from appearing, looks untidy?


    Many thanks for any help!!


    ./phil

  • IF Output

    Hi all!!


    I'm trying to compare to dates, if the completed (G9) is greater than the due date (F9) by using the following:


    =IF(G9>F9,FALSE,TRUE)


    In some cases I do not have a value in Column G and the output is defacto TRUE, how can I get it to display nothing if there is nothing in G?


    I was then thinking of adding a conditional formatting on Column G to say that if Cell G = FALSE the cell would be filled RED - is ther a better way of doing this?


    Many thanks,


    .//p

  • Copy Worksheet Columns

    Hi all!


    I would like to have two worksheets, one with 'data' that contains sensitive information and the other which can be mailed out to the 'users'. However, I only want to be working/updating the 'data' spreadsheet, I would like the 'users' spreadsheet to update itself automatically from columns A to C in 'data'.


    I tried using '=' and then clicking on the cells I want to pull the information from but it creates loads of 0 values in the cells and doesn't important formating ie. cell background etc..


    Apologies for the convoluted fashion in which I have described what I'm trying to achieve, I hope someone can help me?? I've also attached an example, hope that helps!!


    Many thanks for any help!!


    ./phil

  • Last Updated

    Hi all!!


    I would like to have a cell in my spreadsheet that informs the user when the spreadsheet was last updated/saved, any ideas?


    Cheers,


    ./p

  • Shared Workbook Locked

    Hi all!


    Had a look in the historical posts on file locks etc but, found no solution to this little problem. A spreadsheet is shared on our Server 2003, we use Excel 2002 on XP workstations. The spreadsheet contains two worksheets and the Share Workbook settings, the following settings are checked:


    'Allow changes by more than one user...'
    'Don't keep change history'
    'Auto Save every 15mins' with 'Save my changes and see others' changes'
    'Ask me which changes win'
    Print settings
    Filter settings



    After many days of happy saving.. . yesterday, the file locked and no one (4 users had opened it) could save the spreadsheet. An error appeared with:


    This file is locked. Try the command again later.


    I can't figure what could have caused this, the article below on MS didn't help either:


    http://support.microsoft.com/d…ch.asp%3FArticle%3D129276


    I could not find a solution and we had to save and started afresh by copying data into a new spreadsheet and setting configuration as above. Naturally, I would like to avoid this in the future and/or be able to rescue the problem without having to start afresh, any ideas would be more than welcome to avoid this in the future? Many many thanks.. .


    Phil

  • Month - first date and last date

    Similar but Different Query


    Hi all!


    Thanks for the post below, I have a similar sort of issue but I have month in B3 (March) then the year in C3 (2004), I would like D3 to use the information in these two cells to output the end of the month ie. 31 March 2004.


    I tried =DATE(YEAR(B3),MONTH(B3)+1,0)
    but I get #VALUE


    Any ideas would be muchly appreciated.. . Many many thanks,


    Philippe

  • Sum Lookup

    More than one way to cook an egg.. .


    Thanks guys both suggestions work, thank you!!! Why does it always seem so 'easy' once you have the answer!! ;)

  • Sum Lookup

    Hi all!!


    I have two columns, Column AF contains a Yes or No drop down (Range AF25 to AF39) and AC contains in times in the hh:mm format (AC25 to AC39). I then have a cell G33 which contains the total amount of time.


    I would like G33 to SUM only the times that have 'No', example below:


    Yes 1:00
    No 0:10
    No 0:05


    In the above example, Cell G33 would then display 0:15.


    I think it's some sort of LOOKUP formula but just can't get my head around this one.. . Any help would be muchly appreciated?


    Many many thanks,


    Phil

  • Find & Replace in Charts

    More Detail


    I think I forgot to mention that there are 15 charts generating data from 15 separate worksheets.. . Dave, from your example that would still mean that I would need to change source data right?


    Cheers,


    Phil

  • Find & Replace in Charts

    Hi there!


    I have about 15 charts in one Excel spreadsheet that I need to change the source data for, the master spreadsheet has been updated.


    I cannot seem to do a Find & Replace, is there another way other than having to manually go through each charts source data?


    Cheers for any help,


    Phil

  • Delete Sheets Macro

    Compatability


    Thanks again!!


    As always you were correct unfortunately it moved alone to the next issue (if you still have patience with me, wow!!) and stopped at: 'Range("A:B").SpecialCells(xlCellTypeVisible).Copy' in the following:


    Dim s As Worksheet
    Range("A:B").SpecialCells(xlCellTypeVisible).Copy
    Set s = ActiveSheet
    Sheets.Add
    ActiveSheet.Paste
    Application.DisplayAlerts = False
    s.Delete
    Application.DisplayAlerts = True



    Is this also a compatability issue if so, how do I troubleshoot these issues is there a guide that will tell me these things?


    Many many many thanks!!


    Phil

  • Delete Sheets Macro

    Solved but.. .


    Thanks again Derk!!!


    What is rather odd though is that I don't get any errors on my XP with Excel 2002 but my colleagues who also has Excel 2002 (and my mac with Excel 2004) both get an error on the very last line:


    lrow = Range("A65536").End(xlUp).Row
    Range("C2").AutoFill Destination:=Range("C2:C" & lrow)
    Range("C2:C" & lrow).Select
    Cells.Select
    Range("A1:C20000").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range( _
    "G1"), Unique:=True
    Columns("G:G").Select
    Selection.TextToColumns Destination:=Range("G1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True



    How come my XP with Excel 2002 is quite happy with it all and others freak out, is there a setting I must change somewhere?


    Cheers,


    Philippe