Not sure how to word my problem properly

**streamofmight** · 07-09-2015, 05:23 AM

I tried googling this, and I'm sure I can find it, but I have no idea what keywords to use.

Basically I have a formula that can be dragged down. Ideally I can drag down to all 100000 rows, but that creates a huge file that lags. Is there way to add the same formula to the row below only if the current row is not empty?

So saying cell A1 has formula =if(A2="","","3")

i want A1 to A1048576 to have this formula. But that creates a big file.
So only when A1 or A2 is not empty, then will this formula be copied down to B1

Thanks in advance!

**TMS** · 07-09-2015, 05:30 AM

You're using Excel 2007 so convert your data to a Sructured Table (Insert | Table). That will propagate formulae, formatting, Conditional Formatting and Data Validation to cells in new rows.

Regards, TMS

**streamofmight** · 07-09-2015, 05:34 AM

Crap I'm actually on 13. Forgot to update.

I know of the table method, but it is not exactly feasible in my situation. Kinda hard to explain why.

Would a nested formula be better?

**:) Sixthsense :)** · 07-09-2015, 05:47 AM

Try using VBA

But I am not clear (from the below statement) about what you are trying to do

Originally Posted by streamofmight

So only when A1 or A2 is not empty, then will this formula be copied down to B1

**TMS** · 07-09-2015, 10:22 AM

If you copy a formula down a column ... for over a million cells ... the workbook will become bloated and slow. Not sure what you have in mind for a nested formula, but that is only likely to make the situation worse. Especially if you have similar formulae in multiple columns.

Your choices, if you don't want slow and bloated are:
1. Only copy the formula for as many rows as you think you will need
2. Use a Structured Table, or
3. Have a Worksheet Change Event to monitor column A and insert the formula in cells in the Target row.

You probably need to clarify the reasons for excluding options proposed.

Regards, TMS

Not sure how to word my problem properly

LinkBack

Thread Tools

Rate This Thread

Display

Not sure how to word my problem properly

Re: Not sure how to word my problem properly

Re: Not sure how to word my problem properly

Re: Not sure how to word my problem properly

Re: Not sure how to word my problem properly

Thread Information

Users Browsing this Thread

Similar Threads

constructing table of VB codes in word...... or printing properly in excel!

Word 2007 - How do I use columns properly?

[SOLVED] linking to Word not working properly

[SOLVED] Word Wrap does not seem to display properly

Excel Pivot Tables not sorting the word SUN properly

Bookmarks

Bookmarks

Posting Permissions