Assigning Accounts Using VLOOKUP?
Aug 30, 2012
I have two spreadsheets, one named Tracker, the second named Data. Data is a central database spreadsheet with account #s and Tracker pulls information from it. A team is assigned an account based on the last two #s of the account #, called term digits.
Goal: A VLOOKUP to where if a column equals the term digit range (example, any accounts ending in 00 to 09), it will pull the account # from Data to Tracker.
That part is easy, the tricky part I'm having issues with is that I need it to do the search mentioned above AND search for account #s that end in 60.
So: search for 00 to 09 and 60, pull the account # from Data to Tracker once found.
Edit: Term Digit is on column B and the account # is on column A - both in the Data spreadsheet
View 9 Replies
ADVERTISEMENT
Apr 24, 2012
I have a workbook where i am trying to find duplicate accounts. I need to get a count of these duplicate accounts and i need to find the accounts as well. One last thing the workbook covers an entire year and i would like a formula that will cover the 12 sheets to provide the results.
View 4 Replies
View Related
Dec 9, 2008
I'm trying to create an aging accounts table from transaction data. I am tracking loans. Here's the layout of the data that I have. Positive entries mean that a new loan was taken out. Negative entries mean that a loan payment was made.: ...
View 10 Replies
View Related
Oct 1, 2009
I've got a pretty large budget spreadsheet my department has used for about 5 years now. I want to add multiple totals for different accounts per sheet, however my problem is when I or someone else adds a row at the bottom(above the totals) it is not added into the calculation. I'm using simple formulas right now to total sums and also show remaing money in budget.
For example:
Cell F117 is the total amount spent "=SUM(F112:F116)"
Cell G117 is my budget total, just a number
Cell H117 is the difference "=SUM(G117-F117)"
View 5 Replies
View Related
Jan 22, 2010
I’ve thought up a workbook with four sheets named “Employees Summary”, “EMP001”, “EMP002”, “EMP003” and a number of customer account sheets. Customer account sheets are named by their account numbers.
Now for the hard part. I will have three employees. All of us will have to login to the workbook with a username and password. Usernames would be. Administrator, EMP001, EMP002, EMP003. You should be able to change your password at any time.
All sheets will be visible to the administrator. Employees will only be able to see their respective “EMP” sheet and "Employee Summary" sheet. the rest is hidden. And the really hard part Every employee will enter all their transactions for the day into their respective sheets....
View 2 Replies
View Related
Feb 27, 2013
I need formula for aging of accounts receivables.
View 7 Replies
View Related
Nov 13, 2006
I have a report that I ran that gives me all the customer we have. Unfortunately, my system gives me a separate line item for any thing that my be different within the account like the account has multiple services. I need a way through excel or vba to take these multiple rows of the same acct # and site # and combine it into one line. Each account and site could have multiple services so I would need each consecutive service to be moved to last empty cell of the first such account.
View 11 Replies
View Related
Oct 3, 2007
I have a spreadsheet that's full of Accounts Receivable data, ie customer name, invoice number, amount due, etc.
Problem is that there are hundreds of customers mixed together. I'd like to split this spreadsheet into a sheet for each customer so that I can send the individual pages out as statements.
View 9 Replies
View Related
Feb 18, 2014
I'm looking to count the number of customer accounts in a report based on the branch number. I generally need to know, how many accounts has been opened per each branch without double counting the same account number (as it happens that the report may have duplicate number.
finally I need to know the sub total showing the total per branch and subtotal of all branches together.
View 14 Replies
View Related
Dec 28, 2008
I'm trying to set up automated posting from journal entries to ledger accounts.
What it boils down to is having XL match account numbers, from a 'Post Reference' column in a journal, with the corresponding ledger accounts (carrying the same respective account numbers) on another worksheet.
Then the transaction from the journal needs to be added to the identified ledger accounts in a sort of 'below-the-last-entry' manner.
View 5 Replies
View Related
Feb 4, 2009
following calculation that someone created for me.
=LOOKUP(10^10,CHOOSE({1,2},0,AVERAGE(IF(Sheet1!$C2:$C13=$A2,IF(Sheet1!$B2:$B13-DAY(Sheet1!$B2:$B13)+1=B$1,Sheet1!$D2:$D13)))))
My problem is that I have some customer account numbers that are alpha/numeric (1B0012) and some that are just numeric (1675) . The above formula does not pick up the numeric accounts correctly.
Is there something that I need to add/remove from this formula so that it will pick up both? or is it a format issue?
I have attached an example of the spreadsheet with this problem. If you select either of the alpha numeric codes it works, but if you select the numeric code it doesn't.
View 9 Replies
View Related
Feb 19, 2010
I'm trying to find customer loyalty by rep. So I need to find all the accounts that have a loyal relationship with a rep and separate them. All the other reps that sold to those companies are removed. Then I need to find the accounts that still did business with us from year to year, but not with the same rep. Any not loyal accounts are also removed.
The ultimate goal is a pivot table that shows reps on the vertical axis, years on the horizontal axis, with the data being total number of accounts per rep per year. All of the 2008 accounts will be counted, and of the 2009 accounts, only those that were loyal - retained either by the same or a different rep - will be totaled. For this purpose there is no difference between accounts retained either by the same or a different rep, they will be added together. So a rep may have had 10 accounts they sold to in 2008, and of those 10, 5 bought again in 2009, and the rep also picked up 1 account that bought from another rep in 2008. So the rep's totals would be 10 for 2008, and 6 for 2009. I've attached an example workbook to show you what I mean. A "1" in the 2008 or 2009 column indicates the account bought something that year.
View 2 Replies
View Related
Oct 23, 2008
I run reports from Accounting systems that reflect different periods. Of course each period ends up having more or less accounts. See the attached file. This is just one little sample of big massive reports. I need to line up accounts across each Quarter. so that i can calculate differences between each quarter.
In the past few times, I have done this manually. There has got to be a way to do this quicker. I have tried Pivot table. but pivot table didn't help either (unless i was not doing it right).
View 3 Replies
View Related
Aug 1, 2013
I have this table listed below that I want to automatically populate the score cell when i enter a new Cost of Poor Quality price. I have a lookup function to decide what score should be placed =LOOKUP(A2,{0,1001,5001,10001},{4,3,2,0}). I just don't know how to make the table dynamic to account for new entries while keeping the Lookup function working. T
Cost of poor Quality
Score
$1,000
4
$50,000
0
[Code] ............
View 3 Replies
View Related
Aug 25, 2014
I have a category field, and a money spent field in a table.
Category Money spent
1 $12
2 $19
2 $122
1 $5
4 $65
5 $34
5 $12
3 $26
Category 1: $17
Category 2: $$$
and so on..
So I'm looking for a formula that returns information to one cell. A formula that looks if in 'this' column you see the category code '1' for example, then return to me the money spent for ALL the 1's. It should return $17...
The table above is small just for demo purpose, my table has 8 category codes at the moment and the number of rows can be between 15 and 50 I guess... it's a monthly break down.
I would like to see how much is spent in total for each category...
In addition if possible, can this be done for two separate tables... We are two people, and I have two table recording expenditure.
So the formula would need to, from these tables:
Person A Person B
Category Money spent Category Money spent
1 $12 2 $14
2 $192 4 $25
2 $122 4 $65
1 $5 1 $32
4 $65 3 $75
5 $34 3 $85
5 $12 2 $12
3 $26 2 $11
Category 1: $49
Category 2: $$$
and so on...
Look up in both tables, search the two category columns for category '1' for example, then return all the $'s added together... So for Category 1 now it would show $49...
View 8 Replies
View Related
Jul 17, 2012
I have a large workbook that contains hundreds of account codes and values. For example, cell A2 contains the account code "55021357122 Global Business Sales", cell A3 contains code "55021753221 Global Business Purchases" and so on through the column. Column B contains the values.
I need a formula that will sum all the values in column B if the middle 2 number are "21" (positions 4 & 5 in the number). My current range for column A is A6:A366. I've tried SumIFs but that fails on the array because it fails on the MID function.
View 6 Replies
View Related
Jul 22, 2009
I'm working on protecting a spreadsheet to where only certain people would be allowed to unprotect and edit it... I've used a standard password protection and created a userform prompting for password and unlocking the sheet, and this works fine.
On the other hand, I was interested if it's possible to write the code to where once the "unprotect" button is clicked it would seek User Account name from windows and try to match it with a list of users somewhere within the workbook. This way the user wouldn't be prompted for a password, but rather the unlocking would simply work or not depending on user account.
View 9 Replies
View Related
Jun 4, 2013
I would like to assign a value to a row so I can enter different quantities in a cell in that row, and then sum the total of multiple rows.
Example;
I want to add up c3-c10, d3-d10, e3-e10. I want to make row3=$4, row4=$5, row5=$6 etc.
So c3, d3, e3, f3 cells will have different totals ex; I type 10 in c3, 12 in d3 and 15 in e3... but they are values of $4, so
When I sum up a column c3 will actually represent $40, c4=$48, e3=$60 and so on.
Another example;
row3=$4 and row4=$5
i type 10 in c3, and type 5 in c4.
the sum of c3 and c4 = $65
How I assign a value to a row?
View 2 Replies
View Related
Oct 6, 2008
I've been doing a very simple table for my father's bass club. The club has one tournament a month and the 2 lowest weights are dropped. The table has a column of names on the left hand side, beside the name is 12 columns for one weight per month, right of that is a sum of the fisherman's catch weight in column O and to the far right is an equation that removes the 2 lowest weights =O4-(SMALL(C4:N4,1)+SMALL(C4:N4,2))
In the past I have put a 0 if someone didn't fish. He has asked me if it's possible to put DNF for Did Not Fish in lieu of a 0. This way they have that information and know how many people fished and caught nothing versus how many didn't fish. I need to know how to assign DNF a value of 0 for the whole table so that I can still drop the 2 lowest weights
View 3 Replies
View Related
Dec 1, 2013
I have a column with names (repeated names by month). There are about 3700 of them spread over 12 month. so it's about 300 names per month.
Is it possible to assign an ID (a number) to specific names?
So let's say - John Smith will have ID 1 (for example), and will always get this ID?
View 8 Replies
View Related
Dec 4, 2013
The first column shows the sample data. The second column is what I'm expecting.
How to assign a similar value to the duplicates and the unique ones should have the values in order
605-21501
605-21612
605-21501
605-26123
605-26134
605-21612
605-26195
605-21612
View 10 Replies
View Related
Sep 1, 2013
Can you assign a macro to the tab key?
View 4 Replies
View Related
Apr 4, 2014
What I'm trying to so is track weekly labor costs- my crew makes a rate for 6 days then an additional amount for a 7th day. I have attached an image of what I'm trying to do. Basically I want to add up the crew members "a" rate plus the "b" rate (if applicable) over multiple weeks in the total column.
View 1 Replies
View Related
May 27, 2008
I have created an add in for excel with all the macros that will be used for a particular workbook. I have some buttons in the workbook that I have macros assigned to them. I assigned them to the xls workbook before I saved it as an add in. Now, when one of the buttons is pressed, it opens the xls workbook.
how to assign the macros from the add in to the buttons.
View 9 Replies
View Related
Feb 15, 2013
I have a problem with a spreadsheet I am working on.
When a user clicks on column N in the sheet, I have a yes/no message box open asking if they would like to create an invoice for a customer, which opens an external spreadsheet.
The problem I have is that I can't seem to get the message box to perform seperate functions for each button (open the external spreadsheet 'for yes' OR Close the message box 'for no). I can only assign one funtion between the two buttons.
The code I have is as follows:
VB:
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("N:N")) Is Nothing Then
MsgBox "Do you want to create an invoice for this customer?", vbYesNo
If response = vbYes Then
Workbooks.Open Filename:=("H:Copy of Support Service Charges Form.xlsx")
[Code] .....
View 2 Replies
View Related
Aug 5, 2014
I have a table that needs headers assigned to it. I have copied a link to view the example spreadsheets. The first sheet has the table and the second sheet has the headers. The code is designed to go to sheet 1 see what file name is being used and assign the header to the file name. Each file has two columns of data for Location 1 and Location 2, but have the same file name so it's assigns the first header, but ignores the second header. How to fix it so that it assigns it by the file name and also if it's Location 1 data or Location 2 data.
[URL]
VB:
Code:
Sub FindHeaders()
Dim iRowH, iColH, iRowD, iColD As Integer
Dim strHeader, strData, strTitle As String
[Code].....
View 1 Replies
View Related
May 5, 2008
I have a number of cells which are connected to various formulas. EX. A1 = 7. Well I want a formula in A2 which can look at A1 and if A1 = 5-6, then A2 = 1, if A1 = 7-8, then A2 =2, if A1 = 9-10, then A2 = 3, if A1 = 11-12, then A2 = 4. And so on.
View 11 Replies
View Related
Oct 26, 2009
I have a word, for example ROCK. I asigned numbers to the word. R=1,O=2,C=3,K=4. My Question: If I type in ROC it must return 123. If I type in KR it must return 41, etc. Is there a basic formula I can use to solve this?
View 4 Replies
View Related
Nov 29, 2012
is there any way wer i can assign the name of a sheet to cell so that watever i typ in the cell automatically becomes the name of that sheet? i have more than 2000 sheets
View 4 Replies
View Related
Feb 21, 2014
I am trying to write a code for simulating a stock price using geometric brownian motion. I have the function part down as follows:
Public Function SimStock(Initial_Stock_Price As Double, _
Expected_Return As Double, _
Volitility As Double, _
End_Time_Days As Double, _
Number_of_Steps) As Double
SimStock = Initial_Stock_Price * Exp((Expected_Return - Volitility ^ 2 / 2) * (Number_of_Steps / End_Time_Days) * NRnd2() * Sqr((Number_of_Steps / End_Time_Days)))
End Function
Now what i need to do is to apply this to an array. so that when i run the sub it will simulate prices from today up to some number of days that i specify. This number of days will be equal to "End_Time_Days" which is part of my function. Furthermore the simulation has to be such that it uses the above formula for the first entry but then replaces "Initial_Stock_Price" with the result of the preceding entry in each subsequent entry.
View 1 Replies
View Related
