Replies

Sure:
=LEFT(A1,MATCH(TRUE,CODE(MID(A1,ROW(INDIRECT(“$2:$20”)),1))<91,0))
etc.

OK, OK. Assuming JohnDoe, etc is in A1 (and you can fill these formulas down)
First name array enter:
=LEFT(A1,MATCH(TRUE,CODE(MID(A1,ROW($2:$20),1))<91,0))
2nd name, array-enter:
=MID(A1,MATCH(TRUE,CODE(MID(A1,ROW($2:$20),1))<91,0)+1,255)

OK, Maybe not most efficient, but:
If text is in A1, then
B1:=ISERROR(1*LEFT(A1,1))
C1: array-entered: =MATCH(NOT(B1),ISERROR(1*MID(A1,ROW(1:100),1)),0)
D1: array-entered: =MATCH(B1,ISERROR(1*MID(MID(A1,C1,255),ROW(1:100),1)),0)
E1: Array-entered: =MATCH(NOT(B1),ISERROR(1*MID(MID(A1,C1+D1,255),ROW(1:100),1)),0)
Solution cell 1: =LEFT(A1,C1-1)
Solution cell 2: =MID(A1,C1,D1-1)
Solution cell 3: =MID(A1,C1+D1-1,E1)
Solution cell 4: =MID(A1,C1+D1+E1-1,255)

Sorry — I don’t get it. Maybe a very precise example would help.

Your references to Range(C4,R) and Range(C7,R) confuses me — what does that refer to? There’s no such thing as range(C4,R), so what do you mean?

If you’re trying to add up prices which correspond to values not equal to a single quote, then you need this:
=SUMIF(B2:B5,””””,prices) which works fine.
If you define a name, Quote, to be =””””, then use
=SUMIF(B2:B5,””&quote,prices) it’ll also work. If you highlight from the first quote to the “e” in Quote and press F9, you’ll see “”””. I don’t see a problem here, nor do I see a bug. Using =SUMIF(B2:B5,””””””,prices) is comparing B2:B5 to 2 quotes, not one.

Does the data change as in just the values or are there more values? If just the values change, then by copying the data onto the same fields we’ve been toying with, it should be automatic. If the data consists of entirely new values in that there are more rows as well as more collumns, then a macro needs to be written to do the work, and it’s not trivial (not difficult, but not trivial). I suggest either you do the steps I recommended a few posts ago and record them then edit it (if you know VBA at all), or, given the size of the task, you can “hire” me to write the code for you. My going fee is $125/hour.

1 – Click Carpenter 55P in the chart once.
2 – Click it a 2nd time. Not as in a double click, but 2 single clicks. Notice the formula bar.
To create it in the first place:
1 – click the area of the column chart you want to put in the text. Name box (left of formula bar) should read “Series 3”
2 – Click it again in the same place. Name box should read “Series 3 Point xxx” or something like that.
3 – right click in same place & choose “Format Data Point”
4 – select Data Labels tab and check Value. Click OK.
5 – Now select the value entered by this process, say the 6925.
6 – select it again.
7 – go to the formula bar and type =, then click on the cell containing the text to show.

HTH

yes

OK, look at this — not saey to replicate, but doable…

Your data needed to be rearranged (see attached). Once it’s in the proper arrangement, you need to use the chart wizard & in step 1 choose 2nd from the left in the top row, which is the stacked column chart. IN the next step you have to override to get the series to go in columns.

Maybe — did you enter it with the range TWICE? Like
=SUM(1/COUNTIF(A1:A100,A1:A100))
and ctrl/shift/enter?

Ctrl/Shift/Enter:
=SUM(1/COUNTIF(range,range))
where range is the range of cells you want to count unique items.

You don’t need a macro to do this — manually use insert/name/define, find your old range name, type a new one in its place, then globally replace the old name with the new name in the workbook.

Select all of column B, then simply use this:
=B1<=A2
(You had a quote in your formula, among other things).
My course is not taught online, sorry.