|
Gabi Mullor
09-04-2013
02:33 PM BST
|
Hi,
I'm trying to create a table that when I choose A with a scroll bar - the whole line of numbers will show in my table
same if I choose B OR C.
I'm not sure which function is suitable: offset or lookup.
Type 1 Type 2 Type 3 Type 4
A 1 2 1 1
B 2 1 1 2
C 2 2 1 1
Thank you so much for your assistance
Gabi M.
|
Kevin Bates  07-04-2013
04:19 PM BST
|
HI, I have been scratching my head for a few days having tried to create a function and I now require some assistance please. What I need appears simple in my mind but not so when it comes to assembling a function.
I want to create a function that will automatically update my loan repayment information (reducing balance, number of payments left and I need to be able to alter the repayment amount and it update the relevant info) every time the workbook is opened (against the current month from the system clock). My loans are %0 interest so there is no complicated issues there, but all have different repayment periods.
Thank you in advance for your assistance.
Kevin B
|
Kevin Singleton 03-04-2013
05:08 PM BST
|
/m2521 /m2520 This system is designed for Excel and accounting amateurs who are using the workbook as a simple accounting system in an organisation staffed by volunteers where Excel expertise cannot be relied upon. In my original message I mentioned that I did not want to use HIDE as this completely hides the column with no way for the uninitiated to un-hide it unless they know their way around Excel. They should only require basic Excel knowledge to operate the system by inputting their income and expense items into cells and the workbook does the rest. We cannot control the computer or version of Excel they will be using the workbook on nor their expertise. So, by using the GROUP function, even if for some reason VBA/Macros do not function well on their computer, they can still use the system and open and close columns by clicking the "+" or "-" button above the relevant GROUPED columns. HIDE leaves no trace and you can only tell something is hidden by looking at the column letters and noticing where one is missing. Whereas with the GROUP function you can always see and click the tell-tale "+" or "-" above the column you are looking for. Using the Group or Ungroup method is not the answer as this adds/removes grouping entirely from the relevant column rather than expanding/collapsing the that column. ActiveSheet.Outline.ShowLevels ColumnLevels:=2 is also not the answer as this expands/collapses all columns on that level. I want to target just one column of the many there may be on level 2 or 3 or whatever and achieve the same as I would by clicking the relevant button "+-") above that column. Any help you can give will be greatly appreciated. Edited 03-04-2013 05:39 PM
|
|
Raymond Bewsey
02-04-2013
04:10 PM BST
|
If this the sort of thing you were looking for?
Columns("E:E").Select
Selection.EntireColumn.Hidden = True
|
|
Kevin
31-03-2013
05:25 PM BST
|
I have created a workbook for a simple accounting system for volunteer organisations that cannot afford to pay for a full accounting package. Everything works fine but I want to add an enhancement using a Macro/VBA code.
My workbook has various sheets amongst which SETUP, INCOME, EXPENDITURE. All sheets are interlinked with the SETUP sheet containing Income/Expenditure item names and opening balances and previous year totals for a set of accounts. On adjacent sheets each income/expenditure type listed on the SETUP sheet has a corresponding column. Since some organisations have more income/expenditure headings than others, in some case there may be many redundant columns serving no purpose but occupying visual space. I have GROUPED these columns individually so that users can click the + sign at the tops of any columns to open/close them.
I am trying to make this automatic using Macro/VBA so that, if cells on the SETUP page representing the NAME and OPENING balance of an item contain data, the relevant column on the income/expenditure pages will open with the income/expenditure name in the column head. If the relevant cells for that column on the SETUP page are empty, the column on the Income/Expenditure page will be closed, so shrinking the width of the sheet.
With the code I am trying to write, when the workbook opens or individual worksheets get the focus, all relevant columns will automatically be open and those that are not needed will be automatically closed/grouped. But, in case individual users have problems on their systems, the columns can always be manually opened/closed by clicking the + buttons at the heads of the relevant columns
I have searched high and low on the web but cannot find code that does this. There are lots of mentions of HIDING column/rows or sections of a sheet (I don't want to HIDE them) but nothing that opens and closes GROUPED columns/rows.
Can you please help with this. Thanks in advance
|
|
Ray Bewsey
30-03-2013
01:24 PM BST
|
Hi - back again for your excel(lent) help. This is a Vba Q. I have a stocklist workbook for the motorhomes that we sell. The main page is the current stock list. When we sell an item I place the cursor on the row that holds the data for that vehicle and click a 'sold' button that runs my 'move_to_sold_sheet' macro (and does some other things too). One of the thing that I would also like this macro to do is to move a folder (and it's contents inc a sub folder) for me rather than me having to remember to do it by hand every time we sell a vehicle. eg I store the images for the vehicles in separate folders. eg all folders for vehicles with reg plates starting with A would be held in the F:\STOCKIMAGES\A\ folder so I might have F:\STOCKIMAGES\A\ABC 123F...\ and F:\STOCKIMAGES\A\ARC 55F...\ etc - There is also a _SOLD\ folder in here as F:\STOCKIMAGES\A\_SOLD\ -
Manually I navigate to the F:\STOCKIMAGES\A\ folder and Cut and Paste the appropriate images folder into the _SOLD\ folder.
If you have any simple 'move folder to' Vba routine ideas I would appreciate it - at the mo I am not whether I have to do MkDir etc and use file objects to copy files and folders into the _SOLD folder then delete the original folder (all seems a bit messy - and there is probably a really simple solution). Ideas please. Thnak you
|
|
Chris
25-02-2013
08:06 PM BST
|
The (MeadInKent) Excel Functions Guide has finally been updated to reflect the 2010 version of the program. Over 1,000 copies of the guide (with accompanying spreadsheets) have been sold so far.
www.MeadInKent.co.uk
|
|
foxsian@aol.com
15-01-2013
08:46 PM BST
|
My husband, Neil Fox purchasd the excel annual leave database this morning (15/1/13) and paid by pay pal. We have the receipt from PAY PAL, however we are still not in receipt of your product to download, Pleae could you send to me or to my husband at Foxcontracts@aol.com asap as I have been commissioned to do a task with a deadline attached.
Many thanks
Sian Fox
|
|
Giles Dumont
29-11-2012
11:47 AM BST
|
Well done on still maintaining this reference site for Excel, I'm teaching spreadsheets to Y10 and 11 IGCSE ICT pupils again as have not done it for 5 years, and have found myself back to this site for more advice.
|
|
satyananda padhi
09-11-2012
12:28 PM BST
|
pls mail the macro for converting a number like as, "12" into words like as "twelve'
my mail id is padhisatyananda@yahoo.com
|
Cholo4u2 20-10-2012
11:42 AM BST
|
Hello. I am using Excel 2010. I would like to have a calendar pop up when I click on a date cell. The entire column contains dates and I would like to have it display the calendar allowing me to input a date into the cell by choosing the date on the pop-up calendar. When I am not selecting a date, I don't want the calendar visible. Thank you for your help.
|
|
Bob
02-06-2012
10:21 AM BST
|
Sorry figure should be £4537.52pa or £87.26pw
|
|
Bob
02-06-2012
10:11 AM BST
|
Hi Richard. My calculation £5297.60 appears wrong. I went online and it says the sum due on earnings of £60000pa is £4532.37. The basics are: Up to £146pw is exempt. Between £146 and £817 pays 12% plus 2% on all earnings above £817. Thanks for your help. Best wishes, Bob
|
|
Bob
01-06-2012
11:01 AM BST
|
Hi Richard,thanks for this. I'm not sure where the 80.62 comes from but if I insert say pay of £60000 and use your formula and multipy the weekly figures to give annual ones, it gives me an answer of £431.98. But doing it long hand:
£60000-(145*52)=52460-(817*52)=9976*2%=199.52+(817*52*12%)=5098.08. Therefore payment due on £60000 should be: =5098.08+199.53=5297.60. Any ideas, Thanks again, Bob
|
|
Richard Burton
31-05-2012
04:42 PM BST
|
Hi Bob,
Try this.
=IF(AND(Pay>145,Pay<=817),(Pay-145)*0.12,IF(Pay>817,(80.62+((Pay-816)*0.02)),0))
Probably needs a little tidying up, but seems to do the job.
All the best.
Richard
|
|
Bob
31-05-2012
02:29 PM BST
|
Sorry Richard but I've just seen your reply and I'm not sure if I explained myself very well. What I want is a formula to give an answer when : payment due on earnings from £0 to £145 per week = 0, from £146 to £817 = 12% of earnings, and above £817 is charged at 2% (plus the sum charged on the lower amount at 12%) Hope this makes sense. Thanks,Bob
|
New Topic
My Topics
Views:
35967
(Unique:
10560
) / Subscribers:
69 | What's this?
