Bit of Excel help

If I understand your request, you want to have data from one sheet show up in another excel file? I used to do this with excel 2003, but with a cursory attempt I couldn't get it to work on excel 2007.

Open both files, click on the destination cell you want the data to appear and type = then with the formula bar cursor still flashing click on the other file and the source cell you want the information to come from. After the holiday, I will try this again on my home computer and update if there is not another solution posted here.

Drew

I'm not sure I understand your problem completely, but it sounds like you want certain data from one sheet to appear in another sheet. If so, one way to do this would be to assign a group of cells in your source sheet to gather/filter/select the info from that sheet, then, using the approach suggested in the previous post, have your Summary sheet pick it up from these cells.

As an example, assign cell AA1 in your Monthly sheet to capture the data from your column. Then, in the Summary worksheet, select your receiving cell, for example A1 and type =Monthly!AA1 into that cell. Or, you could just use the method from the previous post.

Thanks for the above but it misses the point a bit. What I want is to allow the summary sheet to pick up the next piece of data in a row or column within the monthly sheet.

If I just link a cell in the summary sheet to one in the monthly sheet, I will have ranges of empty cells; in fact just duplicating information within the monthly sheet . If I ask a cell to contain the sum the range of cells it will not list them individually. All I want is to form a sheet which contains data as and when it appears in the master monthly sheet. Those familiar with accounting software will be familiar with being able to go to an individual account ledger and see listed all transactions relating to that account only. While, in another list, seeing all transactions relating to a particular days/weeks transactions across all accounts. That is what I seek to emulate.

Any help appreciated.

Richard.

Sorry Richard, I'm still trying to grasp what you're after. You say you want the "next piece of data". Are you after the data in the last row? I guess a clearer explanation would help. I know there are a number of Excel users in CR4 and perhaps the lack of response is due to a lack of clarity as to the problem.

I am struggling to explain here but all I can say is. If you went to a big sheet of data and wanted to find and make a list of, e.g. the days and dates when John supplied Pete and with what he supplied him you would probably end up with a piece of paper with John to Pete at the top and, then, a list giving the date, Item, cost or income etc. If John had supplied Pete 4 times then your summary would have just 4 lines of data. If I try to do this in Excel by linking a range of cells to another worksheet I would have loads of empty cells as most of the cells linked to would be empty of data. If I use =worksheet1!b6 where column 6 lists supplies to Pete I would have all supplies to Pete if I use =if(worksheet1!b1="John",ifworksheet1!b6="","",worksheet1!b6),0) -where row b is supplies by John, I would have empty cells where no supplies are from John to Pete etc and some cells filled with the relevant data. My question is how do I just list the relevant data without spaces in the summary worksheet. Just as one would if doing the same manually.

Sorry this reply is so lengthy but I just can't explain it without some example. I would however still stick with the sales, purchase ledger and single account ledger simile drawn in my second posting.

Thanks in advance,

Richard.

I think I get the drift now. What you need to use is the array formula that basically says "if John appears in column 1 and Pete appears in column 5, then take the info from column 10 and write this into my John-Peter summary sheet". There are examples on using this on the 'net, and it looks a bit daunting at first, but once you get the hang of it, it's very powerful. Just remember to use the Ctrl-Shift-Enter combo instead of Enter so as to get the curly brackets around the statement.

I have not used this method for other than summing numbers, but the concepts should be the same.

As an example, the equation would look something like this if you were looking to add up all the money Pete and John exchanged

={sum((JohnPete!A1:A100="John")*(JohnPete!E1:E100="Pete")*JohnPete!J1:J100)}

The way it works is that for each line from 1 to 100, if "John" appears in column A, the first set of brackets returns a TRUE or 1. Now, if the second set returns a "Pete", then that's also a 1, so 1 * 1 = 1 and then the value in column J is returned. If either one of the first two tests returns a False, then the answer is zero and you're multiplying the info in column J by zero and get nothing.

Graebeard,

thanks for this. I'm studying array formulas in Excel help right now. However, I don't think this answers my problem. They still seem to link a cell with the answer to one question, as it were. If the answer is a negative it reports nothing in a cell. My problem remains that I don't want empty cells to appear in the summary. I do not want the summary sheet to perform a calculation. Just report a piece of data when it appears and only if it appears; in order with no spaces.

Richard.

You could try performing the calculation either on the original sheet, or in an intermediate sheet which can then be hidden. The results can then be drawn directly to where you want them on the summary sheet. While this may appear more cumbersome, it keeps the final sheet clean and simple.

Thanks one and all but I just can't seem explain my problem through to you all. It is not a calculation thing, it's a reporting thing. I just want 5 lines of summary for five relevant lines in my monthly sheet. ten for ten etc. How do I stop having loads of empty reporting cells? I think, may be I should draw this to a close as it is doing my head in. I think I should just manually transfer data. If I keep the format in the summary sheet as the monthly sheet I can just cut and paste straight in.