Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Formula Help Excel 2003

Sep 20, 2010 9:20AM PDT

I am tracking 50 to 100 areas at any one time in an equal number of spreadsheets. All the spreadsheets have identical layouts. I would like to link these files to a master roll-up file. I need two elements to change in my links based on two identifiers, area number and name. In Lotus, yes I'm that old, I could just designate a cell to each and each time I changed the information in the cell the formula would change. I'm sure that there is an Excel way to do this, I just don't have a clue. Here's the formula D:\Documents and Settings\mtontini\Desktop\PCDS Trackers\[PCDS Tracker TX141 Austin 2011.xls]Summary'!$C$5
The two variables that I want to have change based on a cell's input are TX141 and Austin.
Thanks for any help you can offer.
Thanks

Discussion is locked

- Collapse -
Re: formula help
Sep 20, 2010 7:25PM PDT

Make an onchange event for the source cell that updates the formula in the target cell. That's a nice exercise in VBA (not the most easy thing to start with, I'm afraid).

Kees

- Collapse -
Yep, VBA is the way to automate this
Sep 21, 2010 10:24PM PDT

but personally I would just do it manually.

By the time you have worked out how to automate this with a VBA macro you could have done it by hand.

It may take a few hours, but that doesn't matter.

Do you want this master roll-up file in the same workbook, or in a new workbook? The method would be similar; in the master sheet, select the cell you want for your first link, type in the = sign, then navigate to the first sheet you want to link to, highlight that first cell, then press Enter. You will see the master sheet cell complete with the link to that first cell. Repeat this action for all relevant data.

The only difference to a new worksheet in the same workbook, or to a new workbook, is that with a new workbook, if you now closed down the data workbook you will see that link in the new workbook change to the 'full path' and not to the shortened version. It would change back to the shortened version if you re-opened the data workbook.

Mark

- Collapse -
Yep, VBA is the way to automate this
Sep 22, 2010 12:21AM PDT

Hi Mark,

Thanks I will use your technique as it will take a while to get it to work but then I'm going to free up a couple of hours a week for the next year.

Mike

- Collapse -
(NT) LOL. That sounds about right.
Sep 22, 2010 2:56AM PDT