I work in a fruit and veg business and they are using parchment and quill still, well it certainly feels that way. I have been asked by customers to provide an online order form which i will email to them and they can fill in and email back, i scanned an invoice and used OCR to upload it to excel and it has come out quite well.
Basically there are three columns of product lines and then directly to the right of each product is a column i have entitled Box & KG respectively, if the customer types a number into the box column i want it to automatically add 'Bx' to the end so this if they typed 5 in a cell in the box column it would look like this '5 Bx' (obviously without the quotes), and the same for the KG column but it would denote 5 KG instead, also i would want it to add in the space as well after the value.
I have two tabs BIN QTY and ORDERS. On the ORDERS tab Column A is the order number and is duplicated several times depending how many line items are on the order. The second key is Column H which is the part number which is unique per sales order. I'm trying to use these two values to look up or find the matches on the Bin Qty tab.
-The data in Column A (order tab) gets compared to Column B (BIN QTY tab) -The second match is the Item no. Data Column H (order tab) to Column C in (BIN QTY tab).
-Once both values match it needs to pull the value from column G (BIN QTY tab) and enter it in column N (ORDERS tab).
My example is highlighted in blue with the result in green. I've tried several different index and match formulas with no luck. my Last was
=IFERROR(INDEX('BIN QTY'!$G$1:$G$7957,MATCH(ORDERS!$A1,'BIN QTY'!$B$1:$B$7957,0), MATCH($H2,'BIN QTY'!$C$1:$C$7957,0)),0) MY ORIGINAL SHEET IS MUCH LONGER
I have a VBA module in Access that extracts data and copies to an Excel spreadsheet. Every time I run the code additional data is extracted and displayed on a chart together with previous data. When I enter values to the series collection the first axis be it X or Y is entered correctly but the second axis produces error 1004 'unable to set XValues property of the series class' If I swap the lines of code around the error switches to 'Values' (y axis)
I tried to make use of the indirect function but don't think this is the right type of formula to be used.
Essentially in column A..i have linked cell references
e.g = D2767 However, for the whole of the column I'd like to change the refs to =$D$2767, ALL in column A in one go.
I know that if you place the cursor in one cell immediately after the = sign and press F4 it automatically adds $ SIGNS TO THE CELL, which I find useful for V-lookups, but I obviously cannot do it (fill it) all the way down the column i'm using excel v7.
In addition or related how would I write an IF formula to read eg.
= IF (cell ref begins with C, then link and add PRECEDING AND FOLLOWING $ signs to all refs)?
I have a home finance spreadsheet that I've been using for a few years and in the last couple of days it's ground to a halt. Whenever I enter a value in a cell and hit [ENTER] there is a long delay with the spinning wait icon (Windows 7) and the Excel window reports "Not responding". Eventually (3-4 minutes) the update occurs and everything is fine.
The workbook has 9 sheets on it but only one summary sheet references the others. The sheet I'm updating has about 2200 rows, 50 columns and the formulas are simple A+B-C types. Performance DOES NOT improve if I change recalculation to manual. The only VBA is some macros to perform tasks and they don't execute dynamically. Adding comments and changing formats responds immediately, it is only updating values that exhibits the problem.
I've tried replacing all formulas with their values (paste special/values) across the sheet but again this didn't work.
I have created a budget template. In some instances, a discount is offered when more that x amount of products are ordered ( it varies,so I'll have to change the macro for each instance). I'd like to have a pop up box appear ( and dissapear again when pressing 'enter') that gives a notification like: a discount is applicable, select'
my cell with entered amount is "I54", and want a message if entered above 5
I tried the below and swapped places between sentence 1 and two as it doesn't seem to recognize subname, or sub start, or it just dissapears from my macro list ( although still a module): ( in the below order, it stated it expects the sub to end after sentence 1, if i remove sub popup () ( the title) ut just deletes the thing from my macro lsit,w hat is goign on!, and it just wont work)
'Sub popup() Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range
the code of how do stop saving a report if the spefic cells are not filled.
For ex: In a excel sheet in we have A1,A2,A3 there should be some values else its should not save and should show an alert stating that this cell is empty....
I am trying to find a way to populate a cell with spaces (like " "). But I want to populate a cell based on a number in another cell.
For example, if cell A1 has a value of 7 in it, I want to make another cell have seven spaces (or seven letter Z's, or anything), but I can't figure out how to do it. Then I want to use that equation to do any number of spaces based on the number in the cell, whatever it is.
I would like to sort words or data which has the First capital letter, words which has spaces and words which contains number... i have attached a sample file.
Is there an Excel formula to remove the spacebar + characters in red, as shown below? I need to be left with only the last name, first name and the semicolon.
Mouse, Mickey ;
Microsoft Outlook has changed the way that email addresses from the global addressbook copy and paste (from version 2003 to version 2010).
In some of my formulas, I need to re-enter the contents of the cell in order for a formula to compute.
For example, I am doing some math on a cell whose original contents are "10:21:00 AM" The formula returns a #NUM!. If I retype 10:21 in the cell and hit enter, the formula will now calculate properly. I have reformatted the entire column several times so that it is a custom hh:mm format. The original content also shows as time so I don't think that it is a formatting issue.
My worksheet has about 20,000 rows and this only happens for about 100 of the rows. It is pretty time consuming to manually go fix each one. Any ideas what is going on here?
I'm trying to get a formula that will enter a particular value when 2 specific criteria are met. I've been trying to use an =IF(AND( statement, but so far I keep getting a "the formula you typed contains an error" message.
I need to have cell V9 return a value of 8 if the following conditions are met: Cell D9 = "SG" Cell H8 of a different workbook (Called Work Schedule) = 3
I would like the value of V9 to change to 12 if: Cell D9 = "SG" Cell H8 of 'Work Schedule' = 19
My formula now looks like this: =IF(AND(D9="SG",'WORK SCHEDULE'!H8=3,8,IF(AND(D9="SG",'WORK SCHEDULE'!H8=19,12,""))))
If I use format cells and choose time... do I have to enter the time in decimals to get it to show up like 1:30 PM? Or is there a way for me to type something quicker like 130 p?
So we have people's names in a table. First name in one column and last name in another column. We have a 3rd column where we can use 8 characters to do a combination of First 5 of Last Name + first 3 of first name. However, if someone's last name is only 3 or 4 characters, we'd then want to take more from the first name to fill out the 8 characters.
So: Charles Johnson -> Johnscha John Smith -> Smithjoh Willian Wu -> Wuwillia
What's the best way to do that without creating some crazy formula with tons of if/thens?
I'm trying to copy about 10,000 barcode over to a new spreadsheet, the prob is that when I copy them over they appear as XXXX+12 and then when I try and use them it doesn't like it. I can solve it if I format the column to text - and then double click it. This is ok for a hundred or so but fingers start to hurt after that!
I would like to be able to choose a month (from a drop down menu), then choose who the payment will go to ie. creditor (from a drop down menu) ie. car, truck, cc payment, as a result of those selections, then enter the amount of the payment, and finally the ending balance will appear. I have the drop down menus in place and I have all the balance calculations in place. I could use the spreadsheet that I have but I only want to see the below info. Plus I don't know how to link or lookup this data to the stuff I've already created.
Select Month: Select Creditor: Enter Payment: New Balance:
I have already set the sheet up calculating the balances after a payment has been made. I know how to hide all the formulas, but I don't know how to get it to so that you choose a month, then creditor type (from drop down menus), and payment so that you see a new balance. I only want to be able to link or lookup the month, creditor together so that a payment can be made to that cell that I've already established. As a result, then that cell can be shown in the new balance.
I really DO have a problem. I need excel to create me a list of whenever someone enters their name. Now, this may sound simple, but, the text in the list CANNOT be allowed to be overwritten.Also the text needs to be entered into the NEXT empty cell between C30 and C50. This is basically so I can keep a record of whenever anybody does anything/modifies the cells that include text between S7 andS21
So basically if text is ever entered into S7 all the way down to S21, I need an automatic copy created into the next available cell between C30 and 390. It is also ok if there are duplicates values(names).
I would give a print screen, but I don't know where the attach button is on this forum.
I'm building a database which I intend to upload to an ecommerce website. I am retrieving information on some of our products from the manufacturer's website. The information appears on the manufacturer's website as follows:
Part No : DP9970
* Description : Disc Pads * Manufacturer : Trupart * Unit Of Sale : Boxed Set * Width (mm): 130 * Height (mm): 62 * Thickness (mm): 19 * Note: 4 pads with top clip
When I copy and paste this info into excel, it gives each individual line of information a separate cell. I need the information to appear in one cell in order for it to display properly on my website.
I need to find a way to enter times into excel without having to use a colon.
The data I need in the cell is the hour, the minute, and either AM or PM. No seconds, which I believe is why Chip Pearson’s time VBA procedure doesn’t work for me, and I don’t know enough about VBA to change it for my needs.
So for example, I need to enter in times like 10:09 PM or 12:40 AM or 2:33 AM.
All my times will be entered into Column D.
I have done a ton of searching and tried several different solutions, but none of them allow me to put in AM or PM. If I just put 1009 into a cell with Chip Pearson’s macro, it will display as 10:09 AM, which is great if I need it to be AM. But if I need it to be PM, and I enter in “1009 p”, it will display as “1009 p”, neglecting the colon and not displaying the time correctly.
Does anyone have a solution where I can type in a 3 or 4 digit number (233 or 1240) and either “a” or “p” after it, and have it display as 2:33 PM or 12:40 AM?
I have a column of values, and I want a column next to it that shows the value in every second cell in the first column, but right underneath. See below for an example of what I am trying to achieve. The actual column is 17000 cells so I cant do it manually.
I am a beginner in VBA programming and I have to make a Userform for entering data in the specific places(in a table) in the worksheet. But when I enter a number, it changes all the cells to that number. I can't find my error.
