Spreadsheet help please.

Dunkyboy_2

How can i get a drop down box on spreadsheets.

I have a spread sheet made out for each month of the year with outgoings for each month etc, but on some days of the month i have several direct debits going out, and i can't fit them all in the box, (without making a mess of the spreadsheet), and i have seen other spreadsheets with a drop down box's, whereby the box has a little arrow on the bottom right corner indicating that there is a drop down menu, which then has additional information to see. How can i do this, or is this in the expert league, or do i need different software. I have microsoft's standard excel spreadsheet running on xp home. Thanks techie's..

tru

Right-click the cell, select 'insert comment' then type away Is that what you meant?

I prefer to insert rows so everything is broken down into separate amounts.

Chippy_Minton

Do you mean a filter?

Click the cell containing the column heading, then on the Excel menu click Data - Filter - AutoFilter. That puts in a drop down arrow that allows you to display data in that column according to the filter you select. Can't see how that would be much use with a budget spreadsheet though.

As troo said, you should really have one row for each debit or credit amount, even if they are going out on the same day - just like your bank statement.

tru

Thanks for the filter info. I've never used it before, have no idea what I'll use it for now but I've learned something new :T

thesealeyboy

Dunkyboy wrote:

How can i get a drop down box on spreadsheets.

I have a spread sheet made out for each month of the year with outgoings for each month etc, but on some days of the month i have several direct debits going out, and i can't fit them all in the box, (without making a mess of the spreadsheet), and i have seen other spreadsheets with a drop down box's, whereby the box has a little arrow on the bottom right corner indicating that there is a drop down menu, which then has additional information to see. How can i do this, or is this in the expert league, or do i need different software. I have microsoft's standard excel spreadsheet running on xp home. Thanks techie's..

Another way, which aactually restricts what values can be entered into the cell(s) affected.

Firstly, create a list of values in a column (You can hide the column later) e.g. A, B and C. The click on the cell you want the drop down box on, and goto Data and Validation at the top of your screen. In the 'Allow' box choose 'List' and then choose your range of cells that you typed A B and C into before in the 'Source' box. Once you click on OK the only values you can enter into that cell are the ones in the list you created, plus if you click on a 'validated' cell you have the option to drop a box down and choose a value.

Give me a shout if you want any screenshots or further guidance.

J

Dunkyboy_2

Okay, thanks for your help, but i need to try and explain better.
I saw this on TV. The guy was renovating a house and he had a list of things he needed to do down the left hand side of the screen , ie

Plumbing
Building
Garden

and when they clicked on the box ie (Plumbing)
another list came on screen with all the individual plumbing jobs and costs etc.

it lets you have more stuff on one screen. This would be useful to me for my budget planner.As i like to have EVERYTHING on one screen. For Food as an example i like to know how much i spent and where. So if i clicked on food it would show what i spent in different shops, ie butchers, bakers, asda, etc.
I think i'm out of my depth here, but thanks for your help

espresso

Okay, thanks for your help, but i need to try and explain better.
I saw this on TV. The guy was renovating a house and he had a list of things he needed to do down the left hand side of the screen , ie

Plumbing
Building
Garden

and when they clicked on the box ie (Plumbing)
another list came on screen with all the individual plumbing jobs and costs etc.

Yes it WAS a comment as mentioned above by troo!

:rotfl:

scotty

First you have to create the list to be displayed in the dropdown list. This can be on the same sheet, a different worksheet or even a different workbook. Select the cell which is to have the dropdown list, from the Data menu select Validation and choose the Settings tab. You will see a drop down list for Allow, select List. Define the range where your previously created your list.

tru

Thanks Scotty I've just tried a pretend shopping list, I put tea in A1 and £1 in B1, coffee in A2 and £2 in B2 and I can't get it to work

Do I have to put the item and price in A1, then the next item and price in A2 etc?

beaker141

Dunkyboy wrote:

Okay, thanks for your help, but i need to try and explain better.
I saw this on TV. The guy was renovating a house and he had a list of things he needed to do down the left hand side of the screen , ie

Plumbing
Building
Garden

and when they clicked on the box ie (Plumbing)
another list came on screen with all the individual plumbing jobs and costs etc.

it lets you have more stuff on one screen. This would be useful to me for my budget planner.As i like to have EVERYTHING on one screen. For Food as an example i like to know how much i spent and where. So if i clicked on food it would show what i spent in different shops, ie butchers, bakers, asda, etc.
I think i'm out of my depth here, but thanks for your help

This sounds to me like subtotals are in use. They are a bit tricky to get the hang of to be fair, a colleague uses them frequently but I find them a bit tricky.

You create one big s/sheet and use a category code

e.g
Column A Column B Column C
Plumbing Radiator Valves 26.50
Plumbing Radiators 200
Plumbing PTFE Tape 0.50
Electrical Sockets 2.50

etc

you then put a few headings for the columns, so say Category for A item for B and total Cost for C.

Then select the entire lot and click on subtotals option, it is in data menu.

Then what you want to do in english is insert a row after each change in category (by the way you would have to sort the list first) and add a subtotal to column C.

You can then collapse and expand sections to drill into various categories.

If you want PM me your email address and I'll send you a brief example.

Genghis

Have you tried using Pivot Tables?
I've never really used them but they're good for sorting data.

Try, http://www.microsoft.com/BusinessSolutions/excel_pivot_tables_collins.mspx