Over 1 million tech questions and answers.

Solved: Excel 2007 - Dynamic formula based on Data Validation List

Q: Solved: Excel 2007 - Dynamic formula based on Data Validation List

Hi all,

I am trying to develop a cost calculator based on several drop down lists. I have used the following formula:

=AVERAGEIFS('Scope Of Work'!I:I,'Scope Of Work'!C:C,C6,'Scope Of Work'!F:F,'Cost Calculation'!D6,'Scope Of Work'!G:G,'Cost Calculation'!E6)

Cells C6, D6 and E6 in the Cost Calculation Tab are all drop down menus based on data validation. The formula works when all 3 cells have a selection value but I also want it to calculate if only one or two of the drop down lists have been populated, any ideas?

Thanks,

Mark.

RELEVANCY SCORE 200
Preferred Solution: Solved: Excel 2007 - Dynamic formula based on Data Validation List

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: Solved: Excel 2007 - Dynamic formula based on Data Validation List

Read other 11 answers
RELEVANCY SCORE 106.4

Hi There

I'm not sure if this is possible or not. I checked the internet and coud not find a solution. I have created a very simple dropdown list with only 2 entries. I typed in the entries in the list source box. Is there a way that the user can also type in any other entry of his/her choosing to the cell without the message appearing saying that the entry is not valid?? If it is not possible please let me know.

Thanks
 

A:Solved: Data Validation list (Excel 2000)

Just turn off error alert in Data Validation Option
 

Read other 2 answers
RELEVANCY SCORE 103.2

Hi

I use MS 2010, and in my Excel work book I have a list which works fine for me. But when I send it to others, the cell with the list in does work. - By not working I mean that there is no "arrow" to access the different variables that the list should show.

I assume this is a compatibility issue. Was just wondering if there were any known solution.

Thanks
 

A:Solved: List function in data validation not compatible with earlier version of Excel

Read other 14 answers
RELEVANCY SCORE 102.8

I am using Excel 2010

I want to limit a validation list based on the contents of Cell D4
There are 4 lists possible based on the contents of D4:

If cell is 1 = list (on datasheet tab) should be limited to range A42:A71 only

If cell is 2 = list (on datasheet tab) should be limited to range A72:A87 only

If cell is 3 = list (on datasheet tab) should be limited to range A88:A90 only

If cell is 4 = list (on datasheet tab) should be limited to range A92:A93 only
Thanks
 

A:Excel 2010: Limit validation list based on another cell

Soooo... is this possible or no?
 

Read other 3 answers
RELEVANCY SCORE 100.4

It's difficult to describe my situation in words, so I'll just show you what I'm trying to do.

I have a table that looks like this:

A, B, C
Name, Hours, OT hours
Jane, 166, 0
Jack, 163, 17
Mike, 168, 0
Dot, 167, 24
Erin, 164, 8
Carmen 158,0

and I want to create a list that will have 2 rows if there are OT hours, and 1 if there are not.

A, B
Jane, 166
Jack, 163
Jack,17
Mike,168
Dot, 167
Dot, 24
Erin, 164
Erin,8
Carmen, 158

I can't use macros or VBA. It has to be formula driven. I can use a helper column or two. The starting table will change depending on who charged hours where. I've been trying to use IF and OFFSET and ROWS to do it, and it works until there's 2 consecutive rows that have OT hours.

Just to clarify, I just need to know how to get column A listed properly elsewhere in the spreadsheet - I can derive Column B using vlookup.

Thanks so much!
 

A:Solved: MS Excel: create list based on data in a simple table

2 Formulas - 1 for finding the name, the other for finding the appropriate value.
Note - must start on row 3 or greater.

Names (mine start in cell G3): =IF(G2<>"",IF(G1=G2,INDEX($A$2:$C$7,MATCH(G2,$A$2:$A$7,FALSE)+1,1),IF(VLOOKUP(G2,$A$2:$C$7,3,FALSE)>0,G2,INDEX($A$2:$C$7,MATCH(G2,$A$2:$A$7,FALSE)+1,1))),$A$2)

Hours (mine start in cell H3): =IF(G3=G2,VLOOKUP(G3,$A$2:$C$7,3,FALSE),VLOOKUP(G3,$A$2:$C$7,2,FALSE))
 

Read other 3 answers
RELEVANCY SCORE 99.6

Hi there
sincerely hoping & wondering whether someone out here could help me by offering me your solution or suggestion ?

My situation is as follows:
In my worksheet there is about 600 cells scattered all over the sheet that are to represent different monetary value figures, that I have used NAME MANAGER to define as MONEDATA1, MONEDATA2, and MONEDATA3 (each holding around 200 cells, which is the approx.limit allowed by NAME MANAGER for Excel 2010). Thing is, my work revolves around dealing with 12 different countries of currencies, and so all these monetary cells, in CURRENCY format, thus need to be 1 out of 12 different currencies with respective currency signs, at any one time I do some calculation. So, they must convert into 1 of those 12 currencies, each time determined by a selection from a single dropdown list of 12 currencies located at the top of the worksheet in one particular cell I data validated, which is $C$6.

