Compiling A Sentence Into An InputBox From Various Cells
Jul 30, 2009
I'm creating an InputBox that pulls from various cells in my worksheet to create a sentence as the default value in the text-space. For example:
A1 = Policy Number
B1 = Credit Amount (i.e. $10)
C1 = $ Info [i.e. if credit is for one month (1 x 10), if two months (2 x 10)]
D1 = Date Policy Ended
E1 = Months Excluded from Bills
Thus, the sentence that the InputBox would display would be:
Today's Date + A1 + B1 + C1 + D1 + E1
Date - Policy # - Credit Amount - Info - Date Ended - Months Excluded
A text example would be:
30JUL2009 - 1234 - $10 - 1x10 - 01JUN2009 - Exc Jul,Aug09
The issue I'm having is how to have the InputBox macro ignore one of the cells if it's empty. My current code is:
View 6 Replies
ADVERTISEMENT
Aug 11, 2009
I've been given the task of automating a spreadsheet to assess whether work has been done by each employee. Every employee has their own spreadsheet, where column A is a job code, and column H contains either y or n dentoting whether they have completed the task (y) or not (n).
The main spreadsheet is designed to show any outstadning tasks across everybody so it has a list of everybodies names in column A and then any incomplete tasks will be listed from columns b onwards next to the appropriate person. Here is what I have done at the moment.
View 3 Replies
View Related
Apr 16, 2014
How do I enforce for ranges A1:A10 and C1:C10 that whatever is entered in these cells is changed to sentence case, i.e. "today it is Raining." will change to "Today it is raining.".
I thought of having helper columns with the following formula that would then paste over the ranges on a Workbook.close event but it seems long-winded and not the right way of doing it.
Formula for helper columns:
[Code] ......
View 13 Replies
View Related
Jun 30, 2009
I have the inputbox so i can set a string value,
When the inputbox Cancle button is pressed i want to exit sub,
If the inputbox value is nothink and ok button, I want the msgbox displayed then goto newname.
If the inputbox has a value do >>>>>>That>>>>>
View 6 Replies
View Related
Aug 26, 2009
I'm using an inputbox to let the user select a range of cells. Here's the line
Set MyRange = Application.InputBox("Select cells with numeric data", Type:=8)
When the inputbox comes up, it won't let the user click any cells (or anywhere in the worksheet, for that matter). The cell/range reference has to be typed in manually.
View 9 Replies
View Related
Jun 22, 2006
i need code to find name and compile the value cell next to it...This is how it goes..
File A (2 columns) consist of name,Cash and the name tends to have many repeats (for some reason)
i want my file B to let user first key in a name(which should be the same as File A) and Compile all the cost of that particular name and display in a cell in file B
View 9 Replies
View Related
Mar 8, 2006
I have 5 sheets in 1 wookbook with about 15 columns of varying rows of data. What I need to do is create a macro which collates all the rows of data, which with their own heading, onto 1 sheet so that it can be printed and distributed.
The problem is of course is that normal copy and paste won't work because the rows may vary and the macro need to take that into consideration. I guess probably an 'If not or' type command that would copy and paste the row so long as it had values then move onto the next sheet and continue from the last one.
View 9 Replies
View Related
Feb 19, 2009
I have a row of text (for example say they are cells c3, d3, e3, f3) that have one of four text strings (red, yellow, green, blue). In cell b3, I need it to report back the column heading of all the cells that have the text "blue".
So if cells c3 and f3 have text "blue", then I need their column headings to be inserted into cell b3.
View 9 Replies
View Related
Aug 10, 2006
I have several tabs in a spready, and I want to be able to compile a range of cells from each tab (constant selection of cells from each tab) together as a list in a new sheet.
The list needs to be able to be populated in any order and with repetition.
I'm thinking some sort of sheet for selecting which tabs to bring in info from would be useful, and then a command button to compile all the info into one list on a new tab...but I have no idea how to write the code for it!
View 3 Replies
View Related
May 13, 2014
I have 3 different sheets with data that I want to combine into one sheet using a formula. Here is an example:
Sheet A
Date Amount
10/1 $5
10/2 $10
10/7 $5
Sheet B
Date Amount
10/2 $7
10/3 $10
Sheet C
Date Amount
10/5 $5
10/6 $10
I want a fourth sheet that fills with all the data from these three sheets. Ideally it would be in order by date, but I understand that might not be possible. So the last sheet should look like this:
Date Amount
10/1 $5
10/2 $10
10/2 $7
10/3 $10
10/5 $5
10/6 $10
10/7 $5
The reason I want to use a formula of some sort is because if I add more rows to sheet A for example, I want the fourth sheet to automatically add it (whether it's possible to do in date order or not).
View 2 Replies
View Related
Jul 27, 2009
I have a sheet called "Usage Data" and in that sheet Column A is called "DTL_LOC" (Warehouse #)
I need to pull all data from this column and create a list with no duplicates. Data is both Text & Numeric.
I need to use this information in a Validation List on my "Inventory" sheet in C1
The list is currently located in L3:L102 on the "Inventory" sheet. However this list may increase or decrease as the company grows.
View 14 Replies
View Related
Mar 6, 2012
I have a file with 26 worksheets, each have four columns and the last column is averaged at the bottom, and I'd like to make a summary worksheet tab showing the name of the table, the number of records, and the average score of all 26 sheets.
The problem is, some worksheets have 20 rows, some have 1400 rows. I could go choose the data manually, but isn't there an easier way to do this?
The table name is merge/centered across the four columns, and is the worksheet name (using a formula I found on here). Then the four columns have headers, then data. The last row of each worksheet doesn't have the normal data in it, but says "Records Counted," then has a COUNT of the rows, then has "Average," then has an average of the column above.
View 1 Replies
View Related
Sep 3, 2013
I've been asked to compile and sum weekly sales data. The problem is that the data was supplied in 52 separate tabs, for each week of the year (I don't know who on earth thought that would be the most appropriate way to send the data).
Is there any quick way to import all the data from each tab into one worksheet? The data is laid out the exact same way on each tab. All the row and column headings are the same, and in the same cells. Only the numbers are different (obviously).
View 2 Replies
View Related
Apr 23, 2009
I have looked all over the place but nothing seems to work for me.
In detail, I have a quote book i made with two sheets for different types of items on them. For example:
Sheet 1 is for wooden items, and sheet 2 is for metal items.
So after i enter all the items on sheet one and sheet two, I want to be able to combine all the items from both sheets into one summary sheet that i can send to the customer.
I tried consolidate, and messed around with pivot tabels but it didnt seem to be what i'm looking for.
View 9 Replies
View Related
Oct 25, 2009
I have a large amount of data that I need to be able to summarize certain fields in order to be able to work with the data.
What I have is multiple lines which all pertain to one record and I need to be able to summarize these into one line - which would give me a sum in certain columns. I am working with thousands of lines of data.
There is about 10 columns in the worksheet. I want to be able to tell Excel that if certain columns all match, then to summarize these lines and give me a sum total on a couple of other columns. The data I am working with is invoice data, so I want to know if certain columns - ie - Vendor name, invoice number, cheque number are the same in each row, then to sum these records and provide a total for the invoice amount and cheque amount columns.
This sounds like a complex function to me but I am hoping that someone has a solution for me.
View 10 Replies
View Related
Jul 7, 2014
I am working on a spreadsheet where I need to have data from different sheets added but based on the instructor whose data I want to see in the summary sheet. I want to be able to type the name of the instructor in a cell and have all of the data appear below. I believe that an index match formula would work, but I do not know how to have the data from the different sheets added nested in the formula.
View 2 Replies
View Related
May 17, 2009
I have been trying to compile some monthly and annual data from our main sheet to a FY09 sheet (for the fiscal year 2009). The 2 tabs in the uploaded example are the Distribution tab which we use to track the status of every item and the FY09 tab where I need the totals to be calculated for each month as well as the entire year. I have tried several formulas I found while searching the forum but I can not seem to get any of them to work, (I am sure it is because I don't understand them very well).
Since any formulas used will need to be copied 200 or so times, I would really like a VB solution which should also reduce the physical size of the file. I also tried a pivot table but I do not think it will show everything I need.
Basically, I need all the items separated by month on the FY09 tab. Then column 'F' on the Distribution tab needs to be summed up for each item in column 'D' of the FY09 tab for the respective month and multiplied by the respective item price in column'C' with the total value going in column 'E'. Column 'D' and 'E' need to be summed up for each month and cumulative for the entire year. There needs to be an average items and value for each month as well as for the year.
View 8 Replies
View Related
May 2, 2013
I am compiling an Excel programme with two sheets, One sheet is constantly being updated with data from a website. The download from the website is quite messy and I have to sort through the relevant data. I need to compile two lists on Sheet two that will take the Name from Column A1 and Value from B1 on Sheet 1 but only If the value of C1 is above 5 or below 0 (negative).If the value isn't correct, it will ignore that row and skip down to A2 B2 C2 etc. Hoping to get two tidy lists on Sheet 2 with no blanks/gaps.
View 1 Replies
View Related
May 6, 2014
Here is my code thus far - it seems to go through the directory and copy data (single columns of 101 data-points) from the workbooks, but does not successfully paste it into the master-workbook (error message reads: 'paste method of worksheet class failed').
VB:
Sub LoopThroughDirectory()
Dim MyFile As String
Dim ecolumn
MyFile = Dir("C:Documents and SettingskbriemMy DocumentsErindi_i_vinnsluEXCELprufa1")
[Code] ....
View 3 Replies
View Related
Aug 22, 2013
I do not know what function will do what I need. I am compiling info from different sources to make a mailing list. Basically it boils down to the following. Sheet 1 has Names and Parcel Numbers, sheet 2 has Parcel Numbers and Addresses. I am trying to combine it in a way that I have names and addresses on the same sheet.
View 2 Replies
View Related
Aug 1, 2014
I have several workbooks (5) with the same variables (columns- A:Q) but with a changing amount of rows (2:n, not including the headers). Each row corresponds to a date range (usually a week) for a particular person (up to 40 people) plus a few other values.
I would like to have a way of "merging" or "compiling" the 5 "seed" workbooks into 1 "master" worksheet. Where rows 2:n of each of the 5 "seed" workbooks are added to the master without any duplication of the same name-date range combination. Also, the master worksheet should not include the rows which only contain a name and date range but for which all the other variables are zero or missing.
Each "seed" workbook would have a button that sends the data over to the "master" worksheet.
Is this a really difficult project? Feasible for someone with near to zero VBA experience?
I attached 3 files to show you what I mean. The 2 "seed" files are merged into the "master" file. Please note that in the files only 2 names are used, but the "seed" files could contain any combination of 40 some names. Also note that the length of rows which contains data in the "seed" files is variable, although it should not be longer than 16 rows + the header row.
View 14 Replies
View Related
Oct 8, 2008
Is there a way to get Excel to automatically change the first letter into an uppercase when we start a new sentence just like MS Word ?
View 9 Replies
View Related
Sep 22, 2009
I have a word document with sentences which has to be broken down with length of 65 characters (words should not be broken). This has to be stored in consecutive cells.
Example
sample text input
This is a sample sentence format from a document which has to be broken down with length of 65. This is created by 'VENOM' on 22-09-2009 for sampling it into a excel sheet named as 'The sample.xls'.
Output
This is a sample sentence format from a document which has to be
broken down with length of 65. This is created by 'VENOM' on
22-09-2009 for sampling it into a excel sheet named as
'The sample.xls'.
REQ:
1. words should not break
2. words with special characters should not break(like.. 22-09-2009)
3. words in quotes has to come in full (like.. 'The sample.xls'.)
View 9 Replies
View Related
Jun 17, 2014
I was having trouble on getting a text string within a sentence..
Example:
In column A1:
1 - AMERICA 85 - 90 2 - CHINA
So I want to get only the 85 - 90 and it will shows on column A3..
View 9 Replies
View Related
Aug 25, 2014
I want to EXTRACT LAST 4 numbers from a sentence
EX:
A1:what is your name 1234
To be
B1:1234
View 8 Replies
View Related
Jan 11, 2014
I have tried to set one formula which will given the the Numbers of the Vehicle. However as there are other numbers also which makes it difficult to do so.
View 5 Replies
View Related
Mar 6, 2009
im having trouble getting this date to format in a sentence
View 2 Replies
View Related
Jun 19, 2009
Is there a way to format a number within a sentence? i.e.
In cell A1 I have 2,000 entered and formatted as a number. In cell A2 I type in ="How can I format "&A1&" ?"
When I do this, the result in A2 is How can I format 2000?
Is there a way to format the 2000 to show the thousand separator comma? If the number were -2,000 instead, is there a way to show both the comma and parentheses around the number? (without putting the parentheses manually into the formula)?
View 4 Replies
View Related
Aug 11, 2009
looking for an equation or macro that can extract numbers out of a sentence.
Eg, 'There were 300 children in a school, and 10 classes of children' I need to extrcat the 300 and the 10 into 2 cells.
There may be more than 2 numbers in longer sentences. Is there anyway to do this?
View 14 Replies
View Related
Nov 16, 2009
i really don't know what would be the title, so just hoping that's good for you guys. by the way i need some tricky formula. i have a sentence like this:
Black shoes $250.00, Plasma TV $1,000.00, date to be paid 11/29/09, remaining amount $10,000.00, Computer package $16,000.00.
i want this to be (in different cell):
Black shoes $250.00
Plasma TV $1,000.00
date to be paid 11/29/09
remaining amount and so on...
I did text to columns, but the problem is the amount that has "," really bothers me. can someone give me some tips to do this?
View 5 Replies
View Related
