data file to file

[k13ehr]

Hi, I'm a newbie so not sure of the etiquette yet on here, so I'll just dive in and ask what is probably a stupidly easy question, but is causing me a problem.

I have 2 Excel files one is a weekly accounts page which duplicates the Simplex D book keeping system, the other takes data from the weekly sheet to make totals of income and expenditure to enable me to

I have successfully made the weekly page work and it comprises 52 sheets for weeks 1 to 52.

I now need to extract data from the cells in this file to another called summary, which I've been doing with the simple but very slow and potentially inaccurate method of.

Select cell in summary, press =, then go to weekly file select cell in Weekly I need data from, press enter, and then repeat for the next cell, this takes an age to do.

Is there a simple formula for doing this, as there are as mentioned 52 sheets in one file, and 15 columns x 52 rows to cross reference at the very least



It may be that having just one file comprising weekly and summary might have made this easier, but from a newbie point it's easier to have both file open to more easily go from one to the other, and I'm not sure how I would recombine the two files.

I'm too new to know if I can send my email to anyone on here, but I do have a copy of both files with some dummy data in if that would help anyone to see the problem.

I'd be very grateful for a simple solution to this problem.

Cheers Kev.

[Cheeky Charlie]

Hi, you can upload examples to the forum when editing a post (edit - go advanced - manage attachments is at the bottom of the edit window).

It's not easy to picture your problem, but:
(a) using different sheets in the same workbook makes them significantly easier to work with
(b) you can select the fill handle (little black square on the corner of the active cell) and drag it to copy formulae - this may be all you need?

CC

[k13ehr]

Thanks CC, but that's not the problem.

It's difficult to explain other than the way I have.

Kev.

[JBeaucaire]

It may be that having just one file comprising weekly and summary might have made this easier, but from a newbie point it's easier to have both file open to more easily go from one to the other, and I'm not sure how I would recombine the two files.

1) Open both files
2) Right-click on the Summary tab and select Move or copy
3) In the To Book: section, select the weekly workbook
4) In the Before Sheets section, select move to end
5) Click OK.
6) Save the weekly workbook with the Summary sheet now in it.

[k13ehr]

Thanks JBeaucaire,

I'm not sure I actually need or want to move the summary file contents into the weekly file yet, as I don't see any benefit and non has been shown to me, I was just stating I wasn't sure how it would be done if it did prove to be better for my solution.

At the moment I can click a cell in summary, press =, then go to weekly as it's already on the correct page and click the correct cell and then enter and do it all again for the next cell, which if I don't find a solution to, I'll be doing for several hours.

The method I have tried to make life a little easier was to do a whole row, as described, then copy the cells down, but this makes the weekly worksheets all incorrect IE

='C:\Users\Desktop\[Dummy weekly accounts x 52.xlsx]1'!$I$41+'C:\Users\Desktop\[Dummy weekly accounts x 52.xlsx]1'!$J$41
='C:\Users\Desktop\[Dummy weekly accounts x 52.xlsx]2'!$I$41+'C:\Users\Desktop\[Dummy weekly accounts x 52.xlsx]2'!$J$41
='C:\Users\Desktop\[Dummy weekly accounts x 52.xlsx]3'!$I$41+'C:\Users\Desktop\[Dummy weekly accounts x 52.xlsx]3'!$J$41

the red numbers are just to show what I had to alter for each cell to change the sheet numbers.

You may ask why an almost duplicate instruction in the same formula, my way of making it look at 2 cells adjacent to eachother, one for amounts paid out/in in cash and the other for direct debits/cheque payments.



they all get data from the correct cell and file, just the wrong sheet, so I had to highlight the section in the formula bar and type in the correct week sheet number, if you know what I mean, so a workaround for that would work just as well for me.

dontcha just lurv Excel.

Kev.

PS actually I do think it's great but I do struggle with it[/SIZE]

[JBeaucaire]

The SIMPLE formula would probably be an INDIRECT() formula. You can indirectly create a reference so that it increments part of the reference that wouldn't normally increment.

For instance, if you wanted to reference a sheet called 1 and sum two cells, it would normally be:

=SUM(1!$I$41:$J$41)

You can rewrite that indirectly, substituting the 1 for a row reference on the sheet the formula is in. Let's say that formula is in row4. ROW() = 4 in that instance, so you would need to subtract 3 from that result to get the right answer.