Now, I just need the worksheet to convert all the 3 groups MONEDATA1, MONEDATA2 and 3 simultaneously, instantly when 1 of the 12 currencies is chosen in $C$6, by using a VBA code. Is there a best way to resolve this need ?

Could you help me with the above problem ? Thanks in anticipation and advance ! Greatly appreciated any help.
cheers & have a nice day there,
Jason.
 

A:Excel data validation and executing a code based on selected value

 

Read other 1 answers
RELEVANCY SCORE 99.2

...hope I named the thread right.

Good day everyone. This one thing in Excel has been driving me crazy for a couple of hours now. I've looked all over the internet trying to find the solution and still couldn't. Hoping to find the solution here. otherwise I might stay up all night tonight, trying to make this work

so I'm putting together this custom spreadsheet for my own little needs. let's say I have A1, B1 and C1 cells. in the C1 cell I want to display "1" if the numbers in A1 and B1 are equal and they are not equal then display nothing. so the formula I'm using is

=IF(A1=B1,1,"")

and is there are any numbers in A1 and B1 excel interprets everything just fine. but if if there are no numbers yet, it takes two empty cells as having the similar data and still gives me "1" in C1 cell. so how do I make so that Excel does not execute the IF formula before I enter the numbers in A1 and B1. or is there another of of not getting the "result" before actually putting in the numbers?

hope that someone can help me with this thing.
 

A:Solved: Excel 2007, stop IF formula if no data entered.

Read other 12 answers
RELEVANCY SCORE 98

Hey,,

Here's the deal

I am linking through excel 2007 to an access 2007 DB Query (with columns A & B).
Column A & B show correctly in excel I have added a third column C, to calculate the running sum of A&B
Thus
C1 = Running Sum
C2 = (A2+B2)
C3 = (A3+B3)+C2
C4 = (A4+B4)+C3
C5 = (A5+B5)+C4
C6 = (A6+B6)+C5
...and so on and so forth...

Now I hit the refresh external data button and ......$%##!!!
Suddenly Excel in all his wisdom decided that he feels the need to edit my formula...
Now it reads as follow:
C1 = Running Sum
C2 = (A2+B2)+C1 //the "+C1" is automatically inserted by Excel & results in a Error
C3 = (A3+B3)+C2
C4 = (A4+B4)+C3
C5 = (A5+B5)+C4
C6 = (A6+B6)+C5
...and so on and so forth...

How do I kill this bug?
 

A:Solved: Excel 2007 - Formula Error After External Data Update

The problem is that when you refersh you must have chosen to clear the table so your formula is lost.
What I usually do is refresh and the then have it run a amcro afterwards that reinserts my column with my needs.
 

Read other 2 answers
RELEVANCY SCORE 96

Any suggestions for removing blanks from an Excel 2000 drop-down validation list?
Data validation procedures I found at the MVP site work fine in a new (empty) spreadsheet but cannot get it to work in a spread sheet that already contains lists. Lists are named ranges in several columns but on the same worksheet. The data validation area is on a separate worksheet.
In one column, the drop down works fine but in another column the dropdown starts at the bottom and I must scroll up past blank lines to see the list.

Hope this helps:

Sheet 1 = columns that will contain validation cells
Sheet 2 = Named Range 1 in col. 1, Named Range 2 in col. 2 . . .

On Sheet 1:
col 1 dropdown list =OK
col 2 dropdown list = blanks and must scroll up to see beginning of list (only 3 items in this list)

Thank for any help you can provide.
 

A:Excel Data Validation Blank in Drop Down List

Your named ranges contain blank cells. Anyway you can change the range NOT to include them?
 

Read other 1 answers
RELEVANCY SCORE 96

I am trying to hide rows of my worksheet that show a date in the completed column.
The orders that are complete have a date in column F up to this point I highlight them green so I know they are completed but it would be nice if they highlighted green and were hidden when a date was entered into the completed column.
Can someone assist me with creating a macro that will do this?
I am very new to macros so please forgive my lack of knowledge.

I have attached a sample of what I am doing however inf has been changed.

I am using Excel 2007

Thank you,
 

A:Solved: Macro for excel 2007 to hide rows based on any data entered in a column

Hi, welcome to the forum,

I put some simple code in the Sheet's vba

Make sure you allow macro's to be run

Just enter a date in the last row of column F for testing.

For the other green rows, just click in column F and update the date by retyping it
 

Read other 3 answers
RELEVANCY SCORE 94

Hi.

I'm a beginner when it comes to VB Script and so I am struggling to work this out. I basically have a worksheet that contains information about different staff members. The sheet is set up so that column A (from row 8 on) is their name and columns B to L (also from row 8 on) are other staff attributes. Due to several reasons, there is more than one listing for each staff member (up to 40). The database is supposed to be there so that any staff member can come in and look up their information (or that of any other staff member). However, with so many listings and so many staff members, it becomes an unmanageable database. I could just create filters so that staff members could simply filter out other employees; however, this would create issues. Instead, I have created a cell above the table (say C2) where staff members can enter their name via means of a data validation list (which is linked to a list of staff members on another sheet). I would then like all rows in the table to hide except for those where the name in column A = C2.

