5460 530 Posted October 22, 2010 Share Posted October 22, 2010 ok so I am tidying up some duty hour sheets. In essence I have a master doc for totals, and individual sheets for every SC to put in their hours. On the master doc to simplify I have: cell A: Station name cell B: collar number cell C: external reference to another xls file What I would like to do is in the external reference is to use data from cell A and B to form the path. For example... A: London B: 111 C (currently): ='C:\forms\Format\London\111\[sC 2010.xls]Summary'!$L$21 What I would like it to do so I can format a sheet easier is: ='C:\forms\Format\*cell A*\*cell B*\[sC 2010.xls]Summary'!$L$21 is this even possible? to add an update, lets assume this master is in the root of a folder, and then the subsequent files are in a folder... i.e. c:\temp\master.xls c:\temp\london\1.xls c:\temp\london\2.xls etc Could I just reference like: ='%\london\[1.xls]Summary'!$L$21 ? Link to comment Share on other sites More sharing options...
Garion 0 Posted October 22, 2010 Share Posted October 22, 2010 (edited) ok so I am tidying up some duty hour sheets. In essence I have a master doc for totals, and individual sheets for every SC to put in their hours. On the master doc to simplify I have: cell A: Station name cell B: collar number cell C: external reference to another xls file What I would like to do is in the external reference is to use data from cell A and B to form the path. For example... A: London B: 111 C (currently): ='C:\forms\Format\London\111\[sC 2010.xls]Summary'!$L$21 What I would like it to do so I can format a sheet easier is: ='C:\forms\Format\*cell A*\*cell B*\[sC 2010.xls]Summary'!$L$21 is this even possible? ="'C:\forms\Format\"&A2&"\"&TEXT(B2,0)&"\[sC 2010.xls]Summary'!$L$21" That'll give you the location and can be dragged down to all cells, Then you need to copy and paste special - values for all of them, Then what you need to do is open the Find/Replace and put C:\forms into both the Find and Replace with field and click replace all, this will convert Excels recognition of the location text string into a formula and will then complete the value from the location you need it Edit: Correct Cell reference to match OP Edited October 22, 2010 by Photojonic 1 Link to comment Share on other sites More sharing options...
5460 530 Posted October 22, 2010 Author Share Posted October 22, 2010 Shouldn't the text be on the text box and not the number?.... Didn't work either way, but then the A cell is a multiple choice pull down box. Tried a normal format also and that didn't work..... Next question, is as my master xls is referencing 76 other sheets, and I need to send a copy of master xls to a colleague who doesn't have access to the other sheets, how can I export it or make a "gold" copy (where it takes the reference and puts it in as a number in the cell and not a reference) so I can send in essence a snapshot? Whilst I am using 2007, it will be on a office 2003 based system. Link to comment Share on other sites More sharing options...
TallGuy1546081014 + 557 Posted October 22, 2010 Share Posted October 22, 2010 I've been trying to do something similar with a skills matrix but rather that creating the path I wanted to specify the file name at a known location. Master sheet just has collar number and from this value it then retrieves the data from the relevant cells from each workbook called <collar_number>.xls. However I can't find a way of getting a formula inside the braces that define a workbook reference. So may revert to the alternative plan of sorting the input files into directories as per the OP. Next question, is as my master xls is referencing 76 other sheets, and I need to send a copy of master xls to a colleague who doesn't have access to the other sheets, how can I export it or make a "gold" copy (where it takes the reference and puts it in as a number in the cell and not a reference) so I can send in essence a snapshot? Whilst I am using 2007, it will be on a office 2003 based system. Create a copy of the document e.g. snapshot<date>.xls open it and then Edit > Links> Break Links. This converts all the dynamic cell values into static values. Link to comment Share on other sites More sharing options...
Garion 0 Posted October 22, 2010 Share Posted October 22, 2010 (edited) Create a copy of the document e.g. snapshot<date>.xls open it and then Edit > Links> Break Links. This converts all the dynamic cell values into static values. Theres a far simpler and quicker way, which is to copy the whole s/s into a new book, but paste special values.. this will paste the values rather than the formulas and will never change. No need to screw around with links back to other documents.... 5460, what bit didn't work? Edited October 22, 2010 by Photojonic Link to comment Share on other sites More sharing options...
Duffman 40 Posted October 22, 2010 Share Posted October 22, 2010 5690, what bit didn't work? Sorry to interrupt but '5690'? What is that...? ="'C:\forms\Format\"&A2&"\"&TEXT(B2,0)&"\[sC 2010.xls]Summary'!$L$21" That'll give you the location and can be dragged down to all cells, Then you need to copy and paste special - values for all of them, Then what you need to do is open the Find/Replace and put C:\forms into both the Find and Replace with field and click replace all, this will convert Excels recognition of the location text string into a formula and will then complete the value from the location you need it Edit: Correct Cell reference to match OP I haven't got a clue wot you're talking about but I gave you a +1 anyway cos it looks good! As The Prince Regent said to Blackadder: So, which one of us is Wellington.....? Link to comment Share on other sites More sharing options...
TallGuy1546081014 + 557 Posted October 22, 2010 Share Posted October 22, 2010 Theres a far simpler and quicker way, which is to copy the whole s/s into a new book, but paste special values.. this will paste the values rather than the formulas and will never change. No need to screw around with links back to other documents.... The way I proposed involves 6 mouse clicks and preserves everything about the original spreadsheet including any page set up, conditional formatting, images, cell rows, widths etc. SaveAs, Open last, Edit, Links, Break Links. Job done. What could be simpler than that? Could even write a Macro to do it in one click. Have not found a solution using PasteSpecial that preserves all of the features of the original file that is as simple. Link to comment Share on other sites More sharing options...
5460 530 Posted November 4, 2010 Author Share Posted November 4, 2010 5460, what bit didn't work? The formula wouldn't resolve. I've statically done all the links so now works, I will play with your formula when I get some downtime though Sorry to interrupt but '5690'? What is that...? Your boss Link to comment Share on other sites More sharing options...
Recommended Posts