How do I create this type of workbook?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tonyva - 25 Jan 2007 02:24 GMT

I need to create an excel spreadsheet but not sure how to achieve it. (this
is for commission payout)

On sheet 1 starting lefthand side is "name" next column would be "client"
next "PB" next "Paid Date" then "Outstanding"

The data would never be deleted and entries would just keep going down the
page, would be possible when I click on the "name" PD and Outstanding could
be linked to sheet2.

Any help on this would be great

Thanks
Tony

Reply to this Message

Roger Govier - 25 Jan 2007 08:59 GMT

Hi Tony

Why carry data to another sheet? You might be able to achieve what you
want by filtering the data.

If you highlight your header row and choose Data>Filter>Autofilter
you could use the dropdown on Name to select the required person and
click the dropdown on Paid Date and select Blank.
This would show all transactions which are outstanding.

Signature

Regards

Roger Govier

>I need to create an excel spreadsheet but not sure how to achieve it.
>(this
[quoted text clipped - 14 lines]
> Thanks
> Tony

Reply to this Message

tonyva - 25 Jan 2007 20:54 GMT

Thanks for your reply Roger, I have used the Autofilter that you suggested
and that work really well.

A question for you, I have a columns of data I enter on a daily basis, the
data is all numeric and the totals add at the bottom. The problem is the
spreadsheet keeps gettin deeper and deeper, is there a way around this?

Thanks
Tony

> Hi Tony
>
[quoted text clipped - 24 lines]
> > Thanks
> > Tony

Reply to this Message

Roger Govier - 25 Jan 2007 21:37 GMT

Hi Tony

Insert extra rows at the top of the sheet and put your Totals there.

=SUBTOTAL(9,A3:A1000)
will add all of the visible cells between A3 and A1000 when a filter is
applied.

If you place your cursor in cell A3, then Windows>Freeze Panes, rows 1
and 2 will always be visible with row 1 holding your total and row2 your
header.

Look at help on Subtotals to see what the other parameters other than ,9
can do for you.

Signature

Regards

Roger Govier

> Thanks for your reply Roger, I have used the Autofilter that you
> suggested
[quoted text clipped - 42 lines]
>> > Thanks
>> > Tony

Reply to this Message

tonyva - 25 Jan 2007 23:11 GMT

Roger, after I add the rows do I leave cursor in A3 and insert
=SUBTOTAL(9,A3:A1000) in "insert function" ?

Thanks
Tony

> Hi Tony
>
[quoted text clipped - 57 lines]
> >> > Thanks
> >> > Tony

Reply to this Message

Roger Govier - 26 Jan 2007 08:30 GMT

Hi Tony

No, the formula needs to be entered in A1
A2 would be your Header, say Amount
A3 would be the first amount value - say 100

Signature

Regards

Roger Govier

> Roger, after I add the rows do I leave cursor in A3 and insert
> =SUBTOTAL(9,A3:A1000) in "insert function" ?
[quoted text clipped - 71 lines]
>> >> > Thanks
>> >> > Tony

Reply to this Message

Rate this thread: