Formulas and shortcuts

[Neilwvr]

Hi,


I have two questions...

1. In a sheet, I have a column with vlookup formulas dragged down to Row "1000",
2. The formula(s) have ISBLANK() to only show data if the cell is NOT blank.
3. Lets say for example my vlookup pulls/shows data up to Row "100".
4. So I have formulas up to row 1000 and the vlookup is showing data for 100 Rows which is 100% correct, meaning that there is nothing to show from Row 101 to 1000 as there is no data for that yet.

[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]

Lookup fields​

[/TD]
[TD]

Formulas of B
(without the vlookup)​

[/TD]
[TD]

Result of B​

[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Data 1[/TD]
[TD]=IF(ISBLANK(A1),"","Show something")[/TD]
[TD]Show something[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Data 2[/TD]
[TD]=IF(ISBLANK(A1),"","Show something")[/TD]
[TD]Show something[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Data 3[/TD]
[TD]=IF(ISBLANK(A1),"","Show something")[/TD]
[TD]Show something[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Data 4[/TD]
[TD]=IF(ISBLANK(A1),"","Show something")[/TD]
[TD]Show something[/TD]
[/TR]
[TR]
[TD].[/TD]
[TD]Data 5[/TD]
[TD]=IF(ISBLANK(A1),"","Show something")[/TD]
[TD]Show something[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]Data 100[/TD]
[TD]=IF(ISBLANK(A1),"","Show something")[/TD]
[TD]Show something[/TD]
[/TR]
[TR]
[TD]101[/TD]
[TD][/TD]
[TD]=IF(ISBLANK(A1),"","Show something")[/TD]
[TD][/TD]
[/TR]
[TR]
[TD].[/TD]
[TD][/TD]
[TD]=IF(ISBLANK(A1),"","Show something")[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1000[/TD]
[TD][/TD]
[TD]=IF(ISBLANK(A1),"","Show something")[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Q1:
When I'm on Cell "B2" the Row under my headings, and I use (Shift+End+Down) or (CTRL+Down) to select ALL the Rows that have data it, it selects ALL Rows up to 1000. What is the shortcut to select ONLY the Rows that have ACTUAL Data and ignoring the Formulas? e.g. up to row 100 only as the rest do not contain actual data , but only formulas.

Q2:
Is there a way/function to automatically copy formulas down to the next row instead of me having to select the bottom-right block and dragging them down?

Thanks

 

[Mindpsyche]

A1:

Although your cells appear to be blank there is actually a formula in there which technically means the cells are not blank. Therefore when you use ctrl+shift+ down arrow, you will select all 1000 cells as all the cells have formulas in them, although from 101-1000 they appear as blanks.

A2:

This depends.

Let's assume you have data in column A from Row 1 to Row 1000.

Your first formula is in B1.

If you double click on the bottom right corner of the cell B1, the formula will copy all the way down to B1000. Basically using the data in column A to check till which row the formula should be copied to in Column B.

If you do not have data in Column A, then the above method cannot be used.

You can instead select the cell which has the formula in Column B, i.e. B1. Copy the formula.

Next scroll down using the scroll bar (quicker than the mouse roll) to the cell you want to copy the formulas to. When you click on that cell, press Shift and Click on it. This will select the entire range from B1 to the cell you clicked. Then paste the formula.

 

[Neilwvr]

I was hoping there was a shortcut for Q1... I suppose I could just add a filter and hide blanks first before selecting the range.
I never knew about the double click thing and it's awesome. Will this not slow down the spreadsheet?

Thanks for the Help.

 

[Mindpsyche]

I would not recommend copy pasting data, formulas or otherwise in the same range when filters are enabled, there is a chance of screwing up your data.

I don't understand the question about slowing down the sheet. Basically, if you have like 1000s of formulas in your sheet, especially array formulas, then you can expect the sheet to slow down, otherwise it shouldn't be a problem.

 

[Neilwvr]

The filtering is not for Copying and Pasting the Formulas...

Basically I have a PowerShell script to add Groups and Users to SharePoint. The spreadsheet is used to do a Vlookup to pull various fields (i.e. Employee#, Name, Department,...etc.) from a given list in order to populate the PowerShell script for me without any intervention other than the the imported data provided.
It is used to create 100's of User Groups and then add 100's of Users to each group which could take forever manually.

So essentially the result of each formula will contain the entire PowerShell Code per Group or User. Which is done and working 100%.
Once that is done, I now need to be able to select ALL the Rows that now have the result (PowerShell Code/Script).

So the Filtering to remove blanks is to hide the rows that have no "Code".

As per the Slowing down, that is exactly what I meant, which is obviously not ideal if you have many Sheets with many Formulas which is why I was hoping there was a way to automatically add the formula(s) to the next row without having to manually copy-paste, drag or double click.