=SUM(INDIRECT(ROW() - 3 & "!$I$41:$J$41"))

Now, when you copy that down, the next formula would resolve to a sheet called "2", then "3", etc.

Unfortunately, unless you want to start adding in UDFs or VBA or fancy Add-ins, you can't use INDIRECT() on another workbook unless that workbook is always open.

So, put your summary in the workbook with the weekly sheets, and then your INDIRECT() formula construct would always work.
======

EDIT:
And before the INDIRECT() police arrive, note that INDIRECT() is a volatile function. That means it updates all the time, even when it doesn't need to. That's only a problem if you create a humongous number of these formulas. If you do, you may detect your sheet starting to get sluggish. In massive numbers, volatile functions updating constantly can make things unbearable. Keep that in mind.

[k13ehr]

Hi JB I'm hoping to send a small sample file as I am struggling to make the changes to your formula so it will work for me.

There should be a file attached if I did it right.

Cheers Kev[/SIZE]

[JBeaucaire]

Try this in D5 then copy down:

=SUM(INDIRECT("Sheet" & ROW() - 4 & "!$I$20:$J$20"))

....or this if you want to be able to copy to the right, too:
=SUM(INDIRECT("Sheet" & ROW() - 4 & "!I" & COLUMN() +16 & ":J" & COLUMN()+16))

[k13ehr]

I give up, that did nothing but post REF# and made the text bold for some insane reason.

I thank you very much for your help, but this is harder than doing it the slow way, so if you pass someone on the street going "SELECT = SELECT ENTER" repeatedly just walk on by.

Thank again Kev.

[JBeaucaire]

The #REF might be because your formulas are referring to sheets you haven't put in your workbook yet. you can add another check at the beginning of each formula to see if an error is going to occur, and only display an answer if the sheet has been created. Like so in D5:

=IF(ISREF(INDIRECT("Sheet"&ROW()-4 & "!I" & COLUMN()+16)), SUM(INDIRECT("Sheet" & ROW()-4 & "!I" & COLUMN()+16 & ":J" & COLUMN()+16)), "")

This is a hard formula to get used to reading. It's designed to be able to copy across in each "section", but maybe that makes it too hard to read. You could just customize the formula for each column in row 5, then copy that row down...

Here's your sheet back...it has INDIRECT() formulas in B5:P15 to show you it can work. Even though sheets3-13 haven't been added yet, no #REF errors!

Now, just copy a sheet and name it SHEET3 and the next row will immediately fill itself out..

[Cheeky Charlie]

It's difficult to explain other than the way I have.

It's easier with examples... your subsequent upload explains the probem really well!

A few points:

JB: I agree, indirect is easier than alternatives - a little volatility is not too high a price, IMHO for the simplicity of your solutions - lol @ defensiveness though - how is it in the fort?

k13:

as I don't see any benefit and non has been shown to me

for a start, formulae can look like: =sheet1!A1 instead of 'C:\Users\Desktop\[Dummy weekly accounts x 52.xlsx]1'!$I$41 (and this is with an example saved in a simple location!) - this makes them not only easier to understand, but also improves the reliability of the work to function (some formulae will not work unless the "other" workbook is open). It is possible to take recommendations from people who perhaps know what they're talking about without insisting on proof.

JB:Where possible, you could consider replacing row() - x with row(1:1) - it's one less calculation and it's slightly easier to read - because of the fewer calculations, but also because the evaluated row is shown in the formula content.

Both: The REF! errors almost certainly come from missing sheets, in my opinion, although JB's suggestion is a fine formula, you are better off showing errors like this (particularly ref errors) as the error is telling you something is wrong in your workbook - something you would probably want to fix - perhaps best not to hide it?

I think JB's solution:
=SUM(INDIRECT("Sheet" & ROW() - 4 & "!I" & COLUMN() +16 & ":J" & COLUMN()+16))
(perhaps including fix to row(1:1) et sim. for column(), but this is functionally no different)
is the best for your needs, and I would simply insert the relevant sheets to fix the error, rather than hide it.

HTH

[k13ehr]

, sorry for the delay life intervened, again!!!

The indirect file you sent last night looks promising, but this password thing is stopping me using it the way you have it set up, can it be changed to refer to the weekly file?

