i have a an invoice template setup on my computer in excel.
what i would like to know is how do i get my customer address to automatically fill in (cell b) if i select them by customer number (drop down menu in cell a) in an different cell (cell c)
I have inserted the sheet. As you can see in Sheet 1 I have the Store, Address, Town etc, I would like a drop down box in D13 ( Store Name ) so when I select a store it will automatically fill all the info into D15:D19 Address, Town etc. The info will be pulled from Sheet 2.
View 3 Replies View RelatedI have the following code pasted into the worksheet module which used to work fine but no longer does. I didn't touch the code, it just stopped working.
Private Sub WorkSheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$A$16"
Select Case Target
Case "Custom Color 1": Call CustomColorInput1
Case "Custom Color 2": Call CustomColorInput1
Case "Custom Color 3": Call CustomColorInput1
Case "Custom Color 4": Call CustomColorInput1
Case Else:
End Select
Case "$A$17"
Select Case Target
Case "Custom Color 1": Call CustomColorInput2
Case "Custom Color 2": Call CustomColorInput2
Case "Custom Color 3": Call CustomColorInput2
Case "Custom Color 4": Call CustomColorInput2
Case Else:
End Select
Case "$A$18"
CustomColorInput 1-6 are the same except they input data into different cells F16-F21.
When I attach any of the CustomColorInput macros to a button it works fine. When I try to call it from the worksheet module, the data input box pops up but the data doesn't get entered into the cell. When I try to step through the CustomColorInput macros using F8, the input box pops up, I enter data and press ok. Then I continue with F8 and the debugger jumps to a custom function that I entered (below), which is not called for and is in no way related to the code.
Function PullAfterLast(rCell As Range, strLast As String)
PullAfterLast = Mid(rCell, InStrRev(rCell, strLast) + 1, 256)
End Function
When I close VBA, the data that I typed into the data input box is in the correct cell.
So basically, I'm getting three different responses from the offending code depending on how I call it up. Can anyone tell me what's going on?
More generally, in this and previous occasions, I've had problems with macros that cease to work for no reason that is apparent to me. What are some things that would cause a macro to stop working, without actually changing the code (other than the obvious, like referring to names or worksheets etc. that don't exist anymore)?
I have a spreadsheet which I am trying to use to capture customer data. What I want to happen is whenever I enter a new record, the date appears automatically in a Date column ....
View 6 Replies View RelatedI am trying to get the Price to automatically fill cell C11 of the attached spreadsheet when a user enters Grade and Thickness information. I have tried index function, match function, vlookup function, and combinations of all with no luck.
View 5 Replies View RelatedI ran a Home Service Massage, with ten massagers and it's continually growing. I know I'm supposed to use a database for this, but I'm not very good in creating one. I am however confident in using MS Excel, so in a much as possible, I only want to use MS Excel, and strictly use formulas (if I can get away with it).
Anyway, I keep customer records in one tab, and all their personal info is listed there. There are two types of columns, which I'd like to call 'static' and 'automatic'.
Static columns includes Unique ID (number), Name, Address, Phone Number, etc.
Automatic columns, by the term I used, is filled automatically. This includes the total number of hours they have availed the massage from us, the names of massagers who serviced them, --- and my biggest issue --- the date of last massage. I can manually input this, but I normally forget, so I want this filled automatically.
Aside from customer tab, I have one tab for each massager. First column is the date, then 48 columns for time. Starting from 8AM, 830AM, 9AM... 730AM. Hence I have 48 of them. Each column is equals to 30mins of massage availed. I copy and paste the Unique ID from customer tab.
so A2:A32 are dates, and B2:BC50 is where I copy paste the unique ID. B1:BC1 are time labes.
Is it possible for me, just by using formulas, to populate the 'last massage date' column?
I am writing a program in excel spreadsheet and I was wondering how to fill an input cell with color automatically in excel 2007.
I mean that, say I am writing an addition program, I am giving an input numbers in B1 and B2 and i am writing formula in B3 (=B1+B2). I want excel to fill B1 and B2 with yellow/any color automatlically. Only the cells with number input should be highlighted.
How do you make the auto-fill to not automatically use the next numbered cell:
For example:
I want it to fill with every other cell from a different sheet (same column "D") so i would have a cell ='sheet1'$D10 or whatever then how would i do it so it will auto fill every other cell from then on so the first cell below the initial one would copy from the other sheet as D12 instead of D11?
Suppose in cell A1 I type in the number "100" and in A2 I type in the number "5"
What I want to do: Have the number "100" repeat 5 times in a vertical list (so 100 appears in B1, B2, B3, B4, B5)
I am using this code and it works fine:
Dim MyData As Range
Dim MyResult As Range
Set MyData = Range("E1:E1000000")
Set MyData2 = Range("F1:F1000000")
Set MyResult = Range("J4")
MyResult.Select
Selection.Formula = "=COUNTIFS(" & MyData.Address & ",""=Kim""," & MyData2.Address & ",""=done"" )"
Is there any way I can change the code so that it automatically finds the last cell as Im using Range("E1:E1000000") and Range("F1:F1000000") because there will not be more than 1000000 entries.
I have certain values in cell B4 to B2000. There are 25 unique values in those cells. In cells A4 to A2000 I would like to put a identifier (lets say a letter from A to Y) based on the adjacent B4 to B2000 cell values. I can't use a IF function because I am way over 7 allowed in excel. I could use a vlookup but that seems to slow down the file quite a bit. Is there something else better/faster out there?
View 3 Replies View Relatedi have just finished filling out a 2000 line spreadsheet, on this spread sheet i had a customer number i then had to look up the customer number on our old database to retrieve the address for the customer. Is there any way that for future spreadsheet's i can input the customer number and it will auto fill the address of that customer ?
the columns on my sheet are:
customer A/C number
Customer Name
Customer Address line 1
Customer Address line 2
Customer Address line 3
Postcode
it would save me hours and hours of time digging through the database again.
Assume the following list of addresses are all in separate cells of a single column (A1-A4). I just need the formula to extract the street addresses, and then a separate formula to extract the zip codes.
5430-44 PASCHALL AVENUE PHILADELPHIA, PA 19143 OPA/BRT#: 884350845
4010 MARPLE STREET PHILADELPHIA, PA 19136 OPA/BRT#: 651087200
2618 SOUTH HOWARD STREET PHILADELPHIA, PA 19148 OPA/BRT#: 391251216
5737 WOODCREST AVENUE PHILADELPHIA, PA 19131 OPA/BRT#: 522155600
I want find the data in some rows that same with one or more cell and automatically fill the data. And for more details, I have attached the examp file (Examp.xls).Antoni
View 3 Replies View Relateddoes excel have a function or some kind of method where you can have it display the range of cells that you have selected? For example, if I highlight cells A5:G7 what would be the code for excel to display "A5:G7" in a message box? I need this to be dynamic, so the next time I select cells B3:T32, it needs to display "B3:T32."
View 5 Replies View RelatedI am trying to have some automation to my WS and what I want to do, is if a certain column (column 104) is changed, it will fill out that column with the data entered as long as the account is the same (column 5). Here is the code I tried, but realized very quickly, it puts me into a loop. This is being called by a change worksheet fuction
If Target.Column = 104 Then
If IsEmpty(Target) Then
Else
testacct = Cells(Target.Row, 5)
temptest = testacct
s_TempSwitch = Target.Value
Testoffset = 0
Do While temptest = testacct
temptest = Cells(Target.Row + Testoffset, 5)
Cells(Target.Row + Testoffset, 104).Value = s_TempSwitch
Testoffset = Testoffset + 1
Loop
End If
End If
I have a list of Spid numbers and a service category identifying if it is a Water Spid or a Waste spid.
The Spid is split into 2 parts. The Core is the identifier and the Category states if it is a Water or a Waste spid.
If the 1st digit in the Category is a 1 = Water.
If the 1st digit in the Category is a 2 = Waste.
There may only be a water spid or a property may have a water and waste spid.
What I want to do is have the information on 1 row rather than 2 rows
I have data in this format
SPID
Core
Category
Service Category
100000000151
100000000
151
1
[code].....
and I need to convert it into this format.
Core spid
Water
Waste
100000020
100000020101
100000020251
I have tried IF(lookup) and it works for the 1st row but I can't get it to look at the 2nd row.My data consists of 400,000 plus rows
to illustrate I attached a spreadsheet.The spreadsheet contains 5 colunms.
1. Names
2-4. Numbers
5. Sum of numbers (the formula for the sum is only entered in the first row)
What I want to do is automatically fill in sums from the second to the last row. I was trying it with the "record macro" function but failed because I don't know how to jump to the last row. CRTL-Shift-down is not useful in this case.
I have an Excel file which extracts data from other excel files (the other excel files are named 1.xls, 2.xls, 3.xls, etc.). So the formula in the cell is for example: ='C:[1.xls]Sheet1'!A1
Is there a way to let Excel fill in the next excel file address for the next cell? So that i would get this:
='C:[1.xls]Sheet1'!A1
='C:[2.xls]Sheet1'!A1
='C:[3.xls]Sheet1'!A1
='C:[4.xls]Sheet1'!A1
etc..
Because i have about 500 excel files, and otherwise i have to change the file number manually...
In the worksheet just say:Cell A1: Jan-11-2009 as today date. Cell A2: What is a date function I need to put in there so when a computer clock change to 12:01am it automatically fill in a next day it should be Jan-12-2009 and continues do so for the next day at A3 ....when computer clock change again. Remember I wanted a cells A2...A3...A2000 are blank until the computer clock change to 12:01am.
View 5 Replies View RelatedI am creating a register of first aid kits on my site. What I want to do is have a summary as the first worksheet, and then have contents, and individual expiry dates for each kit on seperate kits. Is there a way to autofill, or auto complete, a different sheet name in formulas?
Ie.
='Kit 1'!$B$27
='Kit 2'!$B$27
='Kit 3'!$B$27
...
='Kit 44'!$B$27
I have a list of people on sheet 1, column A. The corresponding information for each person is listed across 5 columns in sheet B (cols A:E). **The row numbers are consistent throughout the sheets (ie. Joe is listed in Row 5 on sheet 1, and his name and info is listed in Row 5 cols A:E on sheet 2).
Name
June Sales
Sales Quota
YTD Sales
Total June Payout
Joe
$5000
$3000
$20,000
$4,000
Susan
$4500
$3500
$21,000
$4,000
What I want to do is to customize this weekly report for each person: Joe gets only HIS information emailed to him in a "template" that would be standard for each person (see below) -- the template would be the words in blue with the corresponding info, and this would be put directly into the BODY of the email
Name
June Sales
Sales Quota
YTD Sales
Total June Payout
Joe
$5000
$3000
$20,000
$4,000
This would be repeated for all people on the list (100+). I think I can figure out some email code, but it is getting the information into the SAME template for each person repeatedly (not sure how to write a loop for this including the template)
I'm using sheet for ledger. For entering date I use =today() but I want to do that in date column(A) I set a formula only once that if I enter data in 2nd column it automatically add today date and if data enter day after tomorrow it will add corresponding date. Is this possible?
View 9 Replies View RelatedI'm trying to create an invoice with customer list, and the problem now is I'm trying to find a list-dependent data way to input my customer's address.
At the moment, all I can do is a dependent dropdowns from a sorted list (see method here). So it's kinda troublesome in such that my list (of customers) opens up a list for address that has only one option (since there's only one address for each customer).
I hope there's a method out there whereby I can have a list of customers, then select the customer I want, and the address of this particular customer will appear in another cell below automatically (without the need of selecting it in another list).
My address is actually split into three different cells - Address, Building & Postal Code.
I have created a spreadsheet that I must fill out daily. this worksheet has averages that must be automtically shared with a "master" worksheet.
for example
information from sheet2!b23
information from sheet3!b23
information from sheet4!b23
etc...
must automatically be transfered to
sheet1!c6
sheet1!c7
sheet1!c8
etc...
in that order
at this time I am typing in =sheet2!b23 on sheet1 everytime, I want to avoid this.
If you are asking yourself why? I dont blame you but it needs be this way so I can average each days spreadsheet.
I am trying to create a macro that will automatically fill in the days for a user if they fix the date. That is my end-goal, but obviously I have to take it step by step.
My first complication is how can I fill in all of the day rectangle boxes automatically? I was trying to create a macro for it, but it would only change the very first box. (You can see from my sheet, the days go 3 then 2)
After that I want to try and have constant-macro where if they change the month and the year it will automatically update the calendar for the user.
As I am a fresh user of excel 2007 I do not know how hard it is to solve the problem I am facing but hopefully there is a solution that the readers here are willing to share with me.
I have a list of **-s that I have recorded as a hobby with my friends (karaoke, free concerts...) and I have them coded in a specific way, so that I can find them on a shelve easily. The list is built up in a form that is seen on the example file that I added.
So far I have used my own handwriting and a sticker to label my ** boxes. Itīs not a very good looking combo to be honest. Since I already have all the data in my excel tabel it would be good to use excel to take all the things from there and just print it out.
How to do that? The front cover is easy, I used VLOOKUP function. All I have to do is enter a number into the column C1 and the front label gets filled.
The problem for me is the back cover that should take the right track nr, title and length from several rows (VLOOKUP uses only one row at a time)? How to make it work, that excel would know that on the first album there are 3 tracks and when chaging a number in C1 there would be 2 tracks on a second one.
I use a system which produces excel spreadsheets similar to this example (although much larger than this!). This makes it extremely difficult to carry out sorts and to tidy up the spreadsheet - generally I have to do this manually. I am hoping that there is a way that I can easily add in the data as per document 2 in red.
View 2 Replies View RelatedI'm wondering if it is possible to make a table where I can just enter the reference number and excel can fill up the remaining cells with data specific to the reference number from a website. What I mean is that I would like to put in company registration number and hopefully it will be able to pull in other data from companies house website about the company.
View 2 Replies View Relatedi had a problem to fill in weekdays of 2014 excluding friday and saturday as holidays.
View 2 Replies View Related