Sheet 2 skipping

[Countryboy69]

I'm building a workbook for work, I have very limited excel knowledge (YouTube has been a great friend lol). Anyways my question is on sheet 1 column A every cell may not end up filled out so on sheet 2 row b I want it to not skip rows if sheet 1 does. Sheet 1 is my input sheet where sheet 2 does the calculations. Any help would he awesome.

 

[James006]

Hello,

Would be great if you could clarify your request ...

Do you have in Sheet1 your Column A filled with Data separated by Blank rows ...

and your objective is to get in Sheet 2 your Column B filled with exactly the same data ...BUT skipping the Blank rows ...???

 

[Yongle]

Dynamically linking items in the 2 sheets eliminating blanks requires an array formula - committed with {CTRL}{SHIFT}{ENTER}

Paste this formula in cell B1 in sheet2 and commit with {CTRL}{SHIFT}{ENTER} before copying\dragging down
=INDIRECT("Sheet1!A"&SMALL(IF(LEN(Sheet1!$A$1:$A$500)=0,"",ROW(Sheet1!$1:$500)),ROW(A1)))

If you have entered the formula correctly it will look like this (you cannot type in the curly braces)
{=INDIRECT("Sheet1!A"&SMALL(IF(LEN(Sheet1!$A$1:$A$500)=0,"",ROW(Sheet1!$1:$500)),ROW(A1)))}

The number of rows in the range (500) should be adjusted to match the likely number of used rows in sheet1
The formula returns an error when there after the last item in sheet1
Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Name[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Name1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]Name2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]Name3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]Name4[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

11​

[/td][td]Name5[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

12​

[/td][td]Name6[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

13​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

14​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

15​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

16​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

17​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

18​

[/td][td]Name7[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

B​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Name[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Name1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]Name2[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]Name3[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td]Name4[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td]Name5[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td]Name6[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]Name7[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]

#NUM!​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet2[/td][/tr][/table]

 

[Countryboy69]

Ok when I input the formula which I had to slightly adjust ( sheet 1 is titled NOTES and the info begins at A3) but when I hit enter it brings up an open file window titled Update Values: NOTES1 not sure what this means

 

[Yongle]

Easy trick for you

1. Rename sheet "Notes" as "Sheet1"
2. Use my formula
3. Rename "Sheet1" as "Notes" (formula auto-adjusts)
4. Amend range in formula to begin at A3

(I think your formula has a typo - "Notes1!" Or "Notes1" when it should be "Notes!")

 

[Countryboy69]

OK not sure whats going on now but the file window no longer comes up instead it shows #value . sheet 1 is titled NOTES and starts in A3, Sheet 2 is titled DOWNTIME and starts in B3

 

[Countryboy69]

Easy trick for you

1. Rename sheet "Notes" as "Sheet1"
2. Use my formula
3. Rename "Sheet1" as "Notes" (formula auto-adjusts)
4. Amend range in formula to begin at A3

(I think your formula has a typo - "Notes1!" Or "Notes1" when it should be "Notes!")

This is what my formula now looks like =INDIRECT("NOTES!A"&SMALL(IF(LEN(NOTES!$A$3:$A$500)=0,"",ROW(NOTES!$3:$500)),ROW(A3)))

 

[Countryboy69]

also is there anyway to incorporate this formula in the one you made?
=IF(OR(D3="X",E3="X",F3="X",G3="X",I3="X",K3="X",L3="X",H3="X"),NOTES!A3,"")

 

[Yongle]

Did you remember to commit with {CTRL}{SHIFT}{ENTER} ?

 

[Countryboy69]

do i do that before or after i enter the code?