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
View 2 RepliesI 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:
View 2 Replies View RelatedI 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?
View 9 Replies View RelatedI 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
How I would go about accomplishing this?
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.
A B C D E F
Latest Status
Latest Date
Current Status 1
Current Date 1
Current Status 2
Current Date 2
#VALUE!
Sun,16-Feb-14,11:0 PM
Take1
Sun,16-Feb- 14,10:41PM
Take2
Sun,16-Feb- 14,11:02 PM
My formula =IF(B3="","",OFFSET(A3,MATCH(B3,B3:F3,0),0,2)) in A2 is giving me #value error.
The correct solution should be Take2, while matching the condition from B2 latest date.
Sheet Attached. File Name-Latest.xls Zippyshare.com - Latest.xls
I need a formula for left coulmn lookup (using index and matching function) and lookup array has duplicate value.
View 1 Replies View Related=INDEX([ipathfall07shoes.xls]Sheet1!$B$2:$W$139,MATCH(C17,0),1)
View 9 Replies View RelatedI 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:
[Code] ......
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.
View 4 Replies View RelatedI'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.
View 2 Replies View RelatedI have a vba to do page set-up:
*Left, Top, Right, Bootom as 0.5
*Header and Footer as 0.2
Once vba is run page set-up is ok ie margins but when printed Left and right margins are not 0.5cm.
solve this mystery?
Code:
Sub Print_Format()
Dim myRange As String
myRange = Selection.Address
ActiveSheet.PageSetup.PrintArea = myRange
With ActiveSheet.PageSetup
[Code]...
I have a list that looks like this:
1. Here I have som formatted text.
2. Here too.
10. And here.
110. As well as here.
The content of each line is in a single cell.
How do I go about automatically chopping off the number, the dot and the space before the text, but retaining all formattings?
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
Range("L49:L300").Replace What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
it works on most but some of the dates get switched to 06/10/2008.
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.
View 5 Replies View Relatedhi i have a formula in a module that show a date as such
"ddd d" and this shows up as say "fri 6"
how can i get it to show up "f 6" or maybe "fr 6" and not have to use the whole three letters
:o
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
I'd like to import some dates into some textboxes on a userform.
I'm also going to let the user edit these, however... what i'd like is for the user to be forced to:
1) enter a date in the format dd/mm/yy
2) Enter a valid date (eg, not feb 30th)
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.
View 3 Replies View RelatedI 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
=INDEX(Data!$D$2:$D$43,MATCH(Summary!B$1,IF(AND(Data!$B$2:$B$43=Summary!$A4,Data!$C$2:$C$43="WHS1"), Data!$A$2:$A$43),0))
This is an array formula so I have confirmed with CTRL+SHIFT+ENTER to designate as such.
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.
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..
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).
how can transefer data from journal to ledgers accounts,
i can do journal and its formulas, ledger as well,
but i dont know how to connect them to avoid entring data 2 times.
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)...........................
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