Excel 13 Folder Spreadsheet!!!!

[Feast]

Hi everyone,

I am building a spreadsheet for a friend that will contain date of sale,
First name, Last name, address, original $ amount, % of that $ amount, How
many days financed, and balance due. This spreadsheet consist of 31 sheets
representing each day of the month and a final sheet that takes the total
from the previous 31 sheets. I need to create a sheet that will take each
individual name with a balance from the previous 31 sheets and pull all the
information from that row on the specific sheet on a Balance owed sheet. I
have to do this for every month in the year. I have tried v lookup but that
defeats the purpose of going thru each individual item seeing if there is a
balance. I need something that will look it up and pull all the information
from the line item. I need this to look through 12 different workbooks. Some
Please HELP!!!!

 

[Dave F]

Based on your description you should be building a database for the type of
data management you're trying to accomplish.

XL isn't really built for the relational structure you're contemplating. XL
can be forced to have relational relationships between tables of data but it
is too easy to screw up those references.

 

[Feast]

You say it can be forced to do what I am asking. I do plan on locking the
cells where people cannot change them. Is there a way to pull information off
previous sheets without putting in a specific name. I just need the ones with
open balances to pull to a different sheet.

 

[PY & Associates]

We guess you have 12 workbooks, one for each month.
Each workbook has 32 sheets, one for each calendar day and one summary
sheet.

We would imagine to combine all 31 sheets of each workbook in a new sheet,
sort by name/date,
filter out or delete rows with 0 balance

If necessary, we would loop in all 12 workbooks

Doing this once a month is not difficult but of course, can be automated.

Regards

 

[Feast]

Yes....... PY and Associates. Now how would I go about doing this?

 

[Jim Cone]

For starters you could use a 3d formula that sums a cell from
each of the 31 sheets. It assumes each set of data is on
the same row on each daily sheet...
=SUM(Sheet1:Sheet31!H2)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"Feast" <[email protected]>
wrote in message
Hi everyone,
I am building a spreadsheet for a friend that will contain date of sale,
First name, Last name, address, original $ amount, % of that $ amount, How
many days financed, and balance due. This spreadsheet consist of 31 sheets
representing each day of the month and a final sheet that takes the total
from the previous 31 sheets. I need to create a sheet that will take each
individual name with a balance from the previous 31 sheets and pull all the
information from that row on the specific sheet on a Balance owed sheet. I
have to do this for every month in the year. I have tried v lookup but that
defeats the purpose of going thru each individual item seeing if there is a
balance. I need something that will look it up and pull all the information
from the line item. I need this to look through 12 different workbooks. Some
Please HELP!!!!--
Thanks in advance for the help.

 

[PY & Associates]

In sheet day1,
copy all except row1 (label) of each subsequent sheets (day2...day31) and
paste to bottom of sheet day1,
(in fact we consider a sheet for each day is unnecessary, but that is
your design)
sort ascending by balance,
delete all rows with 0 balance,
sort again by name/date

Help yourself with macro recorder please.

Regards