Excel 2010 :: Create Balance Ledger To Track Transactions At Different Locations
Dec 16, 2011
So I'm trying to create a balance ledger to track my transactions at different locations.
This is basically what I have:
C4 = numerical value for site A
D4 = balance for site A
E4 = numerical value for site B
F4 = balance for site B
G4 = total balance of both sites
Values for C4 and E4 are manually entered.
D4: =IF(OR(ISBLANK(D3), ISBLANK(C4)), "", D3+C4)
F4: =IF(OR(ISBLANK(F3), ISBLANK(E4)), "", F3+E4)
G4: =IF(AND(ISBLANK(D4), ISBLANK(F4)), "", D4+F4)
I have these formulas auto-filled to the bottom of the sheet of each column. The problem I'm having is that with this setup, the return on the G column is giving me
#VALUE!
for all rows that do not have any values entered yet. Is there any way to fix the formula in column G so that it reads the value of the cell instead of the formula in the targeted cell?
I am using Office 2010 on Windows 7.
View 2 Replies
ADVERTISEMENT
Nov 12, 2009
Ref attached sheet
I m facing a big and urgent problem in my worksheet..i have done my all work here but now i knew that there is a difference in this vb code..
I need your urgent favour to rectify it for me..
What i need is sheet1 is my journal jounal (data) and sheet2 is my reporting place where i set a macro button at top to update the report..
But in sheet2 column I i need an auto Balance of debit & credit..
View 11 Replies
View Related
Jun 4, 2013
I need to display a set of cells based on the value of two drop down cells i have. As I am not very good at english and worse at explinations, I'll try via screen shots...
I have two dropdowns (C4 and C6) that will indicate what table to use (Second sheet / screenshot). I want that "table" to display in the yellow box on the first page. To complicate matters, some options do not have a CLA option - those starting with X. As there are 24 different outcomes and each is 3x9 if/then statements just dont seem to cut it.
P.S. I have excel 2010 and windows 7
View 1 Replies
View Related
Mar 16, 2014
I want to create leger accounts In cashbook there is account head (ex. Hand Loan A/c, Salary A/c, Exam A/c and so on)
View 2 Replies
View Related
Mar 13, 2014
I have CASHBOOK Worksheet and I want to create a LEDGER Account in Multiple sheet
View 2 Replies
View Related
Jan 29, 2007
I am trying to enhance our estimating spreadsheet. If you look at the example attached it is a snippet of the spreadsheet. What I am trying to do is make column AJ (balanced bid unit) include subordinate items. The way a sub item will be defined is column B (bid item) if column B = S, then its balanced bid unit need to be reflected in the column AJ. I would also any item titled as Y in column B (bid item) to include all listings as S (until next item as Y) in the column AJ totals.
View 9 Replies
View Related
Oct 26, 2006
For the small database in my example workbook, I would like to apply credits earned at a later date to the oldest charges and create a "To Date" balance. After creating the "To Date" balance, I would like to select the smallest "To Date" balance as a way to identify the first charge that has no payment. Please see the attached for more clarity and additional information. The last column shows the desired results.
View 9 Replies
View Related
Jan 24, 2014
The situation I have on my hands is over 200 users, were the majority can't use Excel's built in function to create PDF's.
Yes, I've tried to teach them, but I'm probably not a good teacher.
So, I've resorted to create a button at the top of the worksheet, that says "Create PDF". I thought this was gonna be very easy to program, but alas.
Is there a way to start the built-in Excel dialog that happens when you click "Create PDF/XPS" in "Save & Send"?
View 4 Replies
View Related
Dec 26, 2012
how do i make a bar chart on Excel! 2010
Basically i have these figures
Male : 21
Female : 18
I want to make a bar chart, When i use Excel and try to make it - i get a chart which shows the right column height but the Male and Female bars are BOTH IN 1 Colour. How do i make it so that male bar is blue and female is pink.
View 3 Replies
View Related
Jan 6, 2014
I am not too sure what this is called but I am using Excel 2010 and need particular document that I use repeatedly. Basically, if a particular cell in the document does not contain a phrase then I want Excel to automatically change the text in that cell to bold and red and 2 other cells in Excel to bold and red as well. For more information, the cell is 19–20 and K–O and the phrase in the cell is "No Inconsistency".
So if "No Inconsistency" is not found in that cell then it automatically changes the text in that cell to bold and red, and it also changes two other cells that are found in that document to bold and red as well. The coordinates of those two other cells are 19 with H–J, and the other one is 17 with H.
View 2 Replies
View Related
Mar 19, 2013
Im looking to create a bar chart in Excel 2010 where the totalnumber of widgets is 33 and the number produced so far is 11. The bar chart would be a single bar that represents the percentage complete (33%) with the chart maximum range being 100% which would represent the number 38.
Ive included a mock up example of what the data and chart would look like
View 3 Replies
View Related
Feb 7, 2013
I'm trying to create a userform with a progress bar. The progress bar does NOT need to be 'real' and accurate, but simply act as a timer to illustrate to the user that the program is running some quick calcs.. Again, basic progress bar, with about about a 4 second completion time..
(using excel 2010 VB)
View 6 Replies
View Related
Nov 18, 2013
Code:
Date Jan-14..........Jun-14............Dec-14...........Jan-15..........Jun-15............Dec-15
Measure1
Measure2
Measure3
Code:
Date 2014 2015 2016
Measure1
Measure2
Measure3
I have two tables, examples above In the first table, Jan-14 is a dropdown value that updates all the values to the right by a month increment when a specific date is selected
I then have some code that populates the cells which have 2014,2015,2016 in them.
What I need to do next is populate the 2nd table with values based on the date ranges in the above table.
View 4 Replies
View Related
Nov 26, 2013
I'm trying to create a pivot in vba. I can record what I have done but if I run that recorded code, it returns an error "Invalid call procedure or argument".
Code:
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, _
SourceData:=Sheets("Raw Data").Range("A1:AG" & Z), _
TableDestination:=Range("A3"), _
[Code].....
View 1 Replies
View Related
Jan 7, 2014
I have a workbook which needs a new sheet for each day of the month.I normally just create and re-name each sheet with the date...is there a simpler way where i can create the new sheet with a date for each month instead of doing indiviually?
Excel 2010
View 9 Replies
View Related
Feb 12, 2014
I have 6 columns. The 5th and 6th columns are the most important. The 5th contains a figure and the 6th contains a type.
Bascially I am trying to do a basic chart showing the following 2 things. A. Do a percentage chart of column 6 (service) but not of percentage of hoem many lines the product is in. Not based on percentage of how many times Apples are shown compared to other products. I need it to add up everything, then do a percentage of total amounts of oranges and total of for Apples from the figures on column 5. Also, if possible, the pie chart will even show teh actual total figure per product in each (or next to) each pie chart slice.
know the easiest way to do this (step by step) in Excel 2010 and then I can perhaps write a macro for the future?
View 1 Replies
View Related
Apr 24, 2014
I need to create 3 or 4 detailed reports from an excel 2010 worksheet.
My worksheet contains data including date, invoice number, company , consultant, days, dollars. There are approximately 100 records in the worksheet.
Requirement 1 :I want to use excel 2010 to automatically generate a series of reports broken down by consultant showing date, invoice number, company, and consultant with totals for days and dollars, when all the records for consultant 1 are listed, then I want to show consultant 2.
Same for any other consultants with a grand total at the end.
My understanding is that excel 2010 makes this process very simple .
View 3 Replies
View Related
Mar 8, 2014
I am trying to create a database that contains data taken from several work books that i have saved in one folder on my hard drive. These workbooks contain quite a lot ** data, but i only want to extract the data i want to instead ** having to extract it all? these works books are used by several other people as well, so i want it to be able to update as data changes.
I was thinking about using data links, but that only seems to extract all the data instead ** just what i want?
View 1 Replies
View Related
Mar 24, 2014
As you can see in the attached file, I have a column of classes , a columns of related revenues and the total for each class. I have to create a column, next to these, where I can create the percentage with respect the sum of each class (as you can see by the formula). My problem is I am not able to say vba to find the totals and to create the percentage just with the revenues related to that total.
p.s: I have to work on excel2010.
View 3 Replies
View Related
Mar 29, 2014
I need to create a separate PDF file for each Excel Worksheet, each worksheet has a different name.
I am using Excel 2010
View 1 Replies
View Related
Jan 21, 2012
I get a "Object variable not set" error when creating a Pivot Table from a cache. I want the destination to be in a different sheet in the workbook. I set the new worksheet as: Set WSD = Worksheets.Add and I reference WSD.Cells(2,FinalCol +2) in the destination field of the CreatePivotTable method. I use the code from the Excel 2010 VBA book I got last week.
View 9 Replies
View Related
Sep 21, 2012
Creating a pop up message when a cell value is over 100%.
I have in cell T21 "=sum(t7:T21) but I'd like to give a pop up message when this cell value is over 100%.
Excel 2010
windows XP
View 2 Replies
View Related
Apr 5, 2014
I am working on a schedule worksheet, the days are the columns and the staff member names go down the rows. I want to create a new cell value based on a condition on each row. I have been successful working with one row but how to efficiently do the same for each row.? I'm working with Excel 2010.
View 9 Replies
View Related
Dec 20, 2013
Time. What formula do I use?
6:30AfterBreakfast
7:30AfterBreakfast
8:00AfterBreakfast
8:30AfterBreakfast
9:00AfterBreakfast
9:30AfterBreakfast
10:00Before Lunch
11:00Before Lunch
12:00Before Lunch
13:00Before Lunch
14:00Before Lunch
14:30After Lunch
14:45After Lunch
15:00After Lunch
16:00After Lunch
17:00After Lunch
18:00After Lunch
18:30After Dinner
18:45After Dinner
19:00After Dinner
19:30After Dinner
View 2 Replies
View Related
Sep 30, 2007
I want to create an event log by which I am able to track changes by the users. we work on LAN and any body can write on the workbook. my work is to check for discripencies. If I get any type of help by which I can track the user name, date, cell that has been changed and the PC address. I got this code from ozgrid. in place of "c: racker.text", I used the path where to place the event and I was successful.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Locked Then
TrackFile = "C:Tracker.txt"
TargUser = Application.UserName
TargAddr = Sh.Name & "!" & Target.Address(False, False)
TargVal = Target.Resize(1, 1).Text
TargDate = Format(Now, "yyyy/mm/dd hh:mm")
x = TargDate & vbTab & TargUser & vbTab & TargAddr & vbTab & TargVal
Open TrackFile For Append As #1
Print #1, x
Close #1
End If
End Sub
I want to get the PC address from where the changes were made.
View 2 Replies
View Related
Jan 20, 2012
Using Excel 2010, I am trying to create a macro to insert INDEX and MATCH formulas on a sheet that reference Tables on the same sheet.
The formula works fine when entered in a cell where I can point to the Table. Now I need to put the formula in a macro where it will automatically reference the first Table on the sheet.
Here is an example of the formula:
Code:
=INDEX(Table1891034445678912822253536[#All],
MATCH($I35,Table1891034445678912822253536[L/I],0)+1,
MATCH(L$34,Table1891034445678912822253536[#Headers],0))
Just look at that Table name!!
The original file has multiple sheets and multiple Tables on each sheet. No naming convension was used in naming the sheets or Tables.
Can I cycle through all the Tables on a sheet and rename each of them? For example, find the first Table on the sheet closest to cell A1 and rename it Table 1, find the next Table and rename it Table 2, etc.
Is there a way to reference Tables based on an index (similar to sheets)? Is there a Table(1) and Table(2) referencing system? Is it on a per sheet basis or across the entire workbook? That is, can there be more than one Table(1) in a file?
Can I start in cell A1 and find the first instance of a Table and return the name or index of that Table?
View 2 Replies
View Related
Feb 2, 2013
There was an option in excel 2007 in the pivot wizard to use an existing pivot table report as the source for a new pivot. I cannot find this in Excel 2010.
View 2 Replies
View Related
Jan 21, 2014
Excel 2010. There is a limit to the usability of the
=INDEX($A$1:$A$1000,MATCH(0,COUNTIF($A$1:$A$1000,"<"&$A$1:$A$1000),0))
method. It seems like the limitation is in the Countif function going over 1000 (or some other size limit)
I have a list of ~1500 rows, of that there are approximately 55 unique items. Doing the unique array works correctly up until item ~40, upon which it fails by returning the 1st item only (for the rest).
Trying to debug, and pulling out the Match section, it functions up until 976 (that is
MATCH(0,COUNTIF($N$1:$N40,$C$3:$C$1500),0)
returns 976) anything after returns just 1 (1st item).
It seems to be a limitation on the text string size that COUNTIF can handle.
View 7 Replies
View Related
Jul 28, 2013
I have a Excel 2010 file that I want to activate the file and create a folder in it named customUI.
the steps to achieve this?
View 7 Replies
View Related
Aug 22, 2009
I am trying to make a tally sheet to keep track of my inventory of inserts. I am trying to make it as user friendly as possible as my operators do not have much experience working with computers. I will attach what I have made so far. The only math functions so far are: C4=B15-C15.
Right now the operator has to look at C4 to see current total, type that number into B15, then in C15 type the number of inserts thrown out to show a new current inventory total in C4. Is there a way I can set this up so that all an operator has to do is type in only the number of inserts thrown out in C15 to give current total in C4. Is there a way to make B15 know what is in C4 without the operator having to type it in. If so is there also a way to make C15 the only cell that can be edited.
View 3 Replies
View Related