I was looking at the cell references too (added a bit more data to sheet 1 & 2 and attached it for clarity) I really can't see how they work, they seem to be all the same except for a few, IE cell C5 is different to D5 to I5, then it changes again for J5 to M5 and then again it changes, not complaining, but it would help to be able to understand it better, as I'll need to scale it up, and obviously use it year on year with some changes as other suppliers of services, and as kind and patient, as you've been I don't think you want to do my accounts for me, and I would prefer to be independent as far as I can be, lack of Excel knowledge not withstanding

As for the password thing, it might be easier to put the weekly files into the summary file or even just start from scratch with one single password for all protection, not entirely sure I can do that on my own though as this episode has mainly served to confuse me.

I suppose it would be easier to have the 52 sheets for weekly cash accounting then have the other summary sheets about 6 of them all in one place, I've spared you the job of adding those into the equation as it's mainly prop drawings, gross takings, payments for stock, direct debits and another for something I can't remember.

My brain hurts, your's is obviously firing on all cylinders.

Thanks again Kev.

PS I have now decided to use the "indirect" file as it seems to do what's needed I think, I've added 52 sheets for the weekly bit + another 4 for different summaries.

PPS this could be a viable seller if it works well enough methinks, (I'll take a cheque )[/FONT][/SIZE]

[JBeaucaire]

JB: I agree, indirect is easier than alternatives - a little volatility is not too high a price, IMHO for the simplicity of your solutions - lol @ defensiveness though - how is it in the fort?

Yeah, I need to watch that. It's all about learning here, but I get the feeling sometimes people add on information that, although true, is just so much info to absorb. I really hate how long some of the answers get just trying to "teach it all" at once. Ugh. Short and sweet would be so nice.

But then again, I do the same thing, adding commentary to other's posts for clarity, so I have no place complaining. Perhaps this is the better way for the OP to get the info, in bits and pieces from several people rather than one massive post that is imposing to read altogether.

JB:Where possible, you could consider replacing row() - x with row(1:1) - it's one less calculation and it's slightly easier to read - because of the fewer calculations, but also because the evaluated row is shown in the formula content.

OOh...I like that a lot. I'll use that in another thread on this same topic!

[k13ehr]

Errhumm, fascinating as this back and forth is, it's a little over my head, but we seem to be getting somewhere.

I just had a thought though, what if I need to add another cell in weekly, how would I do it? or maybe take one out or just move it to a different part of the sheet, because of the way the formula is written, unless you have an in depth knowledge of Excel, (not Moi) it is impossible to see what the formula is actually doing.

Would it be possible for myself and others to break the formula below down into it's separate components (if possible) then I may be able to understand it better and the so help myself more.

=IF(ISREF(INDIRECT("Sheet"&ROW()-4 & "!I" & COLUMN()+16)), SUM(INDIRECT("Sheet" & ROW()-4 & "!I" & COLUMN()+16 & ":J" & COLUMN()+16)), "")

I know you don't need to be a mechanic to drive a car, but an awareness of what's going on under the bonnet/hood is handy to keep everything running well.

Isn't it wonderful that we have the internet, and can both help and be helped in places like forums, I'm on another forum, and am often able to help other people, it's so nice to be able to give back.

Cheers Kev[/SIZE]

[k13ehr]

OK, I have now put in all 52 sheets and copied down the formula, and it seems to work fine, and does what is needed as far as the simple sample I offered required to be done.

I now need to add a bit more, I need to put in sheets to perform the following functions

Weekly Summary Proprietor Drawings
Weekly Summary payments for Stock
Weekly Summary payments for Stock
Weekly summary of direct debits, this comes from the cheques column as we don't usually write any cheques.

I tried to drag the formula across to the new columns not expecting it to work, and I wasn't disappointed, I have tried really hard to understand the relationship of the formulas, but it just seems like gibberish to me so I'm stuck for now.

Cheers Kev.[/size]

[Cheeky Charlie]

/unsubscribe

[k13ehr]

What's that supposed to mean then?

Kev.

[JBeaucaire]

Have you ever used the Formula Auditing toolbar? Turn that on. Then click on one of these cells and select the Evaluate Formula icon, use that tool to step through the formula one calc at a time so you can see how it unfolds. Very educational.

[k13ehr]

I shall give that a coat of looking at, ta muchly.

This learning curve is more like a straight line going straight up.

Kev.