DR & CR Match: Formate To The Modern Ledger Type Where There Are No Left And Right But Rather Laid Out In 1 Page
Apr 28, 2006
I have DR and CR entries which are laid out. in the traditional T format (under the Ledger tab). I would like to have it formatted to the modern ledger type where there are no left and right but rather laid out in 1 page. The required layout is shown in the Ledger attachment
I need a formula that will bring up the Model type of a product based on its Material code. All the models I need to generate have a "root" number in them (the first 6 digits define model type). This is what I need to fill out:
I have a sheet that has multiple records of the same recurring format (see attached .xls) from which I must extract data from the same groups of cells in each record (highlighted in yellow for example purposes) and export to a columnar format in another sheet. The only variable with the format is where we get into the amount of people on the plane and thus the line may shift down one or two spaces. I would like to just pull the data for the same person's name from each record as well. What is the easiest way to go about doing this?
I have strings in cell a1. I will put a formula in a3 if string "xyz" is found inside a1, it will show value from a2, else shows nothing.
i tried simple formula cell a3 =if(a1="xyz",a2,"") here it is exact match, i need partial match, or multiple partial match in a single cell. How to do it?
I am trying to create a sheet which will automatically calculate data based on laid down criteria.The criteria are as follows:
Cab Type in cell () Indica
If the value of cell () is less than or equal to 40Km then cell (B3) will be 400.
For every extra km above 40 the cell value in cell (B2) will be extra km done multiplied by 8 in cell (D2)
If the value of cell () is greater than 40 but less than or equal to 80 then cell (B3) will be 800
For every extra km above 80 the cell value in cell () will be extra km done multiplied by 12 in cell (D2) Cab Type in cell () Indego
If the value of cell () is less than or equal to 40Km then cell (B3) will be 600.
For every extra km above 40 the cell value in cell (B2) will be extra km done multiplied by 14 in cell (D2) If the value of cell () is greater than 40 but less than or equal to 80 then cell (B2) will be 1200
For every extra km above 80 the cell value in cell (B2) will be extra km done multiplied by 16 in cell (D2)
Ther are about 5 different types of cabs in the sheet.
Cab Type Provided Total KM run Basic Amount Extra km
What I want to achieve is as under:
1. If value of cell A1 = Indica and value of cell B2 is entered as 40
Then the values of cells (C2:e2) should populate automatically as 400, 0, 400
2. If value of cell A3 = Indica and value of cell B3 is entered as 40
Then the values of cells (C3:e3) should populate automatically as 400, 40, 440
I have some tabs that are color coded. What I want to do is:
1. Depending on the tab color on Sheet A, have it compare that color to a set of cell colors on Sheet B that have descriptors next to them, 2. vlookup the descriptor on Sheet B and 3. put the descriptor into cell A2 of sheet A
I'm trying to use application.match() but I'm running into a issue where I don't know if I'll be matching a string or long data type...
Here's the snippet of code:
Code: ' UNIT DATA ' Set Unit# s1 = ActiveSheet.Pictures(Application.Caller).Name iLen = Len(s1) s1 = Mid(s1, 2, iLen - 2) ' Set Sel Ex Work Date iCheck = Application.Match("MACHINE_NUMBER", Sheets("Allocation").Columns(1), 0)
[code]....
How can I get application.match to look for strings if s1 = "ABC123" or numbers if s1 = "123456"
I've tried dim variant and s1 + 0... But, haven't come up with a solution.
I am building a timeclock, and am using a user form to allow people to sign in/out.
I have a punch in form that logs the inputed username and password (after being checked against my employee list), along with a count of their username on this sheet (as a count of how many times they have punched in) and a timestamp (column 4). These values are all pasted to the first blank row in my worksheet "PunchInTimes".
Then, I have a punch out button that logs these same values the same way, only on "PunchOutTimes". Then, the idea is to link the index number of the PunchOutTimes record and PunchInTimes record for any given user, so that when they punch out, a complete record is pasted (to the first blank row) in a separate worksheet called "Timesheets".
I have been able to paste the username and count to the "Timesheets" sheet, however as soon as I try to index/match the count and inputed username (on the punch out form), I get an error 13 type mismatch.
I'm trying to paste the corresponding punch in time to this punch out time, so ive used an index/match with two columns (username and count) found in "PunchInTimes", and try to return the corresponding 4th column value (punch in time). After pasting the "punch out only" values to the PunchOutTimes, i used a "With/End With" statement, and set wsc equal to my 'timesheets' worksheet at the beginning of my code. So the line where I get my error is below in bold:
Using Excel 2003 and am trying to do a calculation by customer, by service type. Attached is the workbook and I've tried numerous ways to do this. On the revenue calculation worksheet, I am tryng to calculate the type of service minutes from the data retrieve worksheet, multipled by the service rate on the rate table for the specifc type of minute by customer. I'd like to build it as a vlookup by customer and then by service type, but have run into difficulty.
I'm having a bit of a block with an index/match formula that I am trying to create for the attached spreadsheet. i.e. I need to populate cell J3 with the info the corresponding letter contained in the table to the far left. The numbers of reference to match are the 'zones' in H3 and J2.
working on a sheet I have one col that is formated for dates (yyyy-mm-dd) this is fine if you enter a date from the 1900's or 2000's but enter a date from the 1800's and it's a no go!
example: Enter 14 jul 1941 and it formats 1941-07-14 no problem Enter 14 jul 1841 and you get nothing just 14 jul 1841 no formating at all. And this is the same for all the date formats.
I'm using Excel 2000 and I have downloaded a report from another system. The dates come in for example in the format 10.06.2008. Using the replace functionality I can change this to 10/06/2008. However, when I do this using VBA
I have a worksheet that has two different years and I am trying to update a summary page that pulls the amount based on the year in the following formula.
I am trying to convert the following macro to a Worksheet (section change) format, but cannot figure out how to have the cells B87:M150 change based upon the text (red, green, white, amber, na) in O87:o150.
Sub ordinate() Dim r As Range For Each r In Range("o87:o150")
If r.Value = "RED" Then r.EntireRow.Interior.ColorIndex = 3
ElseIf r.Value = "AMBER" Then r.EntireRow.Interior.ColorIndex = 44
ElseIf r.Value = "WHITE" Then r.EntireRow.Interior.ColorIndex = 2
ElseIf r.Value = "GREEN" Then r.EntireRow.Interior.ColorIndex = 4
ElseIf r.Value = "NONE" Then r.EntireRow.Interior.ColorIndex = 2
ElseIf r.Value = "NA" Then r.EntireRow.Interior.ColorIndex = 15 End If Next
Range("N86:IV132").Interior.ColorIndex = 2 Range("A86:A132").Interior.ColorIndex = 2 End Sub
When I view a sheet under Page Break Preview, it shows the Page numbers in the centre of the Page. While I am aware that it would not print the page number I was wondering if there is an option to remove/hide the page numbers.
I am creating a summary page that shows product levels for each org. The summary page looks something like this.
ORG 863869880 Warehouse SubWHS1WHSWHS
Part NumberQTYQTYQTY 122796 143091 147388 118494
And the data source looks like this ORGPartSubQty 863122796WHS1420 863143091WHS115 863147388WHS185 863118494WHS160 863122796VAN033 863118494VAN021 869122796WHS180..........................
I've been trying to return the qty based on the ORG and SUB using an INDEX MATCH function but it keeps giving me a #NA error. Here's the logic behind what I'm trying to do. Return Value (QTY)of part # X if ORG = B and SUB = C (in this case WHS). Here's my Formula
This is going to sound very simply to most here I'm sure. I have a need for a simple ledger, credit, debit, balance. I will only have maybe 12 accounts and want to keep track of hand written invoices and payments.
Here's what I would like: I return to my office, click on a tab for an account, enter the date and amount of an invoice in a debit column for that account. When payment is made, enter the amount received in the credit column. Having a running total in the balance column. A monthly statement option would be nice, but not necessary.
I have searched for templates of general ledgers and get results with budgets, projects . . . just too complex for my needs now.
Attached some sample data I am working on Data is ledger printed from SAP R/3 to .pdf using pdf995 Stripped from .pdf to .xls using Able2Extract
Column A represents unit number Column B represents unit name Column C I pulled account number from Col E if number otherwise 0 Col D I concatenated Col E,F,G is text color blue, otherwise 0. I then filled Col D down to replace zero with value above non-zero.
Problem : There is no account number in C2. I thought about using text in D2 for example and creating a lookup table. The problem with that approach is that exact samy text appears for balance sheet account as ledger account. Salaries& Wages for example is both accnt 216103 (bal sht) and accnt 911000 (ledger activity).
Using 2003, I'm trying to do the following, data starting in A3, and ending in L3:-
- If A3 is blank, cells have no format - If A3 has a value, with A4 blank, fill is light blue and bottom line solid, top line dotted - If A3 has a value, and A4 does too, fill is light blue, top and bottom line dotted - If H3, which is a date field, is greater than today font is black, if less than today, font is red
I am trying to use a match function in VBA to return a value when a number of values in a row match a certain criteria (I have tried the Find approach on the Ozgrid help pages but it is not what I am after). My problem using a match function of course is that when there is no match the code returns a type mismatch error. I have tried to circumvent the error problem using an "On error resume next" but this then delivers a match for all rows. Anyone have any thoughts about how I can get around this problem?
Sub DataSum() Dim strData As String Dim i As Integer i = 0 Sheets("Sheet1").Activate Range("B4", Range("B4").End(xlDown).Offset(0, 0)).Select For Each cell In Selection If cell.Value = 0 Then i = i + 1 Else strData = Sheets("Sheet1").Range("$B$4").Offset(i, 0)...........................