Skip cell based on another cell

Ingko

New Member
Joined
Sep 27, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi all,
Appreciate the assist with a weird one I have.

I dump some report data into multiple tabs and have Tab1 as an overall summary but stumped on an efficient way to skip/insert blank cells based on a reference to another tab. I'll attempt to describe my problem with a Tab-Cell reference format below:

Tab1-A1 is filled by reference to Tab2-A1 (lets say some sort of requisition ID# like "REQ111". [Therefore the result in Tab1 cell A1 = Tab2 cell A1 i.e. "REQ111"]
Tab1-A2 is filled by reference to Tab2-A2 REQ222
Tab1-A3 is filled by reference to Tab2-A3 REQ333
However...
Tab1-A4 is filled by reference to Tab2-A4 SUP111
Tab1-A5 is supposed to be filled by reference to Tab2-A5 but because the previous ID# begins with something other than "REQ" I want to leave Tab1-A5 BLANK
Then as a continuation...
Tab1-A6 is filled by reference to Tab2-A5 REQ444 <= In Tab2, the data dump doesn't have any breaks or blanks. It's only in Tab1 where I want the blank cell, once a blank cell is inserted I need it to continue from Tab2 where it left off.

If Tab2 had 1000 rows of data, of which 56 of those begin with something other than "REQ" then in Tab1 I'd have a grand total of 1056 rows where 56 are blank.
I plan to fill in this blanks by some other conditional formula which I'll tag on later but I'm stuck on the insert blank but continue piece.
Can't/don't want to VBA or powerQuery as those are beyond scope of the staff who will use this file later on.

Thanks in advanced.

Cheers.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,393
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
@Ingko Are you able to employ a 'helper' column in Tab2, which could be hidden if you wish?

Bridal Stuff2.xlsx
ABC
1REQ1111
2REQ2222
3REQ3333
4SUP1113
5REQ4444
6REQ5555
7SUP2225
8REQ6666
96
106
Tab2
Cell Formulas
RangeFormula
C1:C10C1=COUNTIF(A$1:A1,"REQ*")


Bridal Stuff2.xlsx
A
1REQ111
2REQ222
3REQ333
4REQ444
5REQ555
6REQ666
7 
Tab1
Cell Formulas
RangeFormula
A1:A7A1=IFERROR(INDEX('Tab2'!A$1:A$1000,MATCH(ROWS(A$1:A1),'Tab2'!C$1:C$1000,0)),"")


I am working with Excel 2010 so do not know if there are better solutions using some of the newer features within 356.
Hope that helps.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,992
Office Version
  1. 365
Platform
  1. Windows
My take on this
+Fluff 1.xlsm
A
1REQ111
2REQ222
3REQ333
4SUP111
5REQ444
6REQ555
7SUP222
8REQ666
9
Sheet2


Cell Formulas
RangeFormula
A1A1=Sheet2!A1
A2:A12A2=IFERROR(INDEX(Sheet2!$A$1:$A$8,IF(OR(LEFT(A1,3)="req",A1=""),ROW()-COUNTIFS(A$1:A1,""),"")),"")
 
Solution

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,393
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

^^^^Looks like I need to learn to read properly! ;)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,992
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Forum statistics

Threads
1,148,241
Messages
5,745,583
Members
423,963
Latest member
lwilson3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top