I have searched this on the net and I keep seeing similar responses that all say it's hard when there is data validation / formulas involved. Is there a way this can be done?

Cheers
 

Read other answers
RELEVANCY SCORE 93.6

Hi all,

I'm trying to create a form with the use of data validation so that staff do not key in wrong values. The condition is:

1) The year of manufacture for a new insured cannot exceed 8 years
2) If the insured is not a renewal (not new insured) than the year of manufacture cannot exceed 11 years
3) The year of manufacture cannot exceed today's year

Let me try to explain condition 1 and 2 with examples to clarify.

1) The year is 2011. A person wishes to insure his car this year (2011), his vehicle year of manufacture cannot be less than 2004 (2011-8 years). i.e. It can be anywhere in the range of 2004 to 2011, but below 2004 an error will occur prompting the user that we cannot insure this vehicle.
2) The year is 2011. A person has insured his car with us in 2009, his vehicle year of manufacture cannot be less than 2001 (2011-11 years). i.e. It can be anywhere in the range of 2001 to 2011, but below 2001 an error will occur prompting the user that we cannot insure this vehicle.

I've been looking at this for a number of hours and I feel it's only a simple formula to input in the data validation but I just can't figure out.
I've attached Book 1.xlsx for reference
 

A:Solved: Data validation with formula

Read other 7 answers
RELEVANCY SCORE 87.6

I am trying to create a validation list in Excel, but the problem is that it won't let me do this because the dropdown I want to create is on a separate "sheet" than the data that it needs to contain. I am doing it this way, because there is more than one range I need to pull data from.

Is there any way to do this? I would prefer not to have to copy the data from the first sheet to a "hidden column" on the second sheet, but if there's no other way, then I guess I am stuck.

Please help!
 

A:Solved: Excel validation list

Read other 8 answers
RELEVANCY SCORE 86

Hi all,

I was wondering. I have an Excel sheet in which people can select some few things.
For example Type, Gender.

Depending on what's selected here, another validation list is filled in with values (male gives other values then female).

Now I would like that when a Type and Gender are selected, the first item from the new to select values is displayed automatically.

So is it possible that a validation list automatically displays the first item instead of staying blank?

I've searched the internet for a solution, but I can't find any.

Thanks in advance.
 

A:Solved: excel validation list autodisplay first item

Read other 16 answers
RELEVANCY SCORE 86

Hi

I have an Excel 2003 pivot table based on an ODBC query linked to a Sage 200 (accounting software) database which works fine, but I had to add two formulated columns to the data returned by the query because the logic in them is too complex for me to replicate in the query design editor.

When I refresh the query the formulae in the two columns are not automatically copied down past the row which represented the end of file when it was last saved and so have to be copied down manually and I then also have to go into the Pivot Table Wizard to expand the data range.

Although my VBA is (very) rusty I could probably write a few lines of code to automate these two steps but is there a way to do it without VBA?

Thanks

Garry
 

A:Solved: Excel Pivot Table (based on query with formula)

Read other 16 answers
RELEVANCY SCORE 84.8

I am trying to write a macro to run through and change the data validation. I have a column and the data validation for a given cell is in the same column. Data validation does not carry out in autofill like formulas. I can not get the validation formula to work with a variable. ="=$columnvariable$number1:$columbvariable$number2" where column variable is the column letter and number 1 is a row number and number 2 is the other row number. Is there a way to make this work where i can increment the column letter and the numbers?
 

A:Solved: Excel Data Validation

Read other 16 answers
RELEVANCY SCORE 84.8

I have a spreadsheet for work that is basically free form. But in one column I have an ending line that reads "vehicle inspection, unloading" and a few more words. My former spreadsheet allowed me to select whatever line I wanted in that column in which to place the test by selecting it from the drop down. For some reason now it will do that but it will not let me type anything in any line. So, I can select that phrase from the drop down but I can't type any other info into any line with out getting an error. Kind of like it is either that phrase or nothing. How do I keep my drop down info on there and use it in any line I want while still being able to type anything into any line not occupied by the drop down text? Does that make sense? I set this up through data - data validation. I suspect there is just a setting in there I am not checking or unchecking properly.
 

A:Solved: Excel Data Validation

on the validation screen - you should see a "Error Alert" tab
on there will be a tick box
"show error alert after invalid data is entered "

untick that
 

Read other 3 answers
RELEVANCY SCORE 84

Hi,

Ok, here´s my problem. I have a report that I need to make several times a month. To do that I need to get data from a certain BI application that generates huge amounts of data. After that I need to make a pivot from that data and then select different fields from the fields list. Occasionally, I need to use different fields and it is a pain in the a** to find the correct fields, since the BI fields (there are a LOT) are not alphabetized and I can´t change the order of columns there.

