Posts by rem1224

    Ryan -

    Not sure how to do it with a hyperlink, but you can do it with a simple macro that you could assign to a button.

    Let's say your date column is A1:A1300.
    You need to set another cell (let's say B1, to be the function =today().

    Then the macro would be :

    Sub GoToTodayRow()
    For Each c In Range("A1:A1300")
    If c.Value = Range("b1").Value Then
    End If
    Next c
    End Sub

    Hope this helps ........ Ralph

    Do you mean if A1 = B1 then copy numbers from A1, B1, C1, D1 somewhere else and so on through all the rows

    - or -

    Compare number in A1 to every number in Column B and if there is a match copy A1, Bx, Cx, Dx somewhere, then do the same with B1, etc. ?

    What Derk said will work well if you already have data in the sheet.

    If you are starting a new sheet and want that effect on Column B (or any range of cells), you could "pre-fill" each of the cells with a space without using a Macro - - - Highlight the column or range of cells, press {spacebar} once, then CTRL-ENTER to put a space in every hightlighted cell.

    I don't know of any way to accomplish the reverse (i.e. have long text in Col A show over existing text in col B) unless you use a TextBox object

    I've been away on another project for a few days.

    Thanks for the code ideas Will - - I think we were both heading down the same path with the idea of auto-generating the file names.

    I'll make a find/replace macro and that should help the data input situation a little.

    Thanks again for all your input - hope I can return the favor by posting some help to others on the board.


    Daily files are about 100KB and MANPOWER file is about 150KB - not super large by any means.

    I think I'll try making a FIND/REPLACE loop macro as a first shot. I could still use the original logic of the date/filename lists in A and B on sheet 2, then just loop through Column B to replace the filenames on Sheet1.

    Might be easier/faster than opening 84 files, updating links, the closing the files again.


    Good Morning Will and Jack -

    Sounds like everyone here enjoys a good mind twister.... and like you said Will, I too have spent hours trying to make a complex solution work, when all the while the simple way was right in front of me . . . but then again, slapping one's forehead for several minutes once in a while cleans out the cobwebs !

    Anyway, a few details on my project files for clarification:

    The application is tracking future daily manpower requirements for a labor company. There is one Excel workbook for each day of the year (named in a consistent format - "0623MON.xls" for Monday June 23, "0624TUE.xls" for Tuesday June 24, etc). These workbooks have the details of each labor job and have 4 totals (always in the same cell locations).

    There is a master workbook called MANPOWER.xls that shows a summary of 12 weeks worth of the totals in the daily workbooks. (4 totals per day x 7 days per week x 12 weeks = 336 links)

    Every Friday, week 1 is removed from MANPOWER and a new week is added - so somebody is manually keying in filenames for the new week and moving the rest of the weeks around on the sheet.

    Since the Daily filenames are a consistent format, I thought I could easily generate them in a list on MANPOWER-Sheet2 - - A1= 0101MON, A2=0102TUE, etc thru A365. Then I could copy/paste any 12 week period of filenames into say SHEET2!B1:B84. Day 1 on the MANPOWER Summary would alway pull its linked values from the filename in SHEET2!B1, Day 2 from SHEET2!B2, etc. Whenever the need to change the range of weeks shown on MANPOWER arises, just copy/paste a new group of dates (filenames) into B1:B84 and all the links are changed. Hence, no more rearrangement or retyping needed in MANPOWER.

    Sounds simple enough.....

    Best Wishes... Ralph

    Using Excel 2002 (XP)

    I have a spreadsheet with many links to other excel workbooks - a typical link would look like - -


    I'd like to be able to have the filename come from another cell - example Sheet2!A1 in the main workbook would contain the filename.

    I've tried using the INDIRECT function with limited success.

    =INDIRECT("[" & Sheet2!A1 & ".xls]Sheet1!$B$46")

    As long as the file I am linking to is open, it works (since the entire path is not necessary with an open file). If the linked file is not open, I can't seem to get the path in the right spot to complete the link.

    Any ideas would be appreciated.