Hard excel question?

Question

How do I capture all columns and rows associated with a given number across 2 sheets? I then want to combine the data on another sheet.

Example: Lets say I want any row or column with the number 100 in it selected in 2 sheets to put the info together. How can I grab both at the same time?

Answer 1

There are two ways you can do it.

1. You could write a macro to find all of the places it is located then select those columns and cells and then name the range so you can use the selected range.

2. You could use Ctrl F to find all of the instances of it. Then go through and select the rows and columns while holding down the Control key.

I'd recommend #2 for your case. If you are going to do it over and over and over, write the macro. If you are going to do it once or twice, then do #2. It would take a descent bit to figure out how to set up the macro even if you know VBA pretty well. And I'm assuming you're not the most familiar with VBA otherwise you wouldn't be asking the question.

Answer 2

This isn't necessarily very hard, but I need more information. You can do this with a macro, but what I don't know is whether you need it to check every cell of the row, or if a certain cell is going to identify that you need that. Contact me, I have an idea for you.

Answer 3

Your request is rather odd. Please restate your question making the objective / goal clear. Your original statement explains a method you want to use to implement the unstated goal rather than the "why" you want to implement that method. My guess is that there is a simpler way to achieve your goal than the approach you have described.