Excel spreadsheet reference issue?

I have a spreadsheet with tabs A and B. A has figures I have input, B is a calculation page based on A's figures.


I have another spreadsheet with tabs C and D. C is identical to A but has different figures on it, therefore even though the formulae are identical on B and D they will show different results (as they are based on different input figures).





Now assume D is blank (and there are a hundred of these spreadsheets all with blank 'D's'). I want to copy B to D, F, H etc... so that the formulae are identical to that of B's. BUT if I copy and paste the formulae the new B's reference back to A rather than C, E, G etc...





Can this be done?

Excel spreadsheet reference issue?
Is probably an easier way than this, but if u highlight the entire sheet in which you want to change the formula on, hit Ctrl H to replace, where it says 'find what' put in the sheet ref in the formula eg. sheet A, and in the 'replace with' put in sheet that you want to read formula from eg sheet F etc.
Reply:You will need to change all the references yourself, Use the find/replace function





Copy the sheet, and highlight the whole sheet, next click on the home tab and Find/Replace. Click on advanced options and make sure that Find in Formulae is enabled.





Search for A! (the referance to sheet A) and replace with C!, E! or G! the (referance to the sheets you want)





Click replace all and your done!
Reply:You can either


i) Do as you are doing and then do a global find %26amp; replace on the CD EF sheets so that the formulae reference the correct page


ii) Reverse the process and paste the values from C E etc onto the AB sheet and save over the blank sheets.