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
c.Activate
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.
...Ralph
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.
......Ralph
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
Thanks Will -
I was hoping I had missed something in the INDIRECT function.
Back to the drawing board.......
Ralph
Using Excel 2002 (XP)
I have a spreadsheet with many links to other excel workbooks - a typical link would look like - -
='F:/users/common/[0331mon.XLS]Sheet1'!$B$46
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.
