Re: Dynamic named range with custom start and end cell, without using index function
You are correct, that does work.
I read online that index isn't the best formula to use as it doenst adapt if you add more columns/ rows. This is why I was trying offset.
Is this true?
Re: Dynamic named range with custom start and end cell, without using index function
things have moved on - I have a formula that works in my main sheet, it selects the right range, but when I try to chart it, it only charts the first cell!!!
Re: Dynamic named range with custom start and end cell, without using index function
this works, for the number range
=OFFSET(Sheet1!$C$7,MATCH(Sheet1!$A$1,Sheet1!$C:$C,0)-7,1,COUNTA(INDIRECT("Sheet1!$C$7:$C"&MATCH(Sheet1!$A$2,Sheet1!$C:$C,0)-COUNTA(INDIRECT("Sheet1!$C$7:$C"&MATCH(Sheet1!$A$1,Sheet1!$C:$C,0)-1,1)))))
Re: Dynamic named range with custom start and end cell, without using index function
Thanks, I just tried F9 testing the formula you gave, it doesn't result in the correct range
the chart also doesn't match dates
I am trying to figure it out
Re: Dynamic named range with custom start and end cell, without using index function
Thanks so much.
this works, but I dont understand it. Do you have something that would take me through how this works?
I appreciate the help very much, thank you.
Re: Dynamic named range with custom start and end cell, without using index function
Does anyone have any ideas? I am desperate here...appreciate any help. Thanks in advance
I want to create a dynamic named range, as in the spreadsheet attached. I want to create it in a much larger sheet than this.
The issue I have is I have made the dynamic ranges date1 and datesheet which are dynamic based on the start and end dates in A1 and A2. The ranges are then charted.
i have used index for this - the issue with index is if I enter anything in the top row of column c or d (as i will be in the actual sheet), it gives an error
so, the question is, is there a way to do a dynamic named range with custom start and end using something other than index?
Re: Macro vlookup and return multiple values, in alphabetical order
This works, but it doesn't keep the words with * before them always at the end of the list.
If i add Sam to the list, with 2 in column A, it goes to the back despite not having a star
Re: Macro vlookup and return multiple values, in alphabetical order
Thanks so much. Really appreciate the help.
Re: Macro vlookup and return multiple values, in alphabetical order
I cant do it on the spreadsheet hence the thread in the first place.
I want Bill and Jonathan to be green and the other names in E1 to be red. This is based on the numbers next to each name in column C.
I hope that is simple for you to understand.
Thanks for the help.
Re: Macro vlookup and return multiple values, in alphabetical order
No problem
here it is
The formula returns those with "2" in column A, in alphabetical order, with those with a * after those without.
That was the first part, which was kindly solved for me.
Now i have added numbers in column C.
I now want, in addition to the above, those with "1" in forum.ozgrid.com/index.php?attachment/59427/column C to be red text, those with "2" in column C to be green text, and so on.
Re: Macro vlookup and return multiple values, in alphabetical order
Anyone?
Re: Macro vlookup and return multiple values, in alphabetical order
Thank you, this works pefectly
What, if now i want to add another 'layer' whereby I want all of the names with the 2, which your formula has placed in a single cell for me, to be colour colded (multiple colour text in one cell).
Imagine there is another column that has another number per name, this time 1-5. I would want all the 2's in one cell, but the 2's with a 1 in column C to be red, 2 in column C to be green, etc
I have the following list
A B
2 David
2 Janice
3 Sam
2 Jonathan*
3 Sally*
2 Bill
2 Paul*
In a new cell, i want the value to read all the names with 2 in column A, in alphabetical order, followed by all the names with 2 in Column A but with * after them, also in alphabetical order. So essentially the result should be
"Bill, David, Janice, Jonathan*, Paul*"
Please help
In the attached sheet, I want a bar chart that has the January gross and net (vertical axis) for each manager (horizontal axis). I want to overlay the december numbers as a dotted bar with no fill on top of the January ones so we can see how it changed month on month. Is this doable?
Thanks
Re: RankIF - what formula do i need
Quote from NBVC;700822Does this formula work in G4, copied down?
=IF(OR(K4="",L4=""),"",COUNTIFS($K$4:$K$58,K4,$L$4:$L$58,">"&L4)+1)
No, it gives some duplicate values when it should not.
I have used the small and large functions instead as I mentioned above - The issue is now solved.
Thanks
Re: RankIF - what formula do i need
Maybe I can leave as is and get my top 10 and bottom ten using the SMALL and LARGE functions
Re: RankIF - what formula do i need
Still doesn't work.
See in attachment. The Day Rank column now has a blank where there is no exposure data - exactly what i want. BUT it still seems to include it in the rank because there are no 1, 2, 3, 4 etc in my rank...the blanks ahve been included as those values
you see what I mean?
Re: RankIF - what formula do i need
OK I have a new problem with this. I want the formula to exclude rows that have no return from the rank. So even if you have long or short, but no actual number to rank, I want it to be ignored. As it stands, I have around 50 items that have long / short but no actual return number, and they are being included in the rank so messing up my vlookups which bring back the top ten and bottom ten
Re: RankIF - what formula do i need
if they are the same number then they are of equal value to me, but the ranking doenst matter either way around.
Shouldnt be an issue as I have so much data it is unlikely...but just to make sure I wanted to see if it is doable.
