• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Create an array of every other column_See MOD formula which does not work

Kamarlon

New Member
I have data in 4 cloumns. Starting from column A the numbers are: 10, 20,30, 40


I want to create a named range which will give me the first, and third numbers meaning it would give me numbers, 10 and 30. This is the formula I have:


=MOD(COLUMN(A1:A4),2). I called the named range "Actual"


I want this array so I can graph it on a line chart.
 
Hi, Kamarlon!

I didn't understand if your data is 10-40 in column A (A1:A4) or in row 1 (A1:D1), but however if you need a named range that addresses to first and third values just select A1 & A3 (or A1 & C1), go to the Formula tab, "Asignar nombre" button (in english I guess that something like "Assign name"), input "Actual" as name, and then press OK/Accept/Enter.

If you need rows 1 and 3 (or columns A & C), proceed exactly the same but selecting proper range first.

If I misunderstood you, I apologize and please consider uploading a sample file (see second green sticky post at this forums main page for guidelines).

Regards!
 
See link. The file name is "Select every other column"


https://skydrive.live.com/redir.aspx?cid=16d0ed9ee1cb5b75&resid=16D0ED9EE1CB5B75!113&parid=16D0ED9EE1CB5B75!102&authkey=!AFA90w3_C_BAAHc
 
Hi, Kamarlon!

Check this file, please:

http://dl.dropbox.com/u/60558749/Select%20every%20other%20column%20%28for%20Kamarlon%20at%20chandoo.org%29.xlsx

Regards!
 
SirJB7: Thanks for your efforts but I wanted a formula that would do the trick instead of manually selecting the cells. The spreadsheet will be growing so i want the graph to be updated automatically.
 
Hi, Kamarlon!

Let's see then...

http://dl.dropbox.com/u/60558749/Select%20every%20other%20column%20%28for%20Kamarlon%20at%20chandoo.org%29.xlsx

Gotcha?

Regards!
 
SirJB7 we're getting closer. The next thing I wanted to do was to get that formula =INDIRECT(ADDRESS($B3,(COLUMN()-1)*2,4,TRUE,"Sheet1"))as a named range. I want the formula to be dynamic. As it is now I would have to copy over the formula as the months progress.


I think the trick to getting it is in the column formula. We somehow need to tell it to dynamically pick up the cell reference. Currently its picking up the column reference from Sheet 2. I want it to pick up the column reference from the named range "Expenses" that refers to the data in Sheet 1. I hope i'm explaining myself clearly.
 
SirJB7:


I adjusted the formula as follows to create a name range "Actual":


=INDIRECT(ADDRESS($B3,(COLUMN(Expenses)-1)*2,4,TRUE,"Sheet1"))


I added the name range "Expense" in the column formula but it doesn't work. Can you help?
 
Hi ,


I am not sure about your problem , so please correct me if I am wrong.


You need to use INDIRECT only if the referred cells do not follow any clear-cut formula ; if you are sure that the "Actual" and "Budget" columns will always alternate without any gaps , then INDIRECT can be easily replaced by maths.


You need to have named ranges for your chart only if you cannot use helper rows / columns. If these are not a problem , just have the following formulae for your horizontal axis and data.


Put the following formula in any unused row and use this row for your horizontal axis :


=IF(INDEX($B$3:$BI$3,1,(COLUMN()-COLUMN(Sheet1!$B$1))*2+1)>0,INDEX($B$3:$BI$3,1,(COLUMN()-COLUMN(Sheet1!$B$1))*2+1),NA())


Put the following formula in any unused row and use this row for your data :


=IF(INDEX($B$17:$BI$17,1,(COLUMN()-COLUMN(Sheet1!$B$1))*2+1)>0,INDEX($B$17:$BI$17,1,(COLUMN()-COLUMN(Sheet1!$B$1))*2+1),NA())


You can copy these formulae as far as you want ; the #N/A will ensure that these cells are not plotted.


Narayan
 
Back
Top