# Posts by rem1224

• ## [Solved] Formulas: Hyperlinking Question

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

• ## Excel value finding

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. ?

• ## [Solved] Simulating Excel Behaviour - Is it Possible

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

• ## Formulas: Text in Cell use as filename in a link

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

• ## Formulas: Text in Cell use as filename in a link

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

• ## Formulas: Text in Cell use as filename in a link

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

• ## Formulas: Text in Cell use as filename in a link

Thanks Will -

I was hoping I had missed something in the INDIRECT function.

Back to the drawing board.......

Ralph

• ## Formulas: Text in Cell use as filename in a link

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.