Forum Discussion
Need to fill data while keeping blank row in between
Pulled data from a different sheet to my current sheet using ='OrigSheetName'!A172. In the original sheet, the data is in numerical order "150, 151, 152" etc. I want to fill the data into the new sheet but when I use the drop down, it counts each row in my new sheet (which has two blank rows in between data that I'm pulling over) and it is counting those as rows from the original sheet.
Example:
Original Sheet
Data 150
Data 151
Data 152
New Sheet
Data 150
Data 151
Data 152
11 Replies
- It seems like you’re trying to copy data from one sheet to another in Excel, but you want to skip the blank rows in the new sheet. Here’s a way to do it:
Create a helper column: In your original sheet, create a new column next to your data. In this column, for each row, put the row number (you can use the ROW() function for this).
Use INDEX and MATCH in the new sheet: In your new sheet, instead of directly referencing the cells from the original sheet, you can use a combination of INDEX and MATCH functions. The INDEX function returns the value of a cell in a given range, and the MATCH function returns the position of a value in a given range.
Here’s an example formula you can use in your new sheet:
=INDEX(OrigSheetName!$B$1:$B$1000, MATCH(ROW(A1)*3-2, OrigSheetName!$A$1:$A$1000, 0))
In this formula:
OrigSheetName!$B$1:$B$1000 is the range in the original sheet from which you want to pull data.
ROW(A1)*3-2 generates a series of numbers 1, 4, 7, etc., as you drag down the formula. This helps to skip two rows for each data point.
OrigSheetName!$A$1:$A$1000 is the helper column in the original sheet that contains row numbers.
Please replace OrigSheetName, $B$1:$B$1000, and $A$1:$A$1000 with your actual sheet name and ranges. I may need to see the sheet but if you're looking to refer to an array and add 2 blanks after each item in the array, you could use:
arr being a named item referring to the range in the sheet.
=TOCOL(EXPAND(arr,,3,""))Sheet One
Sheet Two
I am trying to get all information from Sheet One to pull over to Sheet Two. It seems like this would be so simple but I just can't figure it out! The story card numbers are being pulled from Sheet One, Column A into Sheet Two. Then, all of text from Sheet One, Column F, needs to be pulled into Sheet Two, Column B under the word "Story Card"
- Are you using Google Sheets?
=IF(MOD(ROW(A4),3)=1,INDIRECT(ADDRESS(ROW($A$172)+QUOTIENT(ROW(A3),3)-1,1,,,"OrigSheetName")),"")
You can enter this formula in any cell of the original sheet and fill it down.
I have corresponding data that is text that I need to pull into this sheet, too. Does this formula only work with numbers? I need to still have two rows blank and then the text from the other sheet.
I'm not sure what you exactly want to do. All the data on the "OrigSheetName" Sheet is text, not numbers:
Original Sheet
Data 150
Data 151
Data 152
Maybe you can attach a screenshot which shows the data and the desired output.
