reference cell data in another workbook by matching date text in the sheet tab

[pmsrefugee]

I have a workbook (WSWD) with the same page copied to multiple tabs. A new tab for each week to track weather and construction days left in the project. I label each tab 03-02-2013_41-PE followed by 03-09-2013_42-PE, etc. I have another workbook (RE Weekly) I need to copy cell data from WSWD. It also has a page copied to multiple tabs 03-02-2013_RE Weekly, 03-09-2013_RE Weekly, etc.


What I want to do is have each sheet in RE Weekly reference the respective sheet in WSWD by matching the date i.e., 03-02-2013 in the sheet tab. That way if cell A1 in WSWD sheet 03-02-2013_42-PE is 209, then I can have a cell C2 in RE Weekly, sheet 03-02-2013_RE Weekly also read 209.


That way, when I add the next week's sheet by copying and moving to end, the new sheets will reference each other by matching the date text in the sheet tab.


I don't know VBA, but I can cut and paste to a module like a pro!

 

[Luke M]

Ideally, we would like to use the INDIRECT function to build the text string needed to create the proper referencing formula. However, INDIRECT doesn't work on closed workbooks, so it would only be functional if both workbooks are open as same time (usually not a viable option). So, our only alternative is to use VB.


Before that, need to know a few more things.

1. What determines the number at end of worksheet in the WSWD file? E.g., why does 3-9-2013 get a 42, and 3-2-2013 get a 41?


2. The sheets in RE Weekly, do all the external referece formulas need to go to same sheet? As in, they would all reference the same sheet in the WSWD file.

 

[pmsrefugee]

1. Each new sheet in numbered sequentially each week i.e., 41, 42, 43, etc

2. Yes, all the formulas in RE Weekly reference the same week in WSWD. That is why I though if we could match the date in the sheet tab, I could add a new sheet by copying the latest sheet to the end and changing the date so the sheet tab in RE Weekly and WSWD would have matching dates within the tab's name.

 

[Luke M]

While searching around for ways to get started, I came across this similar thread:

http://www.ozgrid.com/forum/showthread.php?t=44975


Which mentiond the INDIRECT.EXT add-in. If this option would work for you, it would be the easiest way to go.

Along with that thought, and for future thought, we'll need to use this formula to get the current sheet name:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,999)

 

[pmsrefugee]

I can't figure out the INDIRECT function. If I have both workbooks open, I would like to have one sheet reference a cell in the other workbook. But when I add a new sheet by copying to the end to each workbook and renaming the sheet tab with the new date I need the reference to update the sheet to the new date. for example the date in the reference

='Cat 27_Seg 8_WSWD_CEM-2701.xlsm]03-09-2013_45-PE'!$J$36 needs to change to

='Cat 27_Seg 8_WSWD_CEM-2701.xlsm]03-16-2013_45-PE'!$J$36 when copied to the next week's sheet.

If I could get the date in the reference to match the date in the sheet tab using =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,999)or INDIRECT that would work, but I don't know how to insert that into the cell reference. It's very late, and this probably doesn't make any sense. sorry

 

[Luke M]

It'll be a little cleaner if we designate a cell in our sheet to generate sheet name. In that cell, put this formula:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,999)


Now, whenever you copy the sheet, this cell (let's say it's A1) will show your sheet name.

Now, IF INDIRECT let you work with closed workbooks, your formulas then would become something like:

=INDIRECT("'C:My Documents[Cat 27_Seg 8_WSWD_CEM-2701.xlsm]" & $A$1 & "'!J36")

You can see this work with both workbooks open, but when you close the WSWD workbook, it won't work anymore. I've not used the INDIRECT.EXT function myself, but it looked promising from the initial look. Hope that things look better in the morning.

 

[pmsrefugee]

Between the =MID formula, Concanatate, and indirect plus this site http://www.cpearson.com/excel/sheetref.htm I was able to get it to reference the other workbook and match the new date when I add a new sheet to the end. Thank you very much!