Good morning everyone
Apologies in advance if this is a real beginners error but I just can't figure out why its doing this and its causing quite a problem.
Background:
I have a master spreadsheet that we use to plan transport based on product lines that have been requested by my customer and the sheet also calculates the transport costs depending on the method we have chosen. We have to tweak the plan to get the best value for the customer so it is crucial that these prices are updating as we chop the plan around.
So I work on the pricing and the formulas and then email the sheet to my colleague who saves the master template in a folder only she has access to for security. She then plans the work and sends it back to me.
The formula references a sheet within the workbook (namely: Lymedale, Washington, Brackmills which all represent physical locations) get changed to a full file path as per below examples, so don't worry about what the formula is doing, it works fine in the original format, but once emailed the file-path references cause lookup errors etc.
Original formula:
Formula after sheet being emailled:
Any ideas welcome...
Thanks in advance
Robin
Apologies in advance if this is a real beginners error but I just can't figure out why its doing this and its causing quite a problem.
Background:
I have a master spreadsheet that we use to plan transport based on product lines that have been requested by my customer and the sheet also calculates the transport costs depending on the method we have chosen. We have to tweak the plan to get the best value for the customer so it is crucial that these prices are updating as we chop the plan around.
So I work on the pricing and the formulas and then email the sheet to my colleague who saves the master template in a folder only she has access to for security. She then plans the work and sends it back to me.
The formula references a sheet within the workbook (namely: Lymedale, Washington, Brackmills which all represent physical locations) get changed to a full file path as per below examples, so don't worry about what the formula is doing, it works fine in the original format, but once emailed the file-path references cause lookup errors etc.
Original formula:
=IF(AND(BL2="XD",L2="H"), VLOOKUP(M2,'Matrix 1'!A:B,2,0) / SUM(COUNTIFS(Brackmills!BP:BP, BP2, Brackmills!L:L, "H"), COUNTIFS(Lymedale!BP:BP, BP2, Lymedale!L:L, "H"), COUNTIFS(Washington!BP:BP,BP2, Washington!L:L, "H")),0 )
Formula after sheet being emailled:
=IF(AND(BL2="XD",L2="H"), VLOOKUP(M2,'C:\Users\robin.shaw\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.Outlook\IUTMJVBK\[RDC Master .xlsx]Matrix 1'!A:B,2,0) / SUM(COUNTIFS('C:\Users\robin.shaw\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.Outlook\IUTMJVBK\[RDC Master .xlsx]Brackmills'!BP:BP, BP2, 'C:\Users\robin.shaw\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.Outlook\IUTMJVBK\[RDC Master .xlsx]Brackmills'!L:L, "H"), COUNTIFS('C:\Users\robin.shaw\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.Outlook\IUTMJVBK\[RDC Master .xlsx]Lymedale'!BP:BP, BP2, 'C:\Users\robin.shaw\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.Outlook\IUTMJVBK\[RDC Master .xlsx]Lymedale'!L:L, "H"), COUNTIFS('C:\Users\robin.shaw\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.Outlook\IUTMJVBK\[RDC Master .xlsx]Washington'!BP:BP,BP2, 'C:\Users\robin.shaw\AppData\Local\Microsoft\Windows\Temporary Internet Files\Content.Outlook\IUTMJVBK\[RDC Master .xlsx]Washington'!L:L, "H")),0 )
Any ideas welcome...
Thanks in advance
Robin
