0

I have a spreadsheet which the user would enter a list of data points to be eventually plotted to a graph. There are 100 rows with the left most column containing a tick box for each row. i want the user to be able to tick 10 of the 100 boxes and have this data plotted to a graph in order from top of the list to bottom.

The tick boxes are linked to a column which display true of false. Ultimately i will have a column with 90 'FALSE' and 10 'TRUE' rows. The rows that are true could be in different places every time the sheet is used, including the first row containing true.

I want the data from the 10 true rows to be copied to a 10 row table (on the same sheet) and be plotted to a graph.

I had thought to number the true rows 1-10 from the top to the bottom which would make copying the data to the 10 row table simple.

However, i cannot get the numbering of these 'true' rows to work with a different start row each time. The closest i've managed was using the COUNTIF formula.

Can anyone help?

  • 2
    Please note that Super User is not a free script/code writing service. If you tell us what you have tried so far (include the scripts/code you are already using) and where you are stuck then we can try to help with specific problems. – Muhammad Ali Khamis Aug 24 '16 at 9:48
  • 1
    what type of graph do you want to create. Pivot chart may work for you (it works for majority of chart type except scatter (xy) plot and bubble chart) – Máté Juhász Aug 24 '16 at 9:49
  • Hi, as mentioned before this is not a code writing service, but a good explanation to the code you need can be found here: contextures.com/excelchartsinteractive.html – Michthan Aug 24 '16 at 9:51
  • I'm not expecting anyone to write it for me, i just need someone to point me in the right direction. I'm quite happy to figure it out myself, its just i've hit a brick wall and can't find anything searching the internet thats close to what i'm trying to achieve. – vaux_2 Aug 24 '16 at 9:55
  • 1
    I like where @Máté is going with this - sounds like a job for pivot table – Raystafarian Aug 24 '16 at 10:42
1

Whilst there are more elegant solutions, the formulas are somewhat more complex, but this will give you a very simple solution that is easy to implement.

Let's assume that your TRUE/FALSE values (and I'm assuming these translate to 1s and 0s in your cells) are in the range D3:D103, and lets further assume that the text associated with each of those potential 100 TRUE/FALSES is in the range E3:E103.

In cell C3 put the following formula and just copy down to C103:
=SUM(D$3:D3)

This will give you something like 0,0,0,1,1,1,1,1,1,2,2,2,2,2,3,3,3,3,3 etc going downwards, depending on where the 1s are in Column D.

(If you don't have 1s and 0s, and you actually have the TRUE/FALSE entries straight from the Form Control, just use =SUMPRODUCT(--(D$3:D3)) instead)

Now in say cells J6:J15, put the values 1,2,3,4,5,6,7,8,9,10, and then in cell K6 put the following formula and copy down to K15:
=VLOOKUP(J6,$C$3:$E$103,3,0)

What you will now see in column K is the associated text with each of the 10 options that have been ticked, in the order that they appear in the list.

If you wanted to know what number in the list they actually were, then in say cell L6, out the following formula and copy down to L15:
=MATCH(J6,$C$3:$C$103,0)

If you wanted to know the actual row that each entry appeared on, then in say cell M6, out the following formula and copy down to M15:
=MATCH(J6,$C$3:$C$103,0)+ROW($C$3)-1

0

Ok, i've managed to get it working, its a bit convoluted but it does the job nicely. Had to increase my range to 150 rows to cover the data that would be entered.

I had all of my true/falses in column I, so in column J i put the row numbers of only the rows containing true...

=IF(I7=FALSE, "", ROW())

Then in column K i used the RANK function to assign the 1-10 numbers i wanted. The row numbering will only ever be increasing going down the list so the rank function in descending order works fine.

=IF(J7="", "", RANK(J7,$J$7:$J$156, 1))

Then i was able to use the VLOOKUP function to match those 1-10 numbers to the data i wanted to extract to populate the 10 row table i have elsewhere on the sheet for plotting to a graph.

I'm sure there are many better ways to do this but unfortunately i'm on a deadline to have this completed so i can revisit this later to see if i can improve it.

Many thanks for the posts as it gave me others way to try which ultimately led to getting it working.

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.