Add to cell reference on multiple rows

[craigo298er]

I have a table that I have a lot of production numbers in at regular intervals, say 4/hr at 15min intervals. Each 15min interval is in a different row, all in the same column. How do I quickly populate a table where each hour is on a different row, four columns wide? I know the text =C# doen'ts correspond, it's just to make a point.
I've tried making =C1, =C2, =C3, =C4 on one line, the 5-10 on the next (and that works), but if I click and drag it, it gets all messed up.

Example here since it isn't attaching: https://imgur.com/DTpjmXq

 

[craigo298er]

To be a little clearer, I want to make something that starts out =C1, then =C(1+1) for C2, then =C(1+2) for C3...=C(1+n) for n number of cells

 

[DanteAmor]

Try this

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:16.16px;" /><col style="width:16.16px;" /><col style="width:76.04px;" /><col style="width:14.26px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td style="text-align:right; ">0.25</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td style="text-align:right; ">0.5</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td style="text-align:right; ">0.75</td><td > </td><td style="text-align:right; ">0.25</td><td style="text-align:right; ">0.5</td><td style="text-align:right; ">0.75</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td style="text-align:right; ">1</td><td > </td><td style="text-align:right; ">1.25</td><td style="text-align:right; ">1.5</td><td style="text-align:right; ">1.75</td><td style="text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td style="text-align:right; ">1.25</td><td > </td><td style="text-align:right; ">2.25</td><td style="text-align:right; ">2.5</td><td style="text-align:right; ">2.75</td><td style="text-align:right; ">3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td style="text-align:right; ">1.5</td><td > </td><td style="text-align:right; ">3.25</td><td style="text-align:right; ">3.5</td><td style="text-align:right; ">3.75</td><td style="text-align:right; ">4</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td > </td><td > </td><td style="text-align:right; ">1.75</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td > </td><td > </td><td style="text-align:right; ">2</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td > </td><td > </td><td style="text-align:right; ">2.25</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td > </td><td > </td><td style="text-align:right; ">2.5</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td > </td><td > </td><td style="text-align:right; ">2.75</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td > </td><td > </td><td style="text-align:right; ">3</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td > </td><td > </td><td style="text-align:right; ">3.25</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td > </td><td > </td><td style="text-align:right; ">3.5</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td > </td><td > </td><td style="text-align:right; ">3.75</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td > </td><td > </td><td style="text-align:right; ">4</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >E6</td><td >=INDIRECT("C" & ROWS($C$4:C4)*4+COLUMNS($C$4:C4)-1)</td></tr></table></td></tr></table>

Drag formula to the right until H6 and then drag it down

 

[bmacias]

In the example you showed your data does not start on C1 rather it starts on C4. Let's suppose it did start on C1. Here's the way I would do it: https://imgur.com/d1Zu5ay. There's probably even a faster way to do it, but this is how I would do it given my limited experience.

Cheers,

Ben

 

[craigo298er]

In the example you showed your data does not start on C1 rather it starts on C4. Let's suppose it did start on C1. Here's the way I would do it: https://imgur.com/d1Zu5ay. There's probably even a faster way to do it, but this is how I would do it given my limited experience.

Cheers,

Ben

thanks, as I said in the original post "I know the text =C# doesn't correspond, it's just to make a point. "

I did figure out a workaround where I typed C1, C2, C3, C4 and then C5-10 and C11-15 on rows two and three, then did a Find & Replace C with =C by column. A bit sloppy, but I guess the only way?

 

[DanteAmor]

Did you check the solution in post3?

 

[craigo298er]

Did you check the solution in post3?

yeah, it works, but it is not something one can replicate with ease. Sure, once you have the formula it can be done easily, but to quickly & easily do it from scratch was the goal. to modify it to work in other tables and sheets it'd take some adjustment, but should there not be an easier way?

 

[DanteAmor]

You only have to change C4 to the cell where your information starts.

A bit sloppy, but I guess the only way?

But you decide which one to work with. If my solution does not work for you, it is also right that you comment on it.

 

[craigo298er]

You only have to change C4 to the cell where your information starts.




But you decide which one to work with. If my solution does not work for you, it is also right that you comment on it.

yes, you were right.


that being said, 2+2 and 2 ≡ λf.λx.f (f x) [see solution below, thanks reddit] both equal 4, just one is more complicated to do and not very quick like I asked. Just because you got the right answer, doesn't excuse the fact (to me) that it is not easily repeatable and quickly adaptable to multiple pages & columns or sources. If you're offended, that's on you.

plus 2 2 ≡ (λm.λn.λf.λx.m f (n f x)) (λf.λx.f (f x)) (λf.λx.f (f x))
↦ (λn.λf.λx.(λf.λx.f (f x)) f (n f x)) (λf.λx.f (f x))
↦ (λf.λx.(λf.λx.f (f x)) f ((λf.λx.f (f x)) f x))
↦ (λf.λx.(λx.f (f x)) ((λf.λx.f (f x)) f x))
↦ (λf.λx.(λx.f (f x)) ((λx.f (f x)) x))
↦ (λf.λx.(λx.f (f x)) (f (f x)))
↦ (λf.λx.f (f (f (f x))))
≡ 4.

 

[DanteAmor]

It does not offend me, if the solution does not work for you, it just does not work for you, I just expected a feedback, saying: "It does not work for me", that's all.
The only thing I was trying to do is try to help you.