LinkBack URL
About LinkBacks
This the code. It goes through the data in a2-a20 and inputs it in row b2. I need it changed to go from A6-A200 and put into H6. When I change the values it gets all wonky
Please Login or Register to view this content.
This the code. It goes through the data in a2-a20 and inputs it in row b2. I need it changed to go from A6-A200 and put into H6. When I change the values it gets all wonkyPlease Login or Register to view this content.
Try this alternative approach:
I1 can be moved to a more appropriate cell, just change reference to it in H6 accordingly... better to perform this calculation only once and refer to the result thereafter in other calcs as opposed to recalculating it in every formula in H6 onwards.Please Login or Register to view this content.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
=if(sum(if($a$6:$a$200<>"",--iserror(match($a$6:$a$200,$h$5:h5,0)))),
index($a$6:$a$200,
min(if($a$6:$a$200<>"",if(iserror(match($a$6:$a$200,$h$5:h5,0)),
row($a$6:$a$200)-row($a$6)+1)))),"")
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Originally Posted by DonkeyOte
Try this alternative approach:
I1 can be moved to a more appropriate cell, just change reference to it in H6 accordingly... better to perform this calculation only once and refer to the result thereafter in other calcs as opposed to recalculating it in every formula in H6 onwards.Please Login or Register to view this content.
That works perfect. Great work
I also need a similar list that will generate it based on a condition.
For instance if a row is highlighted the list will form for all entries in the highlighted row and before
IE Row 50 is highlighted the list will generate based on all values within the range a20-a50
You will need to elaborate in terms of what you mean by "highlighted" ... if by colour you will need to further elaborate in terms of any underlying logic that may cause row 50 to be highlighted in the first instance.
Highlighted
The user will manually highlight a row. The range will be from cell a3 including the highlighted row.
Bump is this possible
Highlighting a row is a very HUMAN approach to visualizing data, not a solid method for a program, in my opinion.
Can you consider a different method of determining the end of the a range, perhaps an entry in a cell?
For instance, you could have a cell "E1" where you enter a particular row. Let's say you enter a 9 there.
Prior to that you create a named range that will use that value as part of the named range. Click on Insert > Name > Define, enter the name you want to use, something like NameRange, and in the RefersTo: enter this formula:
=INDIRECT("Sheet1!A3:A" & Sheet1!$E$1)
You could create an additional named range "ValueRange" with the formula to do math with, too:
=INDIRECT("Sheet1!D3:D" & Sheet1!$E$1)
Now you can use those range in a formula:
=Sumproduct(--(NameRange="James"),ValueRange)
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Agreed.
But the condition that the highlighting depends upon is the results of an auction data. Which would need to be entered manually. Thats why I thought the simpliest solution would be to simply just high light the row. If you have any suggestions then I would be happy to hear that
My solution is explained in full above.
Okay let me rephrase
Cell E1 will contain a percentage i.e., 97.25
The code I am using is
What needs to happen... based on the value of cell e1. The codes range needs to be set to include all values less than and equal to e1. The row with the value equal to e1 needs to be automatically highlightedPlease Login or Register to view this content.
Thanks sorry for the confusion
E1 or ? I1
Ideally H5 and the unqiue list will be in h10:h500
Any ideas?
I don't understand where you're looking for the value (97.25%) ? Will the value in E1 ALWAYS be listed in "the" range ? If not are the values listed in order ?
A sample would invariably quicken the process in terms of finding a resolution.
The rates are already arranged in ascending order.
The value will always be listed in E1. Which will set the range
Might be an idea to post a representative sample file so we can see exactly what you mean - with a sample I'd say we can wrap this up in next to no time.
(famous last words)
Right now the range is set from $A$6:$A$200. What needs to happen is the range needs to modified based on the value of e1.Please Login or Register to view this content.
All cells are arranged in decending order by column F.
I need it so that based on the value of e1. The range will be targeted to include all values that are equal and less then e1.
So range =e1 and numbers less then e1.
Then based on the range the unique list will be created based on the values from column a.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks