Greg Stough
New Member
- Joined
- May 26, 2017
- Messages
- 17
- Office Version
- 365
- Platform
- Windows
New member on Mr. Excel, and this is my first post.
I know how to format an entire row based on the value of one cell. That's pretty straight forward, and I use that regularly. What I'd like to do is to format two (or more) rows based on the a value of a single cell. For example; When cell A1 has a value of "123" I want to apply conditional formatting to on A1:L1, BUT, I also want to format B1:L2 because value "123" is found in A1. I know how to create the formula to conditionally format the first row, but I can't figure out how to format the next row with that same formula. Of course I could create a second formula for for the second row, but I'd prefer a cleaner single solution approach. Fair enough?
Background, for those that like to read...
I'm a Business Analyst, and sometimes use Excel in some very non-traditional ways. During software testing a concatenated text file may be generated with several thousand lines (or 10's of thousands) of order information on it. The first part of each line is a "tag" that identifies the type of information found on that row (name, address, item number, PO, number, etc). But the next row may have a different tag, and entirely different information. But there will also be repeating patterns of rows too. For example there may be 6 rows (each with different types of data), and that 6-row pattern may then repeat 100's of times, but with information from entirely different orders.
The software that reads these files looks at the tags and then extracts the data for each line as needed. But I sometimes put these text files into a spreadsheet so they are easier to review and find the needed pieces of data (that's buried in all the other data). Sometimes we need to confirm that certain pieces of this data are correct during out tests. Sometimes these files are just a few lines long, and easy to review, as it, and other times they are not.
So, I'll use conditional formatting of a line to highlight the information they need to review. The business users can then quickly scan (or filter) down through 100's of lines of data and easily find the highlighted rows that they need to review. There are times that there are 2 different types of data (on different rows) that need reviewed, so I'd like search and highlight a row that has the text "PO" in it, for example, but also highlight the next row (or perhaps 2 rows down) that contains a part number, or something related. While I can create two different conditional formatting formulas, I'd like to do it all with one.
Anyway, I'm not sure if this is even possible, but if so I'd like to hear from you. Thanks!
- Greg
I know how to format an entire row based on the value of one cell. That's pretty straight forward, and I use that regularly. What I'd like to do is to format two (or more) rows based on the a value of a single cell. For example; When cell A1 has a value of "123" I want to apply conditional formatting to on A1:L1, BUT, I also want to format B1:L2 because value "123" is found in A1. I know how to create the formula to conditionally format the first row, but I can't figure out how to format the next row with that same formula. Of course I could create a second formula for for the second row, but I'd prefer a cleaner single solution approach. Fair enough?
Background, for those that like to read...
I'm a Business Analyst, and sometimes use Excel in some very non-traditional ways. During software testing a concatenated text file may be generated with several thousand lines (or 10's of thousands) of order information on it. The first part of each line is a "tag" that identifies the type of information found on that row (name, address, item number, PO, number, etc). But the next row may have a different tag, and entirely different information. But there will also be repeating patterns of rows too. For example there may be 6 rows (each with different types of data), and that 6-row pattern may then repeat 100's of times, but with information from entirely different orders.
The software that reads these files looks at the tags and then extracts the data for each line as needed. But I sometimes put these text files into a spreadsheet so they are easier to review and find the needed pieces of data (that's buried in all the other data). Sometimes we need to confirm that certain pieces of this data are correct during out tests. Sometimes these files are just a few lines long, and easy to review, as it, and other times they are not.
So, I'll use conditional formatting of a line to highlight the information they need to review. The business users can then quickly scan (or filter) down through 100's of lines of data and easily find the highlighted rows that they need to review. There are times that there are 2 different types of data (on different rows) that need reviewed, so I'd like search and highlight a row that has the text "PO" in it, for example, but also highlight the next row (or perhaps 2 rows down) that contains a part number, or something related. While I can create two different conditional formatting formulas, I'd like to do it all with one.
Anyway, I'm not sure if this is even possible, but if so I'd like to hear from you. Thanks!
- Greg