Numbers: How to refer to every nth cell

[rbrian]

I need to extract the contents of every nth cell in an unnecessarily complicated spreadsheet. I get a list of jobs, with six pieces of information needed for each job - I want each on one row. However, with needless comments I get the jobs in a giant worksheet, with the information for each job spread across 8 rows.

For example, the first job number is in cell A15, the next is in A23, then A31, etc.

The next piece I need is the bar, the first of which is in B19, then B27, then B35, etc.

I want to make a new sheet in which the contents of A15 is in A1, the contents of A23 is in A2, etc. B19 to B1, B27 to B2, etc.

How can I do this without referring to each cell individually, 96 times a day?

 

[karl878]

More than one way to do this, but I would build this worksheet using the "Offset" function; you will find it under Formula List, Reference, Offset.

From function description: "The OFFSET function returns a range of cells that is the specified number of rows and columns away from the specified base cell."

 

[rbrian]

Thanks Karl, that's a start. Now how to fill the remaining cells properly? I put Offset (A15),0,0 in A1, Offset (A15),8,0 in A2, and those worked - but dragging down to fill the cells underneath filled Offset (A16),0,0 then (A16),8,0 when what I want is (A15)16,0 then (A15),24,0 etc. I tried ($A$15) but that just repeated 0,0 then 8,0, then back to 0,0 then 8,0.

 

[rbrian]

Ok, I managed it, a little bit messy, but it works. I started another column with H1=0, H2=8, H3=16, and dragged that down to fill a list. Then I used A1=Offset ($A$15) (H1),0; A2=Offset ($A$15) (H2),0 and dragged it down to fill a list. Is there a better way?

 

[karl878]

Your last post, where you added the H column, is how I would have developed the worksheet. During development I like to see parameters like that in separate columns and rows, as it helps me visualize how the worksheet works. In most cases, once I know a worksheet processes everything correctly, I would probably not change anything (leave the H column).

 

[rbrian]

One more thing...

Is there an easy way to set the maximum size of a sheet? I need it to be 2047 rows long, which takes a fair bit of time of holding down my finger on the trackpad. And then that same amount of time again to fill in the sequence.

 

[karl878]

Yes, easiest way is to setup the worksheet once. Just as you have been doing by dragging to add rows/columns, then add your text, etc. Then "Save as Template" under "File". Subsequently, you will use this template you created.

 

[rbrian]

Great, thanks. That's not how I expected it to work, but it does the job. I'm sure I'll have many other newbie questions soon!

 

[rbrian]

Hard to believe I've never really used a spreadsheet before, and now I'm exploring functions and should soon have everything I need. It took my colleague about 6 months on and off to design the first version of this, using incredibly complicated VBA macros in Excel - all of which was lost when his hard drive crashed. So we started again, bouncing ideas of each other, him in Excel, me in Numbers. His latest version is much simpler, but mine is simpler still, and backed up on iWork.com.

 

[rbrian]

I'm sure I'll have many other newbie questions soon!

...and here it is. I want to use LOOKUP across multiple columns - is there any way to do this? My table looks something like this, but much bigger:

A1 6 B1 1
A2 5 B2 7
A3 8 B3 10
A4 3 B4 9
A5 2 B5 4

I want to be able to search for A2 and find 5, or search for B3 and find 10. So far as I can tell, I can only do it on one column at a time - but I'll have to search through multiple columns. The whole purpose of the sheet is to bring order to the randomness, but I can't see how to do it unless I remove the randomness first! The final result I'm looking for will be:

1 B1
2 A5
3 A4
4 B5
5 A2
6 A1
7 B2
8 A3
9 B4
10 B3

Is Numbers capable of this?

If I could drag everything into two columns I would, but that messes up the formulas inside them. That may be an option, but only if I can have a relative absolute value...

 

[CylonGlitch]

Your first problem could be simplified further.

A1=OFFSET($A$15,(ROW()-1)*8,0)
This is simply done is takes the current row (you started at 1) and makes it 0 based. Then multiplies by 8 to get every 8th row. This way as you fill down, it calculates for you the next location.

As for the second problem. Lets assume your table is this way and the upper left corner is D1 making the lower right G5.
A1 6 B1 1
A2 5 B2 7
A3 8 B3 10
A4 3 B4 9
A5 2 B5 4

Best way is use VLOOKUP.

=VLOOKUP(A4, D1:E5, 2, FALSE) will search for "A1" in the left column and then return the second column. If you want to search B terms you'd have to use the same command but the range of F1:G5. How do you search BOTH rows? A bit more difficult. Create two searches, one for the A column, and one for the B column. [=VLOOKUP(A4, F1:G5, 2, FALSE)]. If it finds and exact match the value is returned, if not, it presents an error (red triangle) [the second lookup will do this because A4 is not in the search range].

Assume the First VLOOKUP is at B3 and the second at C3 but you want the search result at A3; you do an if at A3 :
=IF(ISERROR(B3), C3, B3)

What this says is that if B3 is in error (meaning it didn't find the item) then use the results in C3; otherwise use the item it found.

Make sense? Easy, isn't it?

 

[rbrian]

Thanks, Cylon. I new there had to be a simpler way!

I'll give your solution to the second problem a try. Will it scale easily? I may need to search up to 100 different columns.