So, is there a way to order the fields in the field list in any way?

Thanks, in advance!
 

A:Is it possible to rearrange data in excel 2007 pivot field list

Hi, welcome to the forum.

If you select the entire sheet before you create your pivot tabel you can sort from left to right
That way the header row, which contains the fieldnames will de alphabetically sorted.

Try it with the Data Sort and options.

I just tried it under Office 2003 and 2007 adn 2010 have the same options.
 

Read other 3 answers
RELEVANCY SCORE 83.6

When i export my inventory from my DB into an excel file, my trailer brands are listed as they are in my DB (of course). But when i bulk upload them with XML feed to trailer inventory sites (HTW.com & HTT.com for our example here). My inventory is listed on their site, but it's not searchable because the brand search is a drop down menu on these sites.

Say i goto HTW.com and do a search for 'Blue Ribbon' trailers none of my blue ribbon trailers show up because all of mine are listed as 'BLUE RIBBON TRAILERS, LTD' and the site is searching for 'Blue Ribbon' and although they are the same trailer brand HTW.com does not see them.

What i'm looking for is maybe a Macro or even formula that once i tell it what site i'm working on, will go through my list and replace all my spellings with the proper ones for that site...

Attached is an XLS file with 3 columns, the first is my list as is from my DB, the other 2 columns are the proper spelling for each corresponding site...

Excel's find and replace feature is a VERY crude fix to this, but since it's so time consuming it's NOT a viable solution.

Any takers??

Slurpee? Zack? Anyone??
 

A:Solved: Excel -Macro or Formula? Replace my data with similar required data - sample

for the record, a complete solution would be GREAT, but not necessary...
I just need someone to point me in the right direction and maybe a small working sample of like 5 brands and i'll expand the list from there...

cheers
 

Read other 3 answers
RELEVANCY SCORE 82.8

hello everybody. its been a while since i have been on this site..anyway this is the situation:

i would like the number in cell A1 to increase by 1 everytime there is data entered in cell A2 and A3( data must be entered in both, reject and do not increment if data only entered in A2 or A3). any way to do this in excel?

thanks all!
 

A:Solved: Excel 2003 - how to perform some data validation..

Read other 16 answers
RELEVANCY SCORE 82.4

I'm working on a macro that does several things with budget reports. I have zero knowledge of Visual Basic, just recorded my macro and played with the code.
It creates a line called "total expenses" then, six cells to the right, I need it to add the value in a row titled "total coaches" and another value in a row called "total directors". these values are 6 cells to the right of the titles. so what I had tried to do was search for the "total coaches", move over six cells, take that cell's location and add it to the "total directors" value with the same process. however, the macro just used RC to record the cell's position. the position of the value needs to be dynamic as the reports will change.

I can get it to create the "total expenses" line and make the cell six to the right of it active. I just can't get the formula to be dynamic based on a text search.

