I want the data in cell a3 in table 2 to be the same as the data in cell a3 in table 1.
I then want the data in cell a4 in table 2 to be the same as the data in cell a57 in table 1.
I then want the data in cell a5 in table 2 to be the same as the data in cell a111 in table 1.
I have over 400 lines to enter and I would like to be able to copy the formula so that I do not have to enter 400 formulas.
iMac 27″, macOS 10.13
From the beginning there seems to be a pattern: the source rows are 54 rows apart: 3, 57, 111.
If, and only if this continues for your 21000+ source lines, you could put this formula in Table 2:A3 and copy it down 400 times:
=INDIRECT("Table 1::A"&(ROW()−3)×54+3)
What this will do is take the row number of the formula itself, so 3 of cell Table 2:A3, subtract 3 (get 0), multiply by 54 (get 0) and add 3 (get 3). It then uses that to build an address (Table 1::A3) from where it fetches the data.
Copied down, in Table 2:A4 it will get data from Table 1::A57, and so on.
From the beginning there seems to be a pattern: the source rows are 54 rows apart: 3, 57, 111.
If, and only if this continues for your 21000+ source lines, you could put this formula in Table 2:A3 and copy it down 400 times:
=INDIRECT("Table 1::A"&(ROW()−3)×54+3)
What this will do is take the row number of the formula itself, so 3 of cell Table 2:A3, subtract 3 (get 0), multiply by 54 (get 0) and add 3 (get 3). It then uses that to build an address (Table 1::A3) from where it fetches the data.
Copied down, in Table 2:A4 it will get data from Table 1::A57, and so on.
My suggestion is to keep the utility's data as virgin as possible, to avoid repeating complicated manipulations year after year, especially it you think it will always be offered in the same format. Save that trouble.
But if you insist on starting your data from line 39 then the formula would be
=INDIRECT("Table 1::A"&(ROW()−3)×54+39)
where 3 is the row of the first formula and 39 the row of the first data to copy.
To be sure I provided the correct formula I triple-clicked on it in my previous post and copied, went to Table 2::A3, typed = and then pasted. It worked perfectly, so I can't see what syntax error you could have got unless your document is in another language that uses different names for the INDIRECT and ROW functions.
"I have over 400 lines to enter and I would like to be able to copy the formula so that I do not have to enter 400 formulas."
Easy to do, IF there is a distinct pattern to the formulas, such as 'each receiving cell is in the same position on Table 2 relative to its 'source' cell on Table 2.'
But in your description:
3. The receiving cell on Table 2 is 106 rows above the above the location of the source cell on table 1.
Your basic formula will be a simple one.
In cell A3 of Table 2, it will be =Table 2::A2
In cell A4: =Table 2::A57
In Cell A5: =Table 2::A111
For each, you'll need to:
Click on the receiving cell in Table 2 to select it.
Press = to tell Numbers you want to enter a formula.
Click th source cell to insert that cell reference into the formula.
Click the green circle on the formula editor to confirm the formula.
Move on the the next cell pair.
Regards,
Barry
Thank you. I am having some syntax errors, but I think you have pointed me in the right direction. I just have to double check everything and find out where my error is. Often I leave out quotes etc when working with numbers. But thank you, I will work on this this weekend. You are correct in your assumption that the rows are 54 rows apart. I have solar power and my utility let's me download my usage. However, the download is by the hour based upon what I have bought and then by the hour based upon what I have sold. I am converting this to daily and then monthly etc. The original table with downloaded data for about a year is 29000 rows. I have a little work to go, but I think you have put me on the right track.
There may be an easier way to do what I am trying. When I filter the results of my first deletion of unneeded rows, the data I need is showing as consecutive rows down. However, the first filtered row is 39 and then increases by 54 so that I get rows 39 93 147 201 etc. If I could move the data from the rows that are visible and eliminate the filtered rows from the spreadsheet, I would then have rows 1 2 3 4 5 etc and I can then manipulate my data easier. Any suggestions on that possibility.
Thank you again. I will work on this some more. The reason I am making a separate table is because the data is hourly and I am converting the data to daily and then weekly. Going to solar has been a big savings for me and I am just curious about my usage.
feaco wrote:
the data is hourly and I am converting the data to daily and then weekly.
Pivot Tables are efficient at doing this kind of aggregation:
Click the data table and in the menu choose Organize > Create Pivot Table > On Current Sheet.
Click a circle i and you'll see lots of options.
No formulas! You have your answer after just a few clicks.
More here:
Intro to pivot tables in Numbers on Mac – Apple Support (AU)
SG
Eureka. Thank you again for the help. This is exactly what I wanted and it works great. My syntax errors were two-fold.I forgot the X that you used was *. Also, I have a little problem with the way numbers adds its own parenthesis. When I got past these two items, I was good. In my early days (We used to call them brown boot days in the army) I used Lotus 123 and Quattro and I wrote a lot of spreadsheets. Now, I just write spreadsheets when I want to track something, so I tend to forget more than I learn. I have never used indirect before and I understand your logic of indirect and row.
copy cell data from one table to another
