I'm using the conditional sum wizard to gather the cost of some projects based on two other conditions:
Column 1 is Country
Column 2 is Cost
Column 3 is Category
I have sucessfully used it with several countries but after sucessfully returning the correct resultsa for 5 countries it says "The formula you are building is too long. Please specify fewer conditions" - The amount of conditions is the same as the sum just before it which worked fine!!!
I am trying to create a formula in the Conditional Formatting wizard.
I would like the formula to format B2:F2 in a particular way if a count of the number of values in this range equals 5 AND if all values are less than or equal to 25.
I have tried with following formula without success:
=IF(AND(COUNT(B2:F2)=5),COUNTIF(B2:F2,”<=25”)=5),””))
How do you get around this? I only have TWO criteria!
The list is A1:T1000 in size.
How do I setup a STARTUP WIZARD in EXCEL which allows user can input information, and these information will be stored in the excel worksheet automatically.
The startup wizard is defined as a window which will pop up automatically when you open the Excel document. The wizard contains forms.
I have the dupe wizard add on, on my excel program. I am wondering why when I click on the Dupe Wizard button a message in a dialog box pops up saying 400. The Dupe Wizard application will not do it's job now, only the dialog box saying 400 pops up.
View 3 Replies View RelatedI am getting frustrated with the Text to Columns Wizard. After using it once on a spreadsheet, it continues to opperate whenever I paste.
Is there a way to stop it from automatically converting text to columns when I paste?
Is there a way to reset the defaults for the text import wizard? I occasionally import a number of files in CSV format, but the wizard defaults to tabs as the means of separating the text. I would like to reset the default on the second page of the wizard to commas, not tabs.
View 3 Replies View RelatedI am trying to write a Macro that will alow me to choose a text file to important but bypass the Import Text Wizard when doing so. I used the record macro function to get this
View 4 Replies View RelatedI am using this code to import txt documents, but once the file has been selected and everything, the excel data import wizard shows up... I now would like to get rid of this problem, as well add a standard path to this code, so that I dont always have to start from the desktop and click through tons of folders to get the folder containing my files.
View 12 Replies View RelatedI was reinstalling office on a laptop where the hard drive had been replaced. I used the 'save settings, wizard on my desktop and put in the new settings when I reinstalled Office.
This almost worked, but not quite.
Word and PowerPoint seem to be fine but when I reopened Excel I got a number of messages saying that objects could not be loaded because they were not on the machine. Some of this was because Excel was trying to reference add-ins that needed to be reinstalled. I have done that, and there are now fewer messages - but there are still two and I can't get rid of them. One looks for 'objects' the other a single 'object'. There seems no way of finding out what the objects are in question, and what it that Excel is stumbling over. Can I do anything other than abandoning my previous settings and starting again, or else living with having to click twice every time I start excel to get rid of the messages?
I have tried uninstalling Excel and reinstalling but this doesn't help. Perhaps registry changes are the problem...and I hate the registry.
I am pasting query results from WinSQL into Excel and use 'Paste' and then, from the little Paste Options icon, 'Use Text Import Wizard'. I choose tab delimited and then reset the decimal separater using the 'Advanced' button on step 3. Since I do this daily I would like to create a macro to do it all for me, namely paste what I have already copied to the clipboard using the appropriate settings. Recording doesn't reflect the steps I executed. How can I call the text import wizard from vba with the clipboard contents as input as opposed to a text file?
View 5 Replies View RelatedI'm wondering if it's possible from a button placed on a userform to call the pivot wizard and then create a pivot table, which is then saved and imported into my form ??
View 8 Replies View Relatedim building a user registration form in excel
now i realy would like to have a search form within my wizard but
i cant get it done.
i have read some other articals relating to this but they dont do
multiple field search (like day of birth and telephone numbers etc)
the form i would like to have should do a search for exmp. by phone number but than also displays all other corresponding user info in the results
the xls file included has some advanced features like automated building of commandbar menu's and has mailing features (currently quoted out) + build in timer events + on close auto save + allot.
I have created several userforms within my excel spreadsheet and i want to be able to click a command button on one form which will then hide the current form and show the next form. In previous spreadsheets i have used the following:
Private Sub CommandButton1_Click()
Form1.Hide
Form2.Show
End Sub
However, i am having trouble with this spreadsheet as when i am typing my code after the Form1. the hide command (and show) does not appear in the little pop scroll bar that predicts what command you plan to use. (If i type it regardless then it doesnt execute)
What is even stranger is that if i write a sub routine in a module such as:
Sub Test_Form()
Form1.Hide
Form2.Show
End Sub
And then call it:
Private Sub CommandButton1_Click()
Call Test_Form
End Sub
i'm trying to build an app that'll take a user through 5 or 6 steps of a process, with maybe 2-3 substeps in some steps. right now i'm currently using a multipage Userform , but all the tabs make the whole thing look cluttered (even when there's a "Next" button in plain sight). this is especially because i've put each substep as a multipage form under that step's page (multipage forms inside a multipage form). so steps 1-5 would each have their own pages (1-5), then in page 1 you'd have another multipage form (1.a. - 1.c.). is there a way to make it look simpler? i'd like it to look a bit like a Wizard (ie remove all the tabs), but the only way i can think of is to throw a whole heap of controls on one page, and turn them visible/invisible each step of the way.
View 4 Replies View RelatedI have a table of data as below recording daily foreign exchange rates.
Column A = Time and date
Column B = USA
Column C = UK
Column D = Aust.
Column E = Japan
Column F = Euro
Time and date USA UKAust.JapanEuro
04-Jan-20060.68390.39180.924379.470.5693
05-Jan-20060.68660.39050.918779.720.5666
06-Jan-20060.68710.39150.919579.720.5675
Then in column H i have a formula that works out if the rate has exceeded a specified base rate, if it has i display Threshold Exceeded and if it hasn’t i display No Action Required.
OK from here i want to count the number of times in the month the Threshold has been exceeded. I can do this by using the countif formula.
Now this is the section i need help on
For a specified time, say a month, i want to count the maximum number of times the Threshold has been exceeded consecutively.
E.g
Threshold Exceeded
Threshold Exceeded
Threshold Exceeded
Threshold Exceeded
Threshold Exceeded
No Action Required
No Action Required
Threshold Exceeded
Threshold Exceeded
Threshold Exceeded
Result = 5 (max number of consecutive Threshold Exceeded” for a specified time frame)
I am trying to create a line graph and I have 17 rows of data for 17 different employees. There are 18 rows when including the header row.
My data is as follows
Column A - Employee Name
Column B - Month 1 data
Column C - Month 2 data
Column D - Month 3 data
Column E - Month 4 data
Column F - Month 5 data
When I highlight the data and go to the chart wizard, I select "Series in Rows".
However, on the series tab it only brings in the data for 6 employees.
Is there a way to automatically bring in all the data without having to do it manually, or is this a limitation of Excel?
I am importing a text file that has a lot more than 256 columns. The data will be updated weekly with a different text file. I know how to get past the 256 column limit when importing a text file like this. This link explains the method I am using to import the text file. [url] Since I am importing onto several sheets I get the dialogue box asking me to choose a file to import from for every sheet when I use RefreshAll. The same file that is used by the first sheet will be used by the rest of the sheets. Each sheet just imports a different section of columns from the text file. I want to avoid choosing the same file 30 plus times (once for each sheet) when I click refresh all. Is there a code that would allow me to choose the file for the first sheet, and the other sheets would follow suite?
View 9 Replies View RelatedI have written a VBA program and using in Ms.Excel. The problem is when I try to write the VBA function through the function wizard it works but when I write the function in excel worksheet cell, it doesnt execute. I do not now the reason.
View 2 Replies View RelatedI created an Ms Excel Database Query to bring in data from MS Access. (versions 2002 of MS Excel and Ms Access). The query works fine initially. I can right click, choose Edit Query and change my criteria. Results are returned almost instantly.
My problem is that, once I save the workbook, or autosave happens, I get an error when I right-click to Edit Query: This query cannot be edited by the Query Wizard..
Is it possible to change the format of cell AI3 based on the format of cell C3 and D3? I have C3 and D3 set to turn red based on what is in cell C2 and D2. I would like the following done:
If AI3=C3 & C3 is red, format AI3 blue
If AI3=D3 & D3 is red, format AI3 blue
Otherwise, leave AI3 unformatted.
Possible???
Having trouble changing this formula from its current Median to a Quartile or Percentile. The median formula looks like this:
(MEDIAN(IF(DATA!$B$15:$DZ$15=1000,DATA!$B26:$DZ26))) and works perfect.
However I can't figure out how to manipulate it into a percentile keeping the condition of
IF(DATA!$B$15:$DZ$15=1000)
I need to run a loop through a column of values (attachment col B) and when it finds a "J" it will apply conditional formatting to a row of 4 cells directly adjacent. The attachment is a theoretical before & after.
View 2 Replies View RelatedI have a list containing blocks of stock price values with each block representing a series of values at a given time e.g
1400 156
1400 99
1400 74
1400 86
1400 256
The number of entries may vary for each block but is never more than 60. the next block may be e.g.
14:05 15
14:05 42
etc
and down a very long list of around 65000 entries. I'm looking for a routine that will add up all the values in each block and calculate the total for that time block. I then need to fill in an adjacent column with the perecentage that each value is of the total. A completed block may look like;..............
I have five fields that have a conditional format applied to them: (see attached).
Columns J, K, L, N and O. These conditionals highlight if a minimum number is entered. Ex. Minimum pushups for a 30 y/o male is 27, if a 26 is entered it highlights red. The total score (where I need this to perform) is calculated in column Q. It will format red if the total score is below 75 but what I can't figure out is how to make it format if any of the previous minimums have not been met.
Ex. In cell L4, his crunches were 25 (a automatic failure -- red formatting). Now the total score is above 75 so it calculates as passing (green), but I need it to highlight red regardless because of the minimum not met in cell L4.
I have a dataset of annual values which I need to combine into an array. Each year's data is in a separate worksheet, and contains between 4000 and 6000 rows. I set up a master sheet listing all unique locations for the entire period of record, finally got over the tics and brain damage from that one, and put in a VLOOKUP function for each column to retreive that year's data (examples-
=VLOOKUP($A2,'1995_5580'!$A$2:$E$5581,5,FALSE) =VLOOKUP($A2,'2002_6052'!$A$2:$E$6053,5,FALSE)). Works great. I still need to get something other than #N/A for not found values, but I can figure that out. Checking the results, I discovered that a very small percentage of the locations have more than one entry. In order to make this matrix useful, I need to have a sum of all values that match a given location. VLOOKUP only gives me the first match.
Now, the problems- I can't post a workbook, because it's confidential data. I would really prefer to stick with functions and not macros, because a lot of macros seem to get picked off by the firewall (I'm working on a network drive). I've seen similar problems addressed here with PivotTable (won't work here because a large number of locations are missing in one or more years and each year has a different number of data rows) and array formulas (data is on 23 separate worksheets). I really, really don't want to go through 100K+ rows of data to manually find duplicates and enter them into the master sheet. Not only would it short out my few remaining brain cells, it's inelegant.
how to view changes on my spreadsheet.
I have a tab called SAP DUMP where we copy our raw data to, from here we manually copy and paste into another sheet called ORDER TRACKING SHEET. When we do a fresh dump (overwrite) of raw data into the SAP DUMP tap we need to see if the there is a new entry which needs to be copied across to the ORDER TRACKING SHEET. I've got a vlookup to show this. The one I can't work out is that on the ORDER TRACKING SHEET I also need to see if there are any entries are now no longer on the SAP DUMP tab. This is where I thought a condition format might work, color the line red if it's no longer on the SAP DUMP tap. Can't make it work.....
I have to determine based on average number of weeks on hand whether an Item is Overstock, Obsolete, or Normal. I've got that taken care of with some If statements (I'm sure there's a cleaner way to do this but I've not figured that out yet.)
But I need my if statements to be conditional to whether or not an item is seasonal. with 10,000 active SKU's Its a little bit much to manage those in my excel copy of the item table so I wanted to make an exclusion table where I could just key the item in and the months it would be considered active in stock. This needs to go into the If statement on the items table that looks at the current date and says if it's less than six months old it's a "new item" but if it's six months and a day then it's a normal item, then ignore both if it's seasonal and just be a seasonal item if it is on the exclusion worksheet.
I'm attaching this incase anyone can offer any ideas. It would be much appreciated. I had to delete a ton off of the spread sheet to get it to upload, and the data is dummied to keep company inventory confiential.
I have this spreadsheet I'm making for Christmas to keep track of my wifes and my purchases. I'm having problem with a formula tho and I'm not sure how to go about this. Basically I have a "name" column. As you will see. At the bottom I have "total spent on cristina" "total spent on jon"
I need like an "IF" condition that will look at that name column and say, "if name is Jon" add up the amounts in the "total cost" column according to what Jon spent. If name is cristina, add up her amounts.
In a column, there are 4 option for each cell -
"PAYMENT DUE"
"PAID"
"UNDERPAID BY..."
'OVERPAID BY..."
These are filled in by a formula
I would like to make them 4 different colours but excel only allows 3 options