automatic filling of cells

Question

Level 1

4 points

automatic filling of cells

I have a numbers file where I need to fill the cells with formulas. the file is made up of a first page where there are the minimum and maximum temperature numbers of some places that I monitor, therefore from January 1st to December 31st (see photo).

on the second page "JANUARY" and up to the "DECEMBER" page all the monthly summary temperature values are contained (see photo)

the last page "ANNUAL AVERAGE" is made up of temperature values regarding minimums, maximums and many other aspects. (see photo)

I have more than 18,000 formulas to insert and I'm looking for a way to insert them automatically, just by dragging the cell with the mouse. Some time ago someone from the community had already replied to me, but it didn't help me because by acting in this way the Mac didn't seem to understand the right formula insertion sequence (for example, if I manually insert the formulas regarding the first location and then drag the cell, the cells of the second location do not have the right formulas, so the automatic filling is wrong and is not feasible therefore). I don't know if you will be able to help me from this chat, so I can send the file directly to anyone who replies. Thank you guys :)

Posted on May 14, 2024 9:01 AM

Reply

Answer 1

Badunit

Level 7

26,320 points

May 14, 2024 11:13 AM in response to Yuri2302

Using $'s to lock in either the column or row or both is the typical way to make formulas fillable/draggable to other cells in the column/row, but this only works if the data is arranged in the table(s) so it can be done. Yours appears to be able to use that for for dragging down but not for dragging the the right.

The $'s won't work for dragging to the right in your table because each column is getting data from a different sheet/table. You can use INDIRECT, though, to create the cell references. Maybe something like this:

=INDIRECT(TEXTAFTER(B$1,"media ")&"::Tabella 1::I"&ROW())

TEXTAFTER(B$1,"media ") is getting the month name from the words in the header. For example, "Media Gennaio" will give you "Gennaio".
Tabella 1 is the table name
I is the column letter from which you want the data.
ROW() is getting the row number of the cell with the formula

You end up with a cell reference like Gennaio::Tabella 1::I2

I hope that helps.

Reply

Answer 2

Yuri2302 Author

Level 1

4 points

May 23, 2024 5:46 AM in response to Yuri2302

oh my how beautiful!!!! I corrected the formula and it worked!! after b﹩1 I replaced the comma with a semicolon and like magic it worked! So now I think I can insert all the formulas relating to the "year" page, but now we have to find the formula that allows me to insert all the formulas in the monthly pages, therefore "January" (which however I have already inserted), "February" , "March" and so on. I look forward to your reply, thank you very much!! (t works in both sides: down and right

Reply

Answer 3

Yuri2302 Author

Level 1

4 points

May 23, 2024 10:07 AM in response to Yuri2302

With the formula you provided me I got to cell C27, which corresponds to the calculation of the average temperature in February for the "five mile plateau" location; beyond that cell the formulas do not work since in February I only managed to write the formulas up to the aforementioned location, as you can see in the photo.

How can we insert the formulas relating to the columns N, O, P, Q, R, S and so on? (these are different formulas from the simple calculation of the average, in fact for example in the "S" column the formula is: COUNTIFS(DAYS::Table 1::B3:B33;"<0.1") , since in this case this formula is used to calculate the number of days in which the minimum temperature is equal to 0° or lower within the month; in this case the formula is related to cell J2 of the "January" sheet). So we have to find a way to automatically insert these formulas other than the average calculation, for each month (excluding January because I've already done it).

What we need to try to understand now is how to automatically insert the formulas into the cells of the various months, from the "February" to "December" page. we need to find the formula that allows these formulas to be automatically inserted into the various month sheets as well. I believe the principle is the same as the automatic insertion of formulas in the "year" sheet.

I wait for you :)

Reply

Answer 4

Yuri2302 Author

Level 1

4 points

May 14, 2024 12:09 PM in response to Badunit

hi and thank you for replying me. I'm sorry but I'm very confused; the formula you provided me in which cell should I insert it? because I still have to understand the mechanism and once I understand the game is done.

Reply

Answer 5

Badunit

Level 7

26,320 points

May 18, 2024 6:32 AM in response to Yuri2302

The formula was written for cell B2 on the Anno sheet, assuming I understood your original question and this is the formula you were looking for. Your screenshots show your monthly sheets having the exact same rows as the Anno sheet so you should be able to fill down to complete the rest of the column. Then you can fill right to complete the other month columns (out to column M).

Reply

Answer 6

Yuri2302 Author

Level 1

4 points

May 23, 2024 4:34 AM in response to Badunit

Good morning, even though I applied the corrections you told me to make, it tells me that there is still a syntax error and the formula does not lead to results. Why does this happen? What else should I do?

Reply

Answer 7

Badunit

Level 7

26,320 points

May 23, 2024 5:19 AM in response to Yuri2302

I see a comma in the formula that shouldn't be there. It looks like you replaced it with a semicolon (which I assume is necessary for your region) but did not delete the comma. I can't say if that is all but it is definitely an error.

Syntax errors can be hard to see sometimes but thay are almost always, if not 100% always, a typo or using syntax for the wrong region (using a comma instead of semicolon to separate parameters is a common one here for global users). You caught the comma vs semicolon thing but accidently left the comma.

There have been a few mentions recently of INDIRECT functions not updating/recalculating like they are supposed to. I hope this won't be a problem in your spreadsheet.

Reply

Answer 8

Yuri2302 Author

Level 1

4 points

May 25, 2024 6:08 AM in response to Badunit

Good morning, I'm always waiting for your precious suggestions and in the meantime I wanted to ask you another question: why did inserting formulas by dragging the cells work in the "January" sheet, which is the first month, and didn't it work for the other sheets?

Reply

Answer 9

Yuri2302 Author

Level 1

4 points

May 18, 2024 7:24 AM in response to Badunit

sorry but it doesn't work with the formula you provided me with. :(

Reply

Answer 10

Yuri2302 Author

Level 1

4 points

May 19, 2024 12:00 AM in response to Badunit

i deleted the ( and I put a space after media and here's the result.

is correct b1? cause it doesn't refer to "Gennaio" sheet, so does it should be like this?

Reply

Answer 11

Yuri2302 Author

Level 1

4 points

May 18, 2024 12:16 AM in response to Badunit

Sorry, could you explain me better how's working that formula and where do have I to insert it (I mean for the example you did in the previous text). Thank you in advance. :)

Reply

Answer 12

Badunit

Level 7

26,320 points

May 18, 2024 8:12 AM in response to Yuri2302

There is a space after media.

Reply

Answer 13

Badunit

Level 7

26,320 points

May 18, 2024 8:47 AM in response to Yuri2302

Also there is a syntax error. Probably something to do with the parentheses that didn't convert to the balloon parentheses. Delete the ( and that might get rid of the syntax error.

Reply

Answer 14

Yuri2302 Author

Level 1

4 points

May 27, 2024 8:42 AM in response to Yuri2302

Guys, please, I'm still waiting for an help :()

Reply