displaying the value of a reference once before filling down. I want it to display only once and not display anymore until a different value is included in the fill down. The rinse and repeat.
View 3 RepliesI am trying to accomplish a task that uses the VLOOKUP function to retrieve data from another worksheet. The problem I am having is that it will only return one row of data.
In the formula below, there are multiple rows that contain the $C$4 reference.
I would love to figure out how to return all the rows that contain that
C$4$ reference.
=VLOOKUP($C$4,'Hot Zone Customers'!$C$2:T561,14,0)
can Excel do a double if formula by looking at cells and not fixed type info.
EG: I have a % achieved and $ bonus to pay: however the % achieved & the $ bonus to pay information in the cell will change each month
So... I want the formula to say
=IF (look for cell A1 (which has %achieved) and if found use A2 ($bonus) to give me an answer... otherwise false
The second dillema I have is that A1 has 3 ranges from 100-150, 150-200 & 200-300 to calculate with a set % in A2
I am working in a receiving room at a condo complex and am trying to facilitate how we handle and distribute the packages.
What I have in the first sheet of my would-be file is the unit numbers in a 28X12 array (12 units on 28 floors). I would like to make the array conditionally formatted to be highlighted in red to show that there is a package in for the unit in question. On the next sheet, I am putting information such as carrier, unit number and tracking number for each of the packages we receive. I want the unit number on the first sheet to immediately be red when the unit number is typed in the second sheet. When doing conditional formatting, it is easy to do this, by making the condition that the unit in the array will be highlighted when the unit is seen in the unit column in the second sheet.
My question is this: is it possible to somehow duplicate the references for the entire column that contains the unit numbers. I want a whole column to be the reference for conditional formatting of an array, so that I would be easily able to see who has a package in the receiving room, and who does not.
I recently created a userform. I want the data to be placed into the leftheader, centerheader, and rightheader of the document after the user fills in the required fields. I have made the userform, and I can make the code for it to place one item on my userform in the left, center, or right headers. However, when I try to place more than one item in any of the headers excel deletes the first item and overwrites it with the item that is listed last in the VBA code for that respective header. I am new to VBA (this is actually my first program) so I do not know how to make the first value stay there and simply have the next value placed on the line below it.
View 13 Replies View RelatedI have a question dealing with a variable column and cell reference I'm using. To make it simple here's what I have done. On the FRUIT tab I have the following table in location BU132 - BV161
Name Column
Apples A
Pears B
Corn 'VEGGIE'!A
Peas 'VEGGIE'!B
I'm using the following formula to get the location of a cell on both the FRUIT and VEGGIE tab that has the value.
=INDIRECT(VLOOKUP(CH61,$BU$132:$BV$161,2,FALSE)&CN61)
{where CH61-CH100 contains fruits or vegetables and where CN61 is a table that has the column reference which goes from 1 to 40)
This formula works great as long as the item is on the FRUIT tab. I can just drag the formula down the entire column and get my values for all the FRUITS listed in CH61-CH100. But, it doesn't work for any items in CH which are on the VEGGIE Tab. What syntax do I need to use in the cell next to Corn and Peas above to get this to work right?
I am working with a formula that has multiple embedded IFs and therefore references the same cell multiple times. However I am also copying it between workbooks for different companies so some of the information changes which column it is in. For example, there is a formula in AA5 that has 3 references to R4 and 4 to R5 and I need all of the R4 references to be T4 and all of the R5 references to be T5.
I can't do a "find and replace" because the formula in Y5 still needs to reference R4 and R5.
I have been clicking on R4 and dragging it 3 times to T4 (and the same for R5 to T5). It just seems like there should be a way to move all of the R4 references at one time to T4.
I want a total of column F based on criteria in column D and column K from the worksheet named in cell C9. I have put in the indirect function in the first part but am messing up the syntax in the second and third part of the formula. "Indirect(C9&" should be where 'Oct2009' is shouldn't it. Where am I going wrong with the following formula?
SUMIFS(INDIRECT(C9&"!$F$2:$F$11000"),'Oct2009'!$D$2:$D$11000,$C$1,'Oct2009'!$K$2:$K$11000,"YOKO")
I have a number of statements within the Sheet Event Code (Excel 2007). Three times lately I have added a column and had to go back into the code and find all of the references that needed changing to reflect the new column.
I have been working on this for a couple of days and even tried EE, but to no success.
I have read that Defined Names / Constants should be used as often as possible, but even trying that, the VBA code errors out or "hangs up". Even within Bill Jalen's book (VBA and Macros 2007), there is nothing that addresses this, especially using Intersect.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
On Error GoTo mEnd
Set rng = Sheets("Log").[F14:F10000]
If Not Intersect(rng, Target) Is Nothing Then
If Target = "" Then
With Sheets("Log")
I set up formulas to count text characters in a range of cells. I'm tracking attendance and payments for a small yoga studio.
All I need to do is count "Y"s for prepaid attendance and "DI"s for drop-ins. I have the formulas working but they are absolute so inserting a row will break my sheet.
=COUNTIF(E14:Z14,"*Y*")
=COUNTIF(E11:Z11,"*DI*")
It is suppose to be that if the employee is "FT" and has worked >=4 years the return is 15. But if the employee is FT and has worked 2 years but less than 4 years then it is suppose to return 10 (these are days off) Or if the employee is FT and has worked 1 year, but less than 2 then it should return 5 days off. And all the others in the column get no days off.
I have tried to do it with structured references and with cell references I get a column of zeros!
Is there an easy way to change
='Week 1'!$D$21
='Week 1'!$D$30
='Week 1'!$D$39
='Week 2'!$D$21
='Week 2'!$D$30
='Week 2'!$D$39
to
=IF(ISERROR('Week 1'!$D$21), "", ('Week 1'!$D$21))
=IF(ISERROR('Week 1'!$D$30), "", ('Week 1'!$D$30))
=IF(ISERROR('Week 1'!$D$39), "", ('Week 1'!$D$39))
=IF(ISERROR('Week 2'!$D$21), "", ('Week 2'!$D$21))
=IF(ISERROR('Week 2'!$D$30), "", ('Week 2'!$D$30))
=IF(ISERROR('Week 2'!$D$39), "", ('Week 2'!$D$39))
Without having to change each one by hand?
There is a pattern to the numbers.
There are 5 weeks, rows 12, 21, 30, 39, 48 in columns D,I,N,S,X
i make a lot of "trending" files at work where i link a cell to a seperate workbook where all workbooks are in the same folder.
these workbooks all have the same name with the date at the end
example:
workbook 06-01-2011
workbook 06-02-2011
all the way to
workbook 12-31-2011
usually i use find replace to change the date one at a time, but doing that 365 (x how many cells i have referencing) times is redundant
i have seein INDIRECT of the cell changes but nothing for the workbook name chaning
here is what i am trying to do:
='C:File Path[WorkbookX.xls]Sheet1'!A1
where the X WorkbookX is variable (the date at end of file name)
i have a list of all workbook names or just the dates and would like to do something like:
=D1&$A$1
where D1 is the full path name
or
='C:File Path[Workbook"D1".xls]Sheet1'!A1
where D1 is the date name
using excel 07 i prefer not to use a macro for how i am doing this but if a macro is the only way then i cannot have it actually open the files to pull the data. i prefer no macro since i set it up for future dates so when the new file is created it automatically updates the reference.
my end result i usually have something like A1=referenced cell from first workbook, A2= cell from 2nd book, etc. sometimes B1 would = a different cell from first workbook and so on if i am trending multiple cells from the workbooks
I want to sum up data for two periods (4&5) from a pivot table using GETPIVOTDATA formula. I've only been successful when I manually type the period values 4 and 5 as is shown below:
=IFERROR((SUM(GETPIVOTDATA("Amount",'Transaction Pivot'!$J$3,"Period",{4,5},"Project",$A7,"Category","T&M"))),0)
However, I cannot get the formula to work when I substitute cell references for the period values 4 and 5. Therefore, the following formula does not work:
=IFERROR((SUM(GETPIVOTDATA("Amount",'Transaction Pivot'!$J$3,"Period",{E5,E6},"Project",$A7,"Category","T&M"))),0) where E5 has 4 in the cell and E6 has 5.
Is there a way to unfix multiple cell references from formulas in a range of cells at once?
View 2 Replies View RelatedUsing Excel 2013.
I have two sheets in a workbook.
Sheet 1 has the following columns on it, with the following possible values.
People (Dropdown with value between 1 and 8)
Modifier 1 (Dropdown with Text 1, Text 2, Text 3)
Modifier 2 (Dropdown with Text 1, Text 2, Text 3)
Modifier 3 (Dropdown with Text 1, Text 2, Text 3)
Modifier 4 (Dropdown with Text 1, Text 2, Text 3)
Result
Sheet 2
Contains a set of values (Distance) that correspond to the value of the People dropdown (ie. People 1 = Distance 5, People 2 = Distance 6 and a table that looks like the following:
Mod 1
Mod 2
Mod 3
Mod 4
Text 1
0
0
0
0
Text 2
2
0.5
0.25
0.25
Text 3
7
1.75
0.875
0.875
What I want to happen is that the Result field value on table one is the result of:
Distance*(Modifier 1 + Modifier 2 + Modifier 3 + Modifier 4)
For example:
On sheet on I select People = 2, Modifier 1 = Text 3, Modifier 2 = Text 1, Modifier 3 = Text 1, Modifier 4 = Text 1 which I want to result in the following formula:
6*(7 + 0 + 0 + 0)
This might be exceedingly simple, but I just cannot wrap my head around how to do it.
Is there a way to absolute reference multiple cells at one time?
View 5 Replies View RelatedI am using the dsum formula to sum some values...the formula in B2 is:
=DSUM(BaseSistemasFebrero,"vlfinf",OFFSET('Planes Entidades'!B$1,0,0,COUNTA('Planes Entidades'!B$1:B$49),1))
The Planes Entidades sheet the data is layed out like this: ....
how to code to specify a cell in which to enter data into a spreadhseet, when the heading contains only one row.
In the attached example the headings contain multiple rows with merged cells, is there any way of overcoming this so that the entries are placed in the correct cells?
I've got a matrix combining actions on the left hand side (Col A) and owners on Cols B to F. I have drop down list for each action/owner combo: N/A, OK, KO, TBD.
Owner #1
Owner #2
Owner #3
Owner #4
Owner #5
Results
Action #1
OK
TBD
N/A
N/A
N/A
OK
[code]....
I would like to create a formula in Col G that does the following:If any of the values in columns B to F equals OK, then OKIf any of the values in columns B to F equals KO, then KO I've been tinkering with multiple IFs and quite rightly got bogged down as too many criteria to take into account...
I am trying to display a value based on the date column A as it corresponds to the unique value(s) in column D.
As you can see from my example, Column D may have multiples of a single item.
I was trying to accomplish this via formulas but I am open to using code.
The Value in Column G will display 'NEW' when it is the MIN value from all of the date values in Column A and its corresponding unique group in Column D.or when there is only one unique value in Column G and Column A is = today() [ROW 15]
The Value in Column G will display 'Remove' when it is the MAX value from all of the date values in Column A and its corresponding unique group in Column D.or when there is only one unique value in Column G and Column A is today() or < today(). [ROW 16] (The value in Column A cannot be greater than current date)
How to write a code for displaying multiple data in sheet2 row wise.
I want to use IF condition with for loop.
how to plot Multiple left Y axis display in Multiple draw line graph. Already I have created Right Y axis. I want to draw 4 Line graph in one graph 3 are Left Y axis and one is Right y axis.
View 1 Replies View RelatedI've recently discovered that I enjoy making Excel spreadsheets as a tool to solve recreational puzzles. Mostly variations on crosswords and such. Honestly it probably takes much longer to organize everything into a sheet than it would to just solve on paper, but I seem to enjoy it this way more.
The Actual Question (for TL;DR Types):
Anyway, I am trying to find a way so that a group of cells can all copy the value of the other cells in the group, regardless of which cell the value is entered into. So I don't want there to be one "master cell" that all the cells are linked to. I want them all linked to each other.
Specifically, right now I am making a spreadsheet for a non-traditional style crossword puzzle. My intention was to make fields for the answers to each clue, with individual cells for each letter, as well as the crossword grid itself. Then I would link the cell in the clue answer to the cell in the grid, so that when the answer was filled it, it would be inserted in the proper place. But since this is a crossword, there is more than one clue with a letter that links to each cell. I could make it so that the cell in the crossword grid is the "master" and make the other two equal to it, but I would much rather be able to enter the letter into any of the three cells and have all three fill automatically.
I realize I am making this way more difficult than it really needs to be, but that is kind of what I enjoy about it. Finding creative ways to make the puzzles fit into a spreadsheet and making them more streamlined.
I want to display multiple results based on 2 search parameters, where by when I enter flight number and Dep. Airport it matches the trip numbers and dispalys all the rows matching all flight number and Dep. airport based on the unique trip number.
Notice: - one trip number could consist of multi flight sectors.
1) Explanation of the data :
MMM1805 = flight Number
935 = dep. time
$AS41 = registration code
[Code].....
I have a table of golfers with their scores throughout the year. I would like to display the golfer with the highest score - this i can do but my problem is when more than one golfer has the same highest score. How can i dispay the two names. Ideally in one cell but i think i can work around that. If not there will be another post to follow.
A
B
C
[Code]....
If i have a multi-column combobox, how can i show all my columns in the display window after clicking on an item in the dropdown list? At the moment, only the first field will display :s (also, preferably keeping my first field as the 'value' property!)
Please ref attached spreadhseet.
1) Is it possible to hide all cols save D & E and also all blank rows in Col D
2) I would like to have an initial If statement saying stating that if the answer to the question in D2 is Y then don't display anything else other than the info in B2 & B3.
£) the sorting seems to have gone funny - not sure what I've done here, I'd like it sorted by phase (col A)
4) where there is a blank in the cell under a question (for example D3) - this means that the same question applies and that multiple docs should be returned (e.g for Q1 D2 docs 1 & 2 display, for qu D37 docs 36 - 39 display)
Attached is an example of what I'm trying to do. I've got a master sheet and four sheets, each for a different store. Each store carries the same five items with varying prices. I've set up the master to show the lowest price of each item and have the formula set to display the minimum price. I'd like column D to show the store from which the lowest price came. It can either show the sheet name or display cell A2, as that's the store name on all sheets.
Example.xlsx‎
I have created a sheet that calculates a score. Based on that score a determination is made to do one of three things - Nothing, Peer/Manager Review, Review Required.
In addition it was requested now that I make an overall decision for "Review Required" based on multiple triggers. My thought was to create a table from outputs of each element on another sheet, and then trigger off the table. What I am stuck on is using the multiple choices to trigger the message - Below are the choices of which each or a combination of each should trigger "Review Required"
Calculate Review Required
Total Score 'Review Required
Element 1 "
Element 2 "
Element 3"
Element 4"
Element 5"
Element 6"
Element 7"
Element 8"
Element 9"
Element 10"
Element 11"
Element 12"
Element 13 "
Element 14 '20 or more = reveiw
Element 15 '20 or more = reveiw
Element 16 '20 or more =review
Element 17 'Sum 20 Total of above if less than 20 in each