vlookup question

[mbbx5va2]

Hi

I'm just trying to do a variation of using vlookup. I want to take rows from the data table which are colored green only and then copy and paste the corresponding columns into the output columns starting in column H. I've already put the output manually to illustrate what I want. The vlookup code should go in column H I think.

I have: vlookup(B2,$A$2:$E:$4,4,FALSE)

I'm not sure how to specify that color cell in the first argument. Also I'm not sure about the 4 as in fact I need data copied to three columns.

Any thoughts?

[ashleyuk1984]

I'm sure you can't do a vlookup on cell colours. You'll need to do this via VBA. With VBA you can locate the coloured cells, and then if you still wish generate the vlookup formula or you can just dump the data as values.

[Aussiebear]

Wouldn't it be easier to simply filter your data table to only show those rows where the B cell is Green?

[mbbx5va2]

I'm sure you can't do a vlookup on cell colours. You'll need to do this via VBA. With VBA you can locate the coloured cells, and then if you still wish generate the vlookup formula or you can just dump the data as values.

I was thinking of using an IF statement within the first argument. I'll have to wait until I get home from work to look through some VBA procedures in this case.

Thanks

[mbbx5va2]

Thanks that should be good. I'll get back if there are any further issues.

[OG Loc]

Someone mentioned using a filter on green rows; this would indeed work. The weakness is that if you wanted to say copy-paste the output result somewhere else, you'd still get the resutls from the non-green stuff, as using the filter just hides it, doesn't actually get rid of it. See my attached file. Filter is controlled via the drop down arrow above the colour column. No fancy vlookups required!

[ashleyuk1984]

Someone mentioned using a filter on green rows; this would indeed work. The weakness is that if you wanted to say copy-paste the output result somewhere else, you'd still get the resutls from the non-green stuff, as using the filter just hides it, doesn't actually get rid of it. See my attached file. Filter is controlled via the drop down arrow above the colour column. No fancy vlookups required!

You are correct, but also incorrect if you know how to do it correctly
Select the filtered cells... Press F5, Special, VISIBLE CELLS ONLY... Now you can copy and paste
You're welcome

[Bob Phillips]

Am I missing something here? The copy-paste of the filtered data does not copy the hidden rows, it only copies the visible data. Don't even need the F5 approach. At least on my machine that is how it happens.

[OG Loc]

Am I missing something here? The copy-paste of the filtered data does not copy the hidden rows, it only copies the visible data. Don't even need the F5 approach. At least on my machine that is how it happens.

I decided to double check, and it definitely does copy the hidden rows as well with an ordinary copy. It's possible that all your hidden rows were at the beginning or end of the data so that when you select the visible part, you are actually missing some of the table. If you make sure to select the whole thing by selecting cells beyond the bottom for good measure, I find that it does reveal the hidden parts when you paste is, using Excel 2010. I guess the other exception is if you are pasting it onto the same set of rows so that the relevant ones are already hidden.

[Bob Phillips]

Oh come on, I am a bit better at Excel than that.

I have tried all combinations, and it behaves as I describe.

[OG Loc]

I have tried all combinations, and it behaves as I describe.

I have discovered the truth! In my spreadsheet I had the data in a table, while in yours it looks like it is just a range with a filter applied. Did another quick experiment and it seems they behave differently, as we have both described!

[Bob Phillips]

Guess what. Even tables behave properly for me, no hidden rows copied.

[Paul_Hossler]

Guess what. Even tables behave properly for me, no hidden rows copied.

Well, that's because they know whom they are dealing with

[Bob Phillips]

Well, that's because they know whom they are dealing with

Excel is very kind and gracious to me, that is why I love it

Now Windows on the other hand, ...