here's the relevant code:
ActiveCell.FormulaR1C1 = "Total Expenses"
Range("A247").Select
Cells.Find(What:="Total Expenses", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
ActiveCell.Offset(6, 0).FormulaR1C1 = "=(R[-147]C+R[-1]C)"
With Selection.Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFo... Read more

A:Solved: Dynamic Excel 2007 Macro

This should do what you want, but I don't have an example to look at so I just made up my own example

You can adjust the column numbers to match yours. Note that I made some additions. I think in your recorded macro you went to Row 247 at the start, which I am assuming is the last row...I added code to find it for you.


Code:
Public Sub test()


Dim Coach As Integer
Dim Direct As Integer
Dim totalExp As Integer

Dim ws As Worksheet

Set ws = ActiveSheet

On Error GoTo err_Hand


' finds the last row in column 1
lrow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

'fills in cell 2 rows below last empty
Cells(lrow + 2, 1) = "Total Expenses"

totalExp = lrow + 2


Cells.Find(What:="total coaches", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True).Activate

Coach = ActiveCell.Row


Cells.Find(What:="total directors", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True).Activate

Direct = ActiveCell.Row


Cells(totalExp, 2) = Cells(Coach, 6) + Cells(Direct, 6)


err_Hand:
' 91 is the error number when the search value is not found
If Err.Number = 91 Then
MsgBox "Search value not found"
Exit Sub

End If

End Sub

 

Read other 3 answers
RELEVANCY SCORE 82.4

I'm fighting with dynamic charting in Excel 2007. I just about found my way in 2003 but having to learn it again now, hehe I've created my ranges (which I can see just fine in the Name Manager) and if I go to my chart and put the name of the range in the Chart Data Range, then it works just fine. However, if I close the Select Data Source window and then reopen it, the dynamic range I put in has been replaced with absolute values. How can I make it "stick" so that my chart data will remain dynamic? Am I missing something really obvious here?
 

A:Solved: Dynamic charting in Excel 2007

Think I've been able to answer my own question through further digging in the past 24 hours. Just in case somebody else has this issue:
Inputting the named ranges in just the Chart Data Range field under Select Data Source isn't sufficient; you have to click on the series and put the named ranges in there individually. The range in Chart Data Range will show absolute values, but will nonetheless update according to the dynamic ranges.
 

Read other 1 answers
RELEVANCY SCORE 82

So far i have read you can't do this. So i'm wondering how you achieve this the correct way. I work for a gas field company and the database i am redesigning has a lot of fields that need validation, like well site names, company names etc. The person who built the original database had no validation at all, and you could find the word Production spelled 12 different way sometimes. Made it very hard to keep data readable.

So the part im working on now is the well sites list. I have a table called Well Sites. I have a main work orders table where they must choose a well site which is a lookup of the well sites table. This is fine and dandy, however. If they choose not to use a name in the drop down list, they can simply type anything they want, which is what i want to avoid. I want them to ONLY be able to type names that exist in the well sites table.

I was hoping it would be as easy as setting up a validation rule but from what google is telling me that is not even possible. So how do i control what user's enter. I do NOT want to type in a list of 600 well site names using OR for a validation rule. That is unmanagable, but i need to restrict what they can type so we don't get mis-spellings and mis-labeled wells.

Now i understand some things but some things i've just never done before prior to now. If i setup a one to many relationship between two tables then it seems to work like i want. Only values that exist in the other table can be entered into... Read more

A:Field Validation based on another tables data

Read other 6 answers
RELEVANCY SCORE 82

Hi,
I'm working on an Excel project of mine and wanted to use VBA to create a Data Validation List in a merged cell.
The strange thing is that using VBA for some reason the list does not show as a drop down list but as a long string
The string is the 26 letters of the alphabet
A;B;C;D;E;F;G;H;I;J;K;L;M;N;O;P;Q;R;S;T;U;V;W;X;Y;Z
Click to expand...

Since I was doubting my own VBA code I decided to record a macro and added all the 26 letters of the alphabet
The resulting code:

Code:

Sub DVApply()
' DVApply Macro
Sheets("DataForm").Select
ActiveSheet.Unprotect
Range("B2:C2").Select
ActiveCell.SpecialCells(xlCellTypeSameValidation).Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="A;B;C;D;E;F;G;H;I;J;K;L;M;N;O;P;Q;R;S;T;U;V;W;X;Y;Z"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Range("D5").Select
End Sub

Now here is the thing. The moment I record the macro the list shows up nicely as a dropdown list, but if I run the macro the new list will show up again a a single string and not in a list unless I open the Data Validation Dialog and click Apply and close it.
I&#... Read more

A:Solved: Excel (2010) Data Validation using VBA not working as expected

Whatever the problem is I wrote another routine and it works now.
I've thrown every range at it and it (still) works.
I'ts still strange why even the recorded macro does not work when you run it again afterwards.
Let it be, one more of Microsoft's deep secrets
At least this post had 15 views, but I'm marking it solved.
 

Read other 1 answers
RELEVANCY SCORE 81.6

I need help for a formula for cell "B9". The water bill for this house comes to $21.65.
With the variables of the house being outside the city limits plus the 5 units of water over the minimum, plus the sales tax, I'm having much trouble getting a formula to agree w/the utility co. I do know that their charges are correct as I have 3 or 4 formulas addded that will agree w/them. I just can't get one complete formula for cell B9. Any help will be greatly appreciated.
 

A:Solved: Excel 2007 Formula Help

Read other 10 answers
RELEVANCY SCORE 81.6

Hello.

Please take a look at attached simulated worksheet.

I need help with a formula for Column E.
COL A - Can contain any number of rows with data. Its data are always in the format of "text (#.#)."
COL B - Uses a formula to extract just the text from COL A.
COL C - Uses a formula to extract just the digits and decimal from COL A.
COL D - Contains a finite number of rows (78 to be exact).
I need E1 to average the data in C:C where the corresponding B:B=D1.

Similarly, should average the data in C:C where the corresponding B:B=D2, etc.

So, with the data above E1 would equal 5.8, etc.

Again, it's important to remember that there's no limit to the number of rows containing data for A, B, and C, but D and E have a fixed number of rows with data.

Here's the formula I tried. I can't figure out why it isn't working (it leaves E:E blank). What would work better?

=IFERROR(SUMPRODUCT((B:B=D3)*(C:C))/COUNTIF(B:B,D),"")

I sure hope this is clear (it was difficult to explain!). Thanks for any help you can provide, and feel free to ask for clarification.

jb
 

A:Solved: Excel 2007 formula help

Read other 9 answers
RELEVANCY SCORE 81.6

Ok here is what I have and what I'm asking for. I hope that I have explained it all and asking the right questions.

I have the QC spreadsheet and the Inspector Spread sheet that I will be working on here.
There is a “Q Chart” spreadsheet that has a chart for all the corrections that are filtered from the QC spreadsheet.

The “I chart” is going to be able to, once the code is made, to be able to filter the Inspectors name and then selecting the month in B3:B23 will auto populate the corrections for that inspector for that date and total them out accordingly.

The Inspecor!C25:S25 will populate the “I Chart” once filtered out per inspector.

QC Spreadsheet:
K3: Will have a Due Date. It will always be the 13th of the month.
• ]This date will be typed in manually.
L3: Will have a name First name with the letter then last name always.
• The names will be in a Data Validation list.
R:AG ; Will have errors put into those range of cells.
• These cells have Conditional formatting.
AJ3:11 ; Have the total for that row of errors.
R14:AG14 ; Will have the total for that column when filtered.
• That’s all for QC Spreadsheet.

