Collect used areas of multiple spreadsheets into one...

M

mivpiv

Let me try to explain my problem.

Basically I am trying to set up a petty cash system where each person
has his own sheet - that I then want to collect into one big Master
sheet.

I have 10 sheets in a workbook (lets call them cash1, cash2, cash3
etc).
They all have the same layout and formulas...

The areas where the people input the data that I want copied is ranging

from A9-I34.

I could do a simple =and refer to each cell in the different sheets.
But there might not be information in all the rows (A9 to I34) in all
of the indvidul sheets.

To avoid having a long list with empty rows I would like to only
collect the rows used in a specified area of each sheet and then put
them continously in my Master sheet - as a total resume of ALL the
expenses in on lone list.

I cant figure out how to do this without ending up with a lot of empty
rows in my master.

Please advice me how to solve this problem... It is driving me nuts -
and I will be so happy for any help you might offer.

Thank you,
MivPiv
 
S

sswilcox

I hate to sound like Aaron, but this really might be much better suited
for a database application like Access.

That said, I was able to accomplish something similar using a series of
helper cells.

I had a series of financial-type "products" I wanted to summarize. Each
one had a varying number of variable components (i.e. premiums, stikes,
triggers, barriers, etc.) For some products I only needed two or three
lines in my summary, for others I needed four or five. The summary was
limited to displaying only three products, although the user could
choose from many more than that. Therefore, the information to be
summarized could be anything, depending on the user's selections.

(I am omitting several steps that don't pertain to your situation. I
also admit that this may not be the most efficient solution, but it's
what I was able to come up with.)

1) In your first Cash# worksheet, insert 2 columns in front of A. In
B9, write an IF statement:

=IF(C9<>"",1,) <assuming [now] C9 is the first "real" data about
the line item and not simply an item number>

Copy down. You should now have a "1" next to each valid line item and a
"0" next to blank rows.

2) In A9, write an IF statement to count the 1's:

=IF(B9<>0,SUM(B$9:B9),NA())

Copy down. You should now see a running count of each row with a "1" in
column B. If you have line items on rows 9, 10, 11, 14, 22, and 27 -
you will see a count (1, 2, 3, etc) next to each sequential valid line
item. Blank cells will show "#N/A", which you could supress if you
wanted to with an ISERROR. Probably best to hide both of the new
columns once you get everything set up.

3) On your Master worksheet in some column (assuming A1 for this
example), number your rows beginning with "1" down to an adequate
number for your needs. You can make the font color match the background
if you don't want the numbers to be visible.

4) In cell B1, all you need now is a VLOOKUP:

=VLOOKUP(A1,Cash1!$A$9:$K$34,3, false)

Copy down. This will pull information for the corresponding line item
number in column A from the Cash1 worksheet. The "3" obviously refers
to the third column in the range. As you copy right this formula you'll
need to change that number to reflect the appropriate number of columns
you wish to move right to pull the data you want.

This should get you most of what you want. Not sure how you'd do this
with multiple Cash# worksheets if you want everyone's petty cash items
to appear in one master list. I would probably set up another
sub-master worksheet with what you originally stated (A9 = Cash1!A9,
etc.) Direct links to each and every possible line item. This would
obviously look bad and be exactly what you don't want. But then do
steps 1 and 2 on this new sub-master worksheet and reference that new
worksheet when you work with the master. That would do it.

Good luck.

S
 
Top