Help with a cashflow table
 

Hello, assuming that I have a current date table as follows ...

A B C
1 Week 1 Amount L Category A
2 Week 3 Amount M Category B
3 Week 4 Amount N Category D
4 Week 4 Amount O Category E

......ie, so that i have a list of amounts that are due to be paid in some
weeks, but not every week...and then I need to write a formula to look up the
date in this table and to automatically enter the corrsposnding amount in the
correct values for each week in a table that has every week of the year, so
some week and categories will have a zero amount , whilst those weeks with a
known value will be placed in the correct week/category cell of the table ,
ie so the NEW table would look something like this...
A B C D
Week Week 1 Week 2 Week 3 Week 4
Category A Amount L 0 0 0
Category B 0 0 Amount M 0
Category C 0 0 0 0
Category D 0 0 0 Amount N
Category E 0 0 0 Amount O


Hope this makes sense, delighted to explain more if necessary. Thank you.

Help with a cashflow table
 

You only have one entry per week per category?

If yes, this sounds like a good reason to learn about pivottables.

Here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx



spudsnruf wrote:

Hello, assuming that I have a current date table as follows ...

A B C
1 Week 1 Amount L Category A
2 Week 3 Amount M Category B
3 Week 4 Amount N Category D
4 Week 4 Amount O Category E

.....ie, so that i have a list of amounts that are due to be paid in some
weeks, but not every week...and then I need to write a formula to look up the
date in this table and to automatically enter the corrsposnding amount in the
correct values for each week in a table that has every week of the year, so
some week and categories will have a zero amount , whilst those weeks with a
known value will be placed in the correct week/category cell of the table ,
ie so the NEW table would look something like this...
A B C D
Week Week 1 Week 2 Week 3 Week 4
Category A Amount L 0 0 0
Category B 0 0 Amount M 0
Category C 0 0 0 0
Category D 0 0 0 Amount N
Category E 0 0 0 Amount O

Hope this makes sense, delighted to explain more if necessary. Thank you.

--

Dave Peterson

Help with a cashflow table
 

Actually, thats great. Just what i was lookng for. Much appreciated.

"Dave Peterson" wrote:

You only have one entry per week per category?

If yes, this sounds like a good reason to learn about pivottables.

Here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistan...lconPT101.aspx



spudsnruf wrote:

Hello, assuming that I have a current date table as follows ...

A B C
1 Week 1 Amount L Category A
2 Week 3 Amount M Category B
3 Week 4 Amount N Category D
4 Week 4 Amount O Category E

.....ie, so that i have a list of amounts that are due to be paid in some
weeks, but not every week...and then I need to write a formula to look up the
date in this table and to automatically enter the corrsposnding amount in the
correct values for each week in a table that has every week of the year, so
some week and categories will have a zero amount , whilst those weeks with a
known value will be placed in the correct week/category cell of the table ,
ie so the NEW table would look something like this...
A B C D
Week Week 1 Week 2 Week 3 Week 4
Category A Amount L 0 0 0
Category B 0 0 Amount M 0
Category C 0 0 0 0
Category D 0 0 0 Amount N
Category E 0 0 0 Amount O

Hope this makes sense, delighted to explain more if necessary. Thank you.

--

Dave Peterson