Inspector Spreadsheet:
A3:23 ; Will have inspector names as the same as QCL3.
B3:23 ; Will have dates that you can pick from a Data Validation list.
C3 Will have this code: =IF(B3=AG4,COUNTIFS(QC!$L3:L13,"T. Daniel",QC!$K3:K13,"01/13/2013"),IF(B3=AG5,COUNTIFS(QC!$L3:L13,"T. Daniel",QC!$K3:K13,"02/13/2013&qu... Read more

A:Solved: Formula Help Please, Excel 2007

Read other 8 answers
RELEVANCY SCORE 81.6

If i have a Formula in A1 that says =IF(B1>0,C1,"")

Now lets say that when it calculates in A1 what ever is in B1 when C1 is greater than 0.

Now A1 has the number .8 from the calculation, but it needs to be at least 2. (No matter what the number is it always has to be at least 2.

How do i have the formula state that in A1 if there is a number under 2 that it will replace that number with 2. So if after you put in a number to B1 and it gives you anything under the number 2 in A1 it will replace that number and have a 2 there.

So the number in A1 no matter what the calculation will never go below the number 2.

Thanks in advance for the help.
 

A:Solved: Help with Formula's Excel 2007

Read other 7 answers
RELEVANCY SCORE 81.6

Hi All

I am attempting to make an annual leave spreadsheet to record annual leave requests of employees. What i want it to do is to calculate the amount of days leave requested without including the weekends. for example.....

Cell A1 contains 11/12/09
Cell B1 contains 18/12/09

I want Cell C1 to show the total number of days leave between those dates. The simple answer would b =sum(B1-A1) which would give the value of 7. This would be incorrect though as this includes Saturday and Sunday, the value i need is 5. How can i get the calculation to not include the weekend (and bank holidays if possible) ?

I hope that describes what i am trying to do clear enough. If not please let me know.

Many Thanks
 

A:Solved: Excel 2007 formula help

Read other 16 answers
RELEVANCY SCORE 81.6

I am trying to create a worksheet that will pull information from other worksheets in the same workbook. There are about 100 worksheets and Column 1 has the worksheet number, column 2 lists the item number from the specified sheet. I want a formula that says if A1 is equal to "Sheet***" the search that sheet for item # listed in B1 and return the value of C1.

I can do a lookup but I can not find how to have it look at each sheet and not just a certain one that I chose.

Help please
 

A:Solved: Excel 2007 Formula HELP!!!

Read other 9 answers
RELEVANCY SCORE 81.6

I need a formula to conditionally format dates in a column to indicate that if the date is anything up to 7 days preceding the current date it should highlight those cells in green. Please help.
 

A:Solved: Excel 2007 formula

Read other 10 answers
RELEVANCY SCORE 80.4

I have a Excel 2007 workbook that list products, quantity, and sold. Is there a way to have a formula or some type of code to determine the top ten best selling products?
 

A:Solved: Excel 2007 Formula/Code

Read other 16 answers
RELEVANCY SCORE 80.4

I have placed this line of code

Code:
Range("D49").FormulaHidden = True
in the ThisWorkbook_Event.

When I run the code, I am getting the error message "Application-defined or object-defined error".

The worksheet is not protect.

What could be causing the error to occur?
 

A:Solved: Excel 2007 Hide Formula

The data was in a merged cell.

Changed the code to

Code:
Range("D49:E49").FormulaHidden = True
The error message went a way.
 

Read other 1 answers
RELEVANCY SCORE 80.4

I have a user who does know how to work with office. (Sorry to start out defending her, but when you hear the issue you will understand why) She saved a workbook with formula's in it. When she opened up her spreadsheet the formula was gone, but the values were there. It wasn't a special 2007 formula, she didn't save it at a lower office version, and it was on her computer not the network; so no one could have opened it and made any changes. It is a protected sheet, the formulas are not hidden, and she isn't a veg head so she didn't copy and paste over her formulas with the values only. Now, she is not the only one in her department to have this happen to her. Her boss had the same issue about a month ago. Both are intelligent people who do know how to save files. But to be safe I did look for any txt files, csv files or even any files in the user temp folder to see if they might have been in there instead of the My Document folder.

So here are my questions for you all. Is there some key combination that she/ they might have hit to do this? Has anyone else experienced this issue? Please don't point fingers at the user. I know people can and do make mistakes, but I don't think that is the case this time. I really wish it was then I could mark it closed and go about my merry little way. Thanks in advance for any and all input everyone!
 

A:Solved: Excel 2007 formula vanishes

Read other 9 answers
RELEVANCY SCORE 80.4

I am trying to make a formula saying:

=if'',''((A1="Red","QA","CA")

Meaning if A1 is blank then leave B1 blank but it A has text saying "Red" - B1 should say QA - any other color in A1, B1 should say CA.

I keep getting error

Can someone help?
 

A:Solved: Excel 2007 Null formula

Hi there,

I think you're looking for a nested IF() function...

=IF(LEN(A1)=0,"",IF(A1="Red","QA","CA"))

HTH
 

Read other 1 answers
RELEVANCY SCORE 80.4

Surely I am missing something...

In a spreadsheet, cell H1 contains the number 150, this number will change frequently. Is there a way to get column A to automatically(sequentially 1-150) number based on the number that is manually input in cell H1? I know of the <Edit> <fill> <series> <linear> option, but am in search of a formula that will automatically number instead of this option.
 

A:Solved: Assistance with Excel 2007 Formula

Read other 16 answers
RELEVANCY SCORE 80.4

Is there any way to shorten this formula?

=SUM('Z:\Pipe_Inspection\Data\Planner\EXINSP-INSLIN-THICK\Data Sheet\04-APR\WAC\[A3600ISO-08 DATA SHEET.xlsx]Normal'!$L$10,'Z:\Pipe_Inspection\Data\Planner\EXINSP-INSLIN-THICK\Data Sheet\04-APR\WAC\[A3600ISO-08 DATA SHEET.xlsx]Normal'!$L$11,'Z:\Pipe_Inspection\Data\Planner\EXINSP-INSLIN-THICK\Data Sheet\04-APR\WAC\[A3600ISO-08 DATA SHEET.xlsx]Normal'!$L$12,'Z:\Pipe_Inspection\Data\Planner\EXINSP-INSLIN-THICK\Data Sheet\04-APR\WAC\[A3600ISO-08 DATA SHEET.xlsx]Normal'!$L$13,'Z:\Pipe_Inspection\Data\Planner\EXINSP-INSLIN-THICK\Data Sheet\04-APR\WAC\[A3600ISO-08 DATA SHEET.xlsx]Normal'!$L$17,'Z:\Pipe_Inspection\Data\Planner\EXINSP-INSLIN-THICK\Data Sheet\04-APR\WAC\[A3600ISO-08 DATA SHEET.xlsx]Normal'!$L$18,'Z:\Pipe_Inspection\Data\Planner\EXINSP-INSLIN-THICK\Data Sheet\04-APR\WAC\[A3600ISO-08 DATA SHEET.xlsx]Normal'!$L$24,'Z:\Pipe_Inspection\Data\Planner\EXINSP-INSLIN-THICK\Data Sheet\04-APR\WAC\[A3600ISO-08 DATA SHEET.xlsx]Normal'!$L$27,'Z:\Pipe_Inspection\Data\Planner\EXINSP-INSLIN-THICK\Data Sheet\04-APR\WAC\[A3600ISO-08 DATA SHEET.xlsx]Normal'!$D$17,'Z:\Pipe_Inspection\Data\Planner\EXINSP-INSLIN-THICK\Data Sheet\04-APR\WAC\[A3600ISO-08 DATA SHEET.xlsx]Normal'!$D$18)
 

A:Solved: Shorten formula? Excel 2007

yes
you could use a namerange to use
Z:\Pipe_Inspection\Data\Planner\EXINSP-INSLIN-THICK\Data Sheet\04-APR\WAC\[A3600ISO-08 DATA SHEET.xlsx]Normal'!

i think you can - but cannot test

have a read here
http://www.ozgrid.com/Excel/named-formulas.htm
and
http://office.microsoft.com/en-gb/excel-help/define-and-use-names-in-formulas-HA010147120.aspx
 

Read other 2 answers
RELEVANCY SCORE 80.4

In the attached spreadsheet, which shows net pay after taxes, I want to do the reverse - I want to input a net salary, and have it tell me what required gross salary would be.

I am completely stumped as to how to go about this, any help would be greatly appreciated.
 

A:Solved: Reversing an Excel 2007 formula

Read other 15 answers
RELEVANCY SCORE 79.6

I would like to know if there is a formula that you can use to read if another cell has a color in another excel document.

Example:

Sheet1
i have A1 that is normaly Grey (red:216,green:216,blue:216)
When im done with A1 its green (Red:146,green:208,blue:80)
Normaly it takes a few weeks before im down with that cell

Sheet2
A1 I have 50% and 100% to tell me if the A1 cell on sheet1 is finished or not.

I would like to know if there is a formula i can put into Sheet2 A1 to look at Sheet1 A1 and if its grey to put 50% and if its green to put 100%

Thanks for your help. Not sure if this can be done or not i tried to look on here and google it with out much luck.
 

A:Solved: Formula read a color? Excel 2007

Read other 7 answers
RELEVANCY SCORE 79.6

Hi,

I am having trouble getting results from a formula that is trying to return the sum of numerous values in a field once 2 conditions are met. I would say I have between novice and intermediate experience with excel formulas so I hope I can explain it clearly.

Worksheet 1: results

Worksheet 2: data
Column 1 (month): April, April, April, May, June, June, July, July, etc...
Column 2 (code): 4000, 6500, 4902, 4902, 3002, 4000, etc...
Column 3 (amount): £0, £0, £3598, £725, £0, £2212, £0, etc...

I want to get the sum of the amounts in Column 3 for the code in Column 2 in a specific month. For example, how much was spent in April for code 4902?

I am working with this formula
=SUM(IF(Worksheet2!$A$6:$A$245="April",IF(Worksheet2!$B$6:$B$246=4902,Worksheet2!$C$6:$C$246,0),0))

However, this is returning errors.

Can anyone help solve this one, perhaps with a simpler formula?

Thanks
 

A:Solved: Excel 2007 - Conditional SUM formula problem

Read other 16 answers
RELEVANCY SCORE 79.6

I have Book1

Sheet1:
Column A has unit numbers
Column M has Names
Column N has numbers

A1:A41 is 40 unit numbers
M2 has =if(A2="A",sheet3!D1)
N2 has =if(M2="irvin",Sheet3!E1)

Sheet2:
Column A has all the unit numbers
This sheet will not be of any use in this question

Sheet3:
Column A has all the unit numbers
Column D has all the names for that unit
Column E has the number for that name

What i would like to do:
Put any unit number A:AN into A2:A1000 and Column M auto populate the name for that unit.
Column N i want the number for that name

Example:
When you put unit# A into A2 it will say in M2 Irvin, and N2 will say 1234
if you put unit# T into A3 it will say in M3 Josua, and N3 will say 2145
and so on and so on for what ever unit# i put in all the way down Column A1:A1000

Questions or comments let me know. And thanks in advance for any and all help.
Attached is the Example Book1.
 

A:Solved: Formula Help, multi sheet, Excel 2007

Read other 8 answers
RELEVANCY SCORE 78.8

I set up an Excel Spread Sheet to add one year to a date I enter in previous column. Only problem is that when previous column is empty formula calculates a date (12/31/00) anyway rather than leave the column blank until a date is entered in previous column. I hope I am making myself understood.
gb
 

A:Solved: Excel Formula Fills Cells Without Data

Read other 14 answers
RELEVANCY SCORE 78.8

Is there a way to protect a cell so that its content cannot be changed/moved.

Also, for the same cell, which has a formula, is there a way to hide the formula?

Thanks
 

A:Solved: Excel 2007 - Hide formula, protect cells

Select the cell (cells) you wanna protect, rgiht click, Format cells, go to the protection tab, and check both looked and hidded, click OK
After that go to Review Tab, and select Protect Sheet, Type a password if you wanna and save file
Close and reopen it!
 

Read other 3 answers
RELEVANCY SCORE 78

I am working with Office 2007
From the oracle table I wrote a query to get the Region and also the corresponding areas.
This is loaded as a master sheet in excel sheet 1
Then with the other query I got all the values that are to be displayed ( This is Child sheet)
Now in the child work sheet the user is allowed to change the Region. for this I used the data validation list to display the values as a list.
=Sheet2!A:A (Assuming sheet2 as the master work sheet and column a has the values of all the areas)
but Now I heed to filter the list in such a way that only the values corresponding to Region need to be displayed as areas.

Master Table

Region Area
--------------
SIN area1
SIN area2
SIN area3
NYC area4
NYC area5
IND area6
IND area7

Child Table

Position Region Area

NE SIN area1
NW NYC area4

As shown above the area needs to be based on the region in child table, in the same wise if the region is changed the list items need to also be listed in accordance with it.
Please Note :Macros are not allowed in the excel

Regards

Raghul
 

A:Data Validation List with Filter

why not attach the excel workbook with the 2 sheets, it's easier to visualize
 

Read other 1 answers
RELEVANCY SCORE 78

Hi everyone,
I’ve created a list (insert\ name\ define) then data, validation…..
the list contains more that 200 entries. now using it to choose one of the entries can take much longer that typing it.
Is there a way to type only the first 2 or 3 letters to get to my choices faster instead of scrolling the whole list?
Thank you
 

A:Data validation- choosing from list

Read other 6 answers
RELEVANCY SCORE 78

Trying to pull the word "set" from a cell that has more words and characters than just "set. I also need it to account for variables such as lower case/upper case (i.e. SET, SeT, SEt, seT). The formula needs to pull the word "set", however it looks, into the adjacent cell, this way I can use the Sort tool to organize all cells.

I tried the INDEX formula, but it only looks for the word "set" without distinguishing it from the other characters.

I don't think the LEFT/RIGHT/MID functions will work, as the character count is going to be a variable from each cell (my judgment on these formulas is really shallow due to my lack of time to completely understand them).

Take a look at the attached spreadsheet and you'll see where I'm going.

Thanks in advance
 

A:Solved: Excel 2007 - Formula for Extracting Specific Characters from Cell

Read other 9 answers