I have a workbook with several named lists (9). I also have 1 named list that is actually a listing of the names of the other lists.
What I would like to do is set up a spreadsheet where when the user selects the name of the desired list from a drop down menu in cell C1, the matching list will be available in a drop down list in D1.
I have stared at this for awhile now; I have changed my mind on what would be the best way to set up this workbook about 7 times now, and I think I am just making it more complicated than it needs to be, but if this would work then I can simplify things a lot for the user.
To clarify (or further confuse) In C1 the user chooses the name ADMIN, then the list (named ADMIN) will be in the drop down menu in cell D1; but if the user chooses the name TRAVEL, then the list (named TRAVEL) will be in the drop down menu in D1.
Any help is always appreciated :thanx:
Month, 99, 9999.
I think what you are wanting to do is this:
Select the cell that contains the Today() formula. Then hit CTRL + 1 to bring up the FORMAT CELL dialogue box. In here under the NUMBER tab choose CUSTOM. On the right of that box you will see "Type,"
In the box below type in MMMM, DD, YYYY . This will make your date appear (for example) July, 19, 2004.
Hope that helps
I worked out the same formula just after I posted! I still have no idea on the other problem though. (wish there was someway to incorporate a "find next" lol)
Thanks for your help,
I appreciate it!
I use a similar system for tracking. I put the formula to display todays date in A1 of my sheet =TODAY()
Then in column A I formatted the cells to display numbers with no decimal point, and then used the formula =A1-D3 (or whatever cell contains the date); then just used conditional formatting to change the result to red if it is over 14 days.
I am sure there is a better way, but this one will work till someone more knowledgeable responds.
Hope that helps
I have a list of people and their rates of pay.The names are listed in Column A, The Rate of Pay is in Column D. The rate of pay is the result of a formula and changes often, based on criteria that changes weekly.
At the bottom of the list, I have formulas that display the: Highest Rate of Pay, Second Highest Rate, Average Rate of Pay, Lowest Rate of Pay,and Second Lowest Rate of Pay.
How can I make the Lowest Rate of Pay (D53) ignore the cells that are blank? I used the formula: =SMALL(D2:D46,1) but if any of the cells are empty it just wants to show me 0 instead of displaying the lowest actual dollar amount. (The ISBLANK function and I seldom agree, I am confident I use it wrong and it is decidedly unforgiving)
Also, what would be the easiest way to make Excel show the Name from Column A that matches the result of the Highest and Lowest rates? I have experimented around with "Match" and "Lookup" etc, and the problem I am facing is if 2 people have the same rate of pay I need it to display both names.(Which may not even be possible.)
:thanx: for helping
I have been trying to work out your problem (that is how I learn things,by trying to figure them out.) I have attached a file that I thought worked, but now I am confused.
Do you want the number to order to be preset by month and contigent upon the inventory needed for the next month, or do you want the order amount to be determined by the amount needed for the following month?
Sorry to intrude here, I am sure one of the super guys on here will come up with a great solution; I just love a good puzzle.
"Ex/ I Need To Repeat Something Every 12th Line In Each Column"
I don't know of a formula, but if you are doing this individually it could get tiresome fast. If you know how many times you are will need the information copied you could highlight the first row, then hold your control button and highlight every 12th row for as far as you need the data repeated, then just paste into all the rows at once.
:biggrin: absolutely right! Thanks so much! I kept looking at that formula, but it just didn't register!
You're the best, thanks again :biggrin:
It works for you? If this type looks shakey it is from me banging my head on the keyboard!:( I have done everything you suggested. (I even deleted the space between the " ", thanks ) I even typed the same data into a new worksheet, cut and pasted nothing, and now..sigh..not only does this not work (for me) if the net change is negative, but for the next month Excel is starting from zero. What I am probably explaining poorly is this: on the sheet I have now G13 should show (8664.27)(but it is blank). row 14 is the only entry for April, a 900.00 debit so G14 should show
(7744.27) but it shows 900.00. ?? (shoot me, shoot me now). I am going to look at it again for a bit then just go cry myself to sleep.
Thanks for helping me with this. sure wish I could figure out why it works for you and not me, but I really really do appreciate your help.
well. I can't find anything and I think I tried everything you suggested.
I went to yahoo geocities and slammed a jpeg of my worksheet (2 actually, one with formulas and one with results). If you don't mind looking at that (and it works) perhaps you could find something? (sorry I AM taking the OZgrid training *LOVE IT* but so far I only know enough to be dangerous )
Thanks! I really appreciate the help! The only problem I seem to have is that if the net change leaves is a negative amount due to a credit received, then it will not display the total. (For this sheet the columns are actually backwards. The Debit is added in and the Credit is subtracted out.)
I am still trying to figure out why it won't display the negative amounts. Any ideas would be great!
Thanks again for all the help:yes:
That looks great! I am having some problems though getting the formula for the "Month Change" column. Could you write it out please?
Sorry, 14 hours of making spreadsheets, by the time I got to this one I think I may have gone brain flatline
Thanks for your help!!:yes:
oh, sorry, to add to my question; I need it to be continuous, so it will give a month total for every month.
I am creating workbooks that chart different account code activities for the year. Sometimes there are only a couple dates of activity and sometimes there are many. I will not be using these workbooks, so I am trying to make them as "automatic" as possible. I want to make a formula that, based on the dates entered, gives a month ending balance.
Beg balance Date Debit Credit End
37,000 3/1/04 500.0 0 37,500
I need a formula that, if the date changes to a new month (say April), it will subtract the end balance for the last day of March from the Beginning Balance. Is that possible?
First let me say I downloaded your training courses and LOVE THEM. Thanks!
I have a simple question though, is there a way to make Excel automatically display the name of the worksheet in a cell?
It would be used in a large workbook, each worksheet is a week (week1!, week2!, etc.) I would love to have a formula that I can paste on all the worksheets that displays each sheets own name. Is that possible?
You just saved me a lot of time, you are my hero!
Sorry, I didn't explain that very well.
The information on sheet1 is listed in row2
spanning across the sheet from B2 to AF2.
Where I am placing the data on sheet2 is in Column D Running from D2 to D32.
When I try to fill, Excell will take the data in D2 and fill it in updating the formula like this:
But I don't want the info in D3
I need Excel to take the information across the sheet so that it fills this way:
Staying in cell 2 but moving by columns instead of down the row.
I can do that if the data on Sheet 2 is displayed in the same way, but it isn't it is all in one column.
uhm. Does that make sense?:wow:
That did work! I don't know what my problems are with ISBLANK, it is something small that I am doing! I copied your formula and replace the sheetname and cell ref with mine and it worked fine! THANKS!:)
uhm, now...if I may ask, the information I am pulling for this particular column is in a row on the first worksheet. Is there anyway to use the FILL function to copy my formula down the column -- Excel fills by taking the information in the rows down from the original cell, and I need it to go across. So instead of Filling it to be
'AmexWS!C2 , 'AmexWSC3 it would fill like: 'AmexWS!C2, 'AmexWS!D2, etc.
I thought there was a way to transpose the numbers but can't make it work.
I am sure a lot of you are rolling your eyes at this, and I am sorry, I just don't have time to figure it out!
Thanks again! Love you guys!!
I have this workbook that I add information to all month. The last sheet pulls information from the previous sheets which I print out each month.
I have this (stupid) problem that I just can't fix.
Some rows contain text entries, and in these rows there are often blank cells. When my report sheet pulls the data, if the cell is blank it displays a "0". I really want that to just be a blank cell.
I have tried to use "ISBLANK" in my formula, and tried Conditional Formatting with ISBLANK, but I have issues with ISBLANK. Everytime I try to use it, Excel yells REF! at me, and my brain....well...ISBLANK!:wink2:
It's usually the easy stuff that evades me......I use this book at 2 workstations, both with WinXP, one with Office 2000 and the other Office XP.
Thanks for taking the time to help me!
That's exactly what the numbers were! I am not sure what I did to the first cell with a formula, but when I carried it down it works in every other row. After a brief debate on my need to understand it and unhealthy obsessions, I just cleared that first cell and the sheet is perfect.
Thanks for all your help ;;)