Weekly total transferred to other workbook

**saspinall** · 08-17-2011, 02:22 PM

Hi,

I run a football score predictions sheet for my sons football team and enter the scores from each person into a spreadsheet which then works out the points for me through the SIGN formula. I have a total number of entries from each agent in a cell, worked out by the COUNTIF option based on the agents name and also Sum these together to given total number of entries. I also keep a running total in another spreadsheet of how man each agent sell and number of entries, prize money etc....What I want to do is automate the total spreadsheet so it gets the numbers from the weekly sheet so have opened both WEEK1 weekly spreadsheet and in the running total spreadsheet said ='[Week 1.xls]Who''s Paid'!$K$4 What I want to do is then transport that down to the week 2 in the running total spreadsheet and have it look at WEEK 2.xls ie ='[Week 2.xls]Who''s Paid'!$K$4. Is there anyway of automating the week*.xls bit, it work if I physically change the number from 1 to 2 but thought it should do it automatically.

Use Excel 2007 now...

Stu

**rosilisk** · 08-17-2011, 03:17 PM

Hi Stu,

I'm not quite sure what you mean. Could you post a sample spreadsheet please?

**saspinall** · 08-17-2011, 04:18 PM

Originally Posted by rosilisk

Hi Stu,

I'm not quite sure what you mean. Could you post a sample spreadsheet please?

Cheers Rosilisk....heres the week1 and Money in and Out shts....so in the Money one, in row where it says week 2, I want it to be from week 2 spreadsheet....

**rosilisk** · 08-17-2011, 04:43 PM

Here's what you can do. It's probably a messy solution but it works. I'll describe what I did, but also post the resulting worksheet.

First, I created 2 helper cells, and used the CONCATENATE command to link portions of text together, along with the excel-generated number. This creates the formula you want, but unfortunately excel recognizes it as text, rather than a formula.

here comes the messy bit, if somebody knows a better way to do this then I'm interested to know too. Copy special the 'formulas' in column G, and paste special into column B where you want the formulas, pasting only the values.
Next, select the entire group of cells and do find and replace for a simple section of text, replacing for example Week with Week. This makes excel reevaluate the cells as equations, and it should work. Another way this can be done is selecting each cell individually, and clicking in the formula bar, then pressing enter.

Does that help?

**saspinall** · 08-17-2011, 04:55 PM

I see what you did, thanks...it works well....like you say, a bit messy but gets the right results....

Thanks

Stu

**rosilisk** · 08-17-2011, 04:59 PM

Sounds good. If your question has been answered, please mark the thread solved:

Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Edit button will not appear -- ask a moderator to mark it.