If Cell is Blank, Do nothing

[riqrat]

I have 3 sheets.

Sheet1 is Data1
Sheet2 is Data2
Sheet3 is Ratios

Data1 and Data2 only have two columns - Column A is DATE (mm/dd/yyyy) and Column B is DATA (decimal value).


For Sheet3, I simply want to do the following:

If there is a Date in Column A of Sheet 1 (which will be the same as Sheet 2, so I only use Sheet 1 for this), I want to place that date in Column A of Sheet 3.
Then take the value of Sheet1(Column B) and divide it by Sheet2(Column B) and place the result in Sheet3(Column B).

I am using this formula for Sheet3(Column A) copied for all rows:

=IF('Data1'!A1 = "", "", 'Data1'!A1) ** This is the first row formula

I am using this formula for Sheet3(Column B) copied for all rows:

=IF(A1 = "", "", 'Data1'!A1 / 'Data2'!A1) ** This is the first row formula


Now, these formulas work, but every row in Sheet3 gets filled up with either data or empty "". Excel is considering those blank rows as filled with something and it is draining my resources as well as not allowing me to insert rows because the worksheet is considered filled.

How do I get Excel to simply not do anything with blank rows leaving them as unused, blank rows?

Thanks.

[José Augusto]

Hi

I suppose your data in Data1!A2:B100 and Data2!A2:B100.

Try this (with a helper column to simplify)
In Sheet3!C2 use this formula

Please Login or Register  to view this content.

In Sheet3!A2 use this

Please Login or Register  to view this content.

In Sheet3!B2 use this

Please Login or Register  to view this content.

Copy down the three formulas.
See the example file

[riqrat]

Thank you Jose.

I guess it is my fault for not adding the additional requirement in that Data1 and Data2 grows by one row each day.

So setting a fixed number of rows in the formula (ie. $A$2:$A$100) would not address my need.

Data1 and Data2 are CSV files that I download.
Each day these files grow by one day, as the starting day/year does not change (1969).

So no formulas can be on Data1 and Data2, but on a separate sheet (like Sheet3).

In the example file you provided (Thanks!), Data1 and Data2 had 9 rows of data. Yet Sheet3 only had 6 rows of output.

So it did not appear to register the values from row 8 to 10.

In a perfect world, as the updated Data1 and Data2 files are loaded in Excel, Sheet3 would provide the date and ratios for each row including the new row added, and nothing more.

Thanks.

[samba_ravi]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[riqrat]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Okay, I've produced an example worksheet and uploaded it under 'Manage Attachements'. Not sure how to link it to this post though. Called example.xlsx.

The example is really quite simple.

It contains two CSV data files (in this example I only provided 20 rows of data). Data1 and Data2. These have identical columns. A is Date, B is the decimal values.

Sheet3 is where I'd like to show the results based on using the data from Data1 and Data2. Once this is working, I'd like to Chart Sheet3 results.

In Sheet3, Col A simply displays the date that is the same as found in Data1 and Data2.
Sheet3 Col B is simply the data of Data1 column B divided by the data of Data2 column B.

Example: Sheet3!B2 = Data1!B1 / Data2!B1

**Note: Sheet3 starts at B2 instead of B1 because Sheet2 has labels on Row 1.

Now each day open the worksheet, the latest Data1.csv and Data2.csv files are loaded which now has a new row added.
So Data1 and Data2 grow each day, and I want Sheet3 to automatically calculate for all rows including the new one.
That way the chart I wish to generate from Sheet3 will also be updated each day.

When I simply assign formulas to the whole column, Sheet3 ends up using EVERY row although blank!
That's like a million rows and my computer goes numb.

So bottom line, I want Sheet3 to only use as many rows (+1 for labels) as Data1 and Data2.
So if Data1 and Data2 are only using 1000 rows, Sheet3 should only be 1001 rows deep, no more.

Does this make sense?

Thanks.