I have an excel file which contains a full consolidated list of data (Master Tracker). I am now creating a new tracker and what i want to happen is for it to show the list of names from the Master Tracker which exceeded the current date "now()" and the status is still pending.
FYI: The master tracker and New tracker is different excel files Here is an example:
Master Tracker:
client
Candidate
Case Date
[Code]....
I have a userform where I have 2 comboboxes. The first combobox shows the the first column (only 1 of each) and the second comboBox shows me the secondary list that correlates to the valuse in the first from column B. Now I have a text box that I am trying to get the value from column C depending on what I have in the first 2 comboboxes. What is the easiest way to do it? This is all in VB since it is a UserForm, and using Vlookup seems to be too many lines if I go that route. Is there a way to use Index and Match in VB where it would be more efficient? I attached just a sample of how the data would be layed out in the Excel sheet.
View 4 Replies View RelatedI'm using Excel 2007 and have created a spreadsheet for invoicing. I have created a dropdown list which then imports the address data using vlookup.
My problem is this. If there is no text in say, address field 4 the resulting field in the invoice shows as a 0 eg,
NAME
ADD1
ADD2
ADD3
0
POSTCODE
Is there a way to move the Postcode to the empty cell showing 0? and/or how do I make the cell to show blank instead of a 0.
I'm working on a function that gets its values from a Data Validation List.
My problem is that I want the function to return a value when an item in the list is selected and another if the user did not make a selection... just a basic if....then....else ... but the cell where I call this function shows the #VALUE! error while no item in the list is selected.
I call the function like this:
=myFunction(RC[-5], RC[-2])
where RC[-5] is the Data Validation List and RC[-2] is a cell where the user should enter a number
why I'm getting the #VALUE! error instead of my message
The function basically looks like this:
I have a worksheet (say, Sheet1) that I am going to manually import a large data set into on a weekly basis for reporting purposes. One of the columns from this data dump will have the header "Design Id" in the first row, but it may not be in the same column every time. I am trying to figure out how to create dropdown lists on a separate worksheet (say, Sheet2) in the same workbook where the data source always looks up the column containing the header "Design Id" from Sheet1 and then returns only the unique values from that column as options in the list.
View 1 Replies View RelatedI have a list in column A of percentages (to 4 decimals 0.0013) i need to conditional format the list so that it shows the lowest percentage in 'green' and the highest in 'red'.
View 5 Replies View RelatedI have set up a simple form in excel, whereby people use drop down menus to select the appropriate information (as all data inputted needs to be entered in exactly the same way to allow sorting, counting etc) so I thought drop downs were best. The trouble is the data is carried through to my excel database (on another worksheet) were it is shown in numeric values dependant on how far down the drop down list the select item appears (ie "Fareham" is third on the list, so shows in the database as "3").
I'd very much like my database to be easier to read and actually show Fareham etc, rather than be populated with lots of numbers. I initially tried using IF function (ie =IF(C3=3,"Fareham") but unfortunately two of my drop down lists are too long for this (with 25 and 33 entries respectively).
got this quite complciated formula.
any of the cells specified can be blank at start of spreadsheet
is there a way to hide the DIV/0 error from appearing
this is the formula:
=((((((SUM(C11:C14)*(100%-R2)+(SUM(ThroughputFormulas!C33:C36)*R2)*(L2/30))/1000)*N2)*3600)*P2))/(SUM(C39:C45))/1024/8
I already have a conditional format which is working OK. I would like to put a formula in the cell so it shows a % so currently it would be 100% as there is 3 cells below showing 3 greens.
Alternativily each colour is worth a value - Green = 1, Amber = 2, Red = 3. So Cell I3 would show 3.
I am trying to create a basic invoice spreadsheet.
On worksheet 1, I have days of the month A4 to A35. Columns B, C, D, E are headed.
Every hour of work I would enter a " 1 " in the correct cell for the day. For example, 1 hour, every segment of the day, I would enter "1" in cells Bx, Cx, Dx and Ex. This continues for the month;
On a 2nd worksheet, this is neatened up, and I have added formulas to work out the cost and type of call;
On the third worksheet, I want to display roughly the same, however when there has been no work on the day (ie the date displays "None" and the adjacent cells show: blank, blank, blank, blank, £0.00), I want it to skip the cell and display the next date which has data to show (ie a date). The data on the adjacent cells needs match with the date (in the same way as physically deleting a 'non worked' whole row).
This then on the invoice worksheet would look neat and display only the days actually worked.
What formula(s) could I use to do this.
Hi i have the following SUMPRODUCT formula which works as required, but when i clear the raw data and apply new raw data and apply macro that sorts my data, then all the ranges in the formula shows #REF!
=SUMPRODUCT((Raw_Data!C6:C5000=Summary!$B10)*(Raw_Data!A6:A5000=Summary!C$8)*(Raw_Data!E6:E5000>2))
I am facing an issue related to IF formula. It does show the correct result in one and another cell, but in a few others - doesn't.
This is strange since all variables are correct, and parameters are the same for every cell. And, in theory every cell should show the right answer.
In addition, the result doesn't change even when changing variables in cells that are dependants to cell where IF is located.
Concerning the formula itself. Variables are as such:
- different container types (20, 40, 40rf, 45cs, 45rf, 45)
- different weight limitations per container type (e.g. 20 container max weight 26t)
- using IF(OR(AND(....
- if any of the conditions met (e.g. 20 container < 26t ) then VLOOKUP for the rate from data based on the place name.
- if non of conditions met, then show "Overweight cargo"
I have following formula:
Why does this formula show result as "FALSE"?
If cell B56 is empty, then I need a result of "". i.e. blank result, but it shows FALSE word.
IF(LEFT(B56,2)="on",
then you go straight to the TRUE nested IFs
IF(LEFT(B56,2)="on",
IF(LEFT(B$52,3)="Pla",RIGHT(Table!AN4,2)&"% Discount",
IF(LEFT(B$52,3)="Gol",RIGHT(Table!AO4,2)&"% Discount",
IF(LEFT(B$52,3)="Sil",RIGHT(Table!AP4,2)&"% Discount",""))), "need something here" )
so to get ""
IF(LEFT(B56,2)="on",IF(LEFT(B$52,3)="Pla",RIGHT(Table!AN4,2)&"% Discount",IF(LEFT(B$52,3)="Gol",
RIGHT(Table!AO4,2)&"% Discount",IF(LEFT(B$52,3)="Sil",RIGHT(Table!AP4,2)&"% Discount",""))), "" )
I am getting 0 or 12:00 AM when I format cell as time and put formula.
I working on timesheet using this formula (=SUMIF('2'!G4,"="&TODAY(),'2'!E17)). If date in G4 on sheet2 match with todays/current date then copy data in cell E17 to sheet3(b11).
G4 = todays date
E17 = time eg. 2:25 AM ( I have to format destination cell as time because E17 has time value
Formula works fine but when there is no data in E17 or E17 is blank then my destination cell shows 12:00 AM.
On cell K7, i have this formula:
=IF(ISERROR(I7-J7),"",I7-J7)
On cell L7 i have this formula:
=IF(ISERROR(RANK(K7,$K$7:$K$29,0)),"",(RANK(K7,$K$7:$K$29,0)))
The issue is that in cell K7 a dash (-) shows up and ranks this as the number one, even when i have no data in I7 and J7.
MTD Conv
Conv Goal
B/W Goal
RK
-
1
29.3
31
(1.70)
12
21.8
25.0
(3.20)
15
Why is it that when I edit some cell's formulas and press enter the result is not the changed formula but the formula itself complete with the '=' sign infront of the fuormula. The work around for me is to cut the formula and paste it into a new cell then drag the old cell over the previous one I tried to edit.
View 3 Replies View RelatedI have created a vlookup and it shows as a formula not as data. I can do 'text to columns' to correct it, but I need to drag this vlookup to lots of different cells, then change it slightly in each one.
Every time I make a change, it reverts to the formula and I have to do 'text to columns' again.
I am linking two worksheets together. Formulas work fine except when I close the source sheet, it turnes into #REF error. I saw in one of the posts that you can not use INDIRECT formula on closed workbooks (INDIRECT is a part of the formula in my sheet) and downloaded the changingExternal. zip file as directed. Although, after staring at it for a while, I realized that I had no idea as to what was going on in there.
View 3 Replies View RelatedWhen I first started using excell I was relatively good at it, however a few changes were made. The main one being for excell 2002 was the collumns were not by letter. A minor problem that I managed to get past. However the next was a function issue. Before I remembered a way to total rows with a simple function like =b3*c3, which worked for the first one. Then, from that point, I could copy that formula and paste it to all of the cells in the collum in which the forumula changed for individual cells to =b4*c4, =b5*c5, etc.
My questions are simple. Is there a way that I can do this on Excell 2002 without having to do it on a cell by cell basis, and is there a way to change collumns back to letters.
I've attached the spreadsheet in which I want to figure this out on. The goal, is to total price and quantities sold into the totals section without doing it on a cell by cell basis.
I trying to write a conditional formula to test data in a cell to see where it falls between certain values, and depending on the data in the cell, will display a message in another cell showing what level has been attained. It's a long formula and I may not have the syntax correct. Can someone look at this and tell me where I have went wrong? All I get in the target cell is "################".
Here is the formula:
Try this formula. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas
=IF(c13<1600,"Not at prize level yet",IF(AND(c13>=1600,c13<=1999.99),"You have reached level 1",IF(AND(c13>1999.99,c13<=2499.99),"You have reached level 2",IF(AND(c13>2499.99,c13<=2999.99),You have reached level 3,IF(AND(c13>3000,c13<=3499.99),"You have reached level level 4",IF(c13>3500,"You have reached the top level!!"))))))
I'm using Excel 2010.
A formula I am using is outputting "0" instead of the expected value. Upon evaluating the formula I realized that this was because some of the values - years, in this case - had quotes around them while others did not. Please see the image below for the screenshot of the evaluation.
These values - the years - are being evaluated in the following formula.
Code:
=SUM(
IF(inventory!$F$3:$R$3=$A3,
IF(inventory!$B$4:$B$56=$G$1,
IF(inventory!$D$4:$D$56=$G$2,
inventory!$F$4:$R$56))))
This formula references the following sheet (an excerpt from that sheet). You can also see the formulas found in the cells causing the problem.
*ABC5212/20/20102010Adjustments53*20112011543/17/20112011Carwen Printers559/9/20112011Adjustments569/9/20112011Copeland Printing
Spreadsheet FormulasCellFormulaB52=IF(A52="",C52,YEAR(A52))B53=IF(A53="",C53,YEAR(A53))
B54=IF(A54="",C54,YEAR(A54))B55=IF(A55="",C55,YEAR(A55))B56=IF(A56="",C56,YEAR(A56))
I suspect that the problem is being caused by the output of the formula in these cells. If I simply type in "2011" instead of using the formula in B52:B56, then the first formula in the code section above does not have a problem.
How I can reconcile this?
I am trying to track inventory over 160 days in a spreadsheet. Every week I update a table that shows the number of skus and dollar amount that are over 160 days in a table. I have a ton of formulas that will automatically populate once I put in the new weekly data on a different spreadsheet. I want to track the weekly data so what I would really like to be able to do is paste the actual numbers and not the formulas into another excel spreadsheet so I can track the progress over time. Is there a way to just paste the actual numerical number instead of the formula itself?
View 2 Replies View RelatedBut the formula seems to be working properly.
I've got ALOT of parentheses, 5 nested IFs, 5 ANDs, 4 ORs. The ANDs and ORs are within the IFs.
While editing the formula, I can, at times, achieve the condition where the cell references become color-coded. Or at least some of them do. But, when I get to this point, I'm usually at a spot where I can see the formula isn't right.
I'm confused. What does it mean when my text is all red when I go to edit a formula? Did I hit some limit? Again, I don't get error messages and the formula seems to work properly.
I am working on a spreadsheet which has a column which shows square footages (Column AE) and I also have a column which shows costs (Column Y).
What I would like to do is to bracket these footages in to 6 bands (0 - 5000, 5000 - 10000, 10000 - 15000, 15000 - 20000, 20000 - 25000, 25000+) and then average the costs within a particular band.
I am looking for a way to show the character location number of a text file, possibly in the first row or a macro that I can run at any given location that will give me the location # I am currently viewing.
Currently, when I open the file I can see the character # at the opening screen (see attached file) but they disappear when it actually converts. I would like to be able to keep the character location ruler once the file is opened in Excel so I don't have to manually count.
I would like to have a validation table that, when used, provides a second validation table based on the selection made in the first one. My company provides a wide array of services that I would like to divide into categories - the first drop-down would list these categories ("Cleaning", "Repair", etc.), and then have the next column's drop-down only show those services under the "Cleaning", "Repair", etc. categories.
I know how to make validation tables (I have already created the different tables, with each service under its proper category), but I don't know to get Excel to make one list "pop up" after making a selection from another one.
I want 3 extra columns in the attached sheet which is part of a 62000 row sheet.
column d..I want a formula for the lowest ip number(COLUMN C) in each event( event number in column1) printed on all rows in column d fo that event...
column e...second lowest ip number for each event..
coolumn f..third lowest ip number for each event..
Because file size is large therefore I have uploaded the file to megaupload. Click the weblink below:
[url]
Is there formula or UDF which I can use in Column W in Pivot by Week worksheet tab so that I can consolidate all jobs for machine based on shift by day?
Have look in Column W in Pivot by Week worksheet tab for a sample for desired solution.
For Instance, in cell W7 I have used manual formula to consolidate all jobs for G16 Day.
=C9&", "&C10&", "&C18&", "&C19&", "&C20&", "&C21&", "&C23&", "&C24&", "&C25&", "&C32&", "&C36&", "&C37
All job that have a count is greater than 0 is included in my formula.
I need to consolidate the same for other machines as follow: ....
I have a report which has a list of customers, each customer has 24 columns which represent the payment history over 24 months. If a payment has been made for that month the date and time (formatted correctly) will be populated in this cell.
Each customer has a product name attached to it so a product can appear several times. I need is a formula that shows the total payments recieved for a particular month for a particular product. For example.
I have managed to create the following flag which works a treat, it picks up a date an account was set up but looks at 1 column.
This task joins a string together based on a number of characters per cell in the range.
I want to isolate one range, Col N, and add an IF condition to it.
There may be other issues preventing this from happening, e.g. the number of IF that exist in the complete formula. I will isolate the current cell and its requirements and then post the entire formula at the end for reference....