Fill Down repeating previous Cell

Question

Lennox73 Author

Level 1

0 points

Fill Down repeating previous Cell

Hi

I'm trying to do what I thought would be a simple task but I'm having major problems

I want to look up a cell on a different sheet

Sheet 1

Column/Row	A	B	C	D
1	Wall ID	Dead	Live	Total
2	1	10
3	5	15
4	2	8
5	4	10
6	3	6
7	3	9
8	4	5
9	2	8

Using the above

I want to fill down the values of the sheet into sheets

Sheet 2

Column/Row	A	B	C	D
1	Wall ID	Net Load	Point Load 1	Point Load 2
2	1	=sheet1:D3
3	2	=sheet1:D5
4	3	=sheet1:D7
5	4	=sheet1:D9

However when I fill down instead of sheet1:D11, 13 ,15 ,17 in rows 6, 7, 8, 9 as expected I seem to get a copy of D9 in 6, then D11, D13, D15, D17.

If I say, I highlight B2:3 and select the yellow tab on the bottom of the cell, it would produce D5, D7, D7, D9, D9, D11, etc

What Gives, what am I doing wrong.

iMac, Mac OS X (10.7.5)

Posted on Feb 11, 2014 11:52 AM

Reply

Answer 1

Feb 11, 2014 12:21 PM in response to Lennox73

Lennox,

Why are you skipping rows? Why is there information on a row with no wall ID?

When I look at the table I am confused by how numbers are realized:

is the value in column D supposed to be the sum of the circled values in column B and C? IF so why not placed tham all in the same row? This would simplify the solution

Reply

Answer 2

Feb 11, 2014 12:31 PM in response to Lennox73

Regardless of the answer abover... here is how you can solve the problem:

this method located the WALL ID, in the first column of the "Data" table (on the left). Notice I named the table on the left "Data"-- this is important as the table on the right refers to the table by the name "Data". You can change table name of the name of the table I use in the following formaulas.

In the table on the right (named "Summary"):

B2=IFERROR(OFFSET(Data::$A$1,MATCH(A2, Data::A,0)−0, 3), "")

this is shorthand for select cell B2 and type (or copy and paste from here) the formula:

=IFERROR(OFFSET(Data::$A$1,MATCH(A2, Data::A,0)−0, 3), "")

select cell B2, copy, then select column B by clicking the column header (the letter "B" at the top), then hold the <command> key and click the cell B1 to unselect cell B1, then paste

Reply

Answer 3

Lennox73 Author

Level 1

0 points

Feb 11, 2014 9:58 PM in response to Wayne Contello

Thanks Wayne

For your info, the reason that I have skip rows is purely for presentation reasons, so that I can separate the Dead and Live Loads

I would insert a pic to show you, but it won't let me for what ever reason (soz new to this sort of thing)

So its not a matter of using fill down then :-(

That formula looks very complicated now wonder I couldn't get it to work, would never have managed to figure that one out. Surely there is an easier way

Reply

Answer 4

Barry

Level 9

59,867 points

Feb 11, 2014 10:44 PM in response to Lennox73

Hi Lennox,

The 'easier way' is to set your data up in single rows for each record/case.

If you want to separate them vertically for presentation, you can make the rows twice the height that a single row would require, the format the data's position in the cell(s) to top or bottom.

Placing the ID and the data to be retrieved in the same row makes it possible to treat the main table as a simple lookup table, and use one of the LOOKUP functions to retrieve the total. The example below uses LOOKUP:

Formula in column B of Table 2 (smaller table):

B2: =LOOKUP(A,Table 1 :: A,Table 1 :: D)

Fill down to B5.

Table 1 (larger table) shows formatting I discussed above. I don't think it (or the separate rows version it's based on) helps the presentation. Colour fill might be a better choice:

Note that no thought was given to the colour choice. These are just the bottom cell from each of the first three columns on the Color Palette.

Regards,

Barry

Reply