0

In my Excel document, I have two sheets, one with data and another that is referencing the first.

Sheet 1 is structured sort of like this:

[Meta data]

Date/time        | Temperature
2019-01-01 00.00 | 7,5
2019-01-01 00.01 | 7,4

The number of rows in the first sheet will grow over time as you add more data.

Sheet 2 is structured sort of like this:

[Meta data of a different kind]

Date/time                         | Temperature                         | Change in temperature
Copies first date/time in sheet 1 | Copies first temperature in sheet 1 | Calculation

As new data is added to sheet 1, I need to add new rows in sheet 2 as well. The way I do this is by selecting the last row in sheet 2, clicking the right corner and manually dragging the formulas downwards until I have covered all of the data in sheet 1. Since the data in sheet 1 can be very large this method is both cumbersome and time consuming.

Sadly, it's not possible to double-click the right corner to auto-fill the formulas downwards, probably because sheet 2 doesn't know how many rows there are in sheet 1.

Do I have to manually drag the formulas downwards or is there a better alternative?

3
  • could be handled in VBA. a little script such as when row added to sheet 1, formulas in last row of sheet 2 get copied down.
    – Forward Ed
    May 11, 2019 at 17:34
  • 1
    Alternatively you could over fill sheet2 with formulas. Ie sheet 1 has 4 rows of data, you could put 1000 rows of formulas in sheet 2. You could set the formulas from sheet 2 to display blank or 0 when there is no corresponding data in sheet1 eventually sheet 1 will catch up to sheet two. its just a potential work around.
    – Forward Ed
    May 11, 2019 at 17:35
  • Thank you for your answers. Very appreciated. I think I will use the latter solution for the time being since I'm not good with scripts. For some reason, I can't give you upvotes ...
    – Candleout
    May 15, 2019 at 9:05

1 Answer 1

0

Instead of using the normal =Sheet1!A2 formula in Sheet2, use =IF(Sheet1!A2="","",Sheet1!A2) instead..

then in the "Change in temperature" column (assuming it is = column C ).. do : =if(B2="","",<calculation>) , instead of just =<calculation>.

Drag all the way downwards (till the end of sheet). If there is no data in Sheet1, the Sheet2 will just display blank. ( :

Hope it helps.

4
  • It doesn't really, because I don't want to manually drag the formulas downwards. That's the main issue. But thank you for the answer.
    – Candleout
    May 15, 2019 at 9:08
  • You don't need to drag it.. just use keyboard shortcut. || Assuming the 2nd sheet is empty.. and the =if() formula was entered in the 1st row cell.. It takes only 2 steps : 1. goto the 1st line formula, copy. [ ctrl + c ] 2. go downmost n paste [ ctrl + shift + DownArrow , then ctrl + v ] . || And there you go.. the formula was safely applied till the end on the sheet.. within 2 seconds.
    – p._phidot_
    May 16, 2019 at 0:12
  • It'll also work for the =if(B2="","",<calculation> formula...
    – p._phidot_
    May 16, 2019 at 0:25
  • I see what you mean. Yes, that is a wokaround that will sort of get me to where I wanted. Thank you.
    – Candleout
    May 17, 2019 at 6:18

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .