Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
VLOOKUP to return value AND cell color
2,002 views
Skip to first unread message
Phrank
Jul 7, 2015, 11:49:20 PM7/7/15
to
Hi. Is there a way to use VLOOKUP to lookup and return both the values
from one list to another as well as the cell fill color of the source
list?
My users have a main workbook that imports updated data to a temporary
sheet. Below shows the general layout of the temporary sheet, with
DOC being the key. I've already got a macro that copies the DOC, RAT,
and Notes from the main workbook and imports the new DOC numbers from
the external workbook. And the macro adds a VLOOKUP formula to the
second RAT and NOTES columns. The macro then copies/pastes the
updated DOC, RAT, and Notes columns back to the main workbook. And it
works well.
BUT, users add personal color coding to their DOC, RAT, and Notes
cells, and it's important to be able to carry over the color coding
with the value that's looked up.
Example sheet layout:
DOC RAT Notes DOC RAT Notes
Is that possible? Thanks!
Frank
from one list to another as well as the cell fill color of the source
list?
My users have a main workbook that imports updated data to a temporary
sheet. Below shows the general layout of the temporary sheet, with
DOC being the key. I've already got a macro that copies the DOC, RAT,
and Notes from the main workbook and imports the new DOC numbers from
the external workbook. And the macro adds a VLOOKUP formula to the
second RAT and NOTES columns. The macro then copies/pastes the
updated DOC, RAT, and Notes columns back to the main workbook. And it
works well.
BUT, users add personal color coding to their DOC, RAT, and Notes
cells, and it's important to be able to carry over the color coding
with the value that's looked up.
Example sheet layout:
DOC RAT Notes DOC RAT Notes
Is that possible? Thanks!
Frank
GS
Jul 8, 2015, 12:26:32 AM7/8/15
to
> Hi. Is there a way to use VLOOKUP to lookup and return both the
> values from one list to another as well as the cell fill color of the
> source list?
No!
> values from one list to another as well as the cell fill color of the
> source list?
>
> My users have a main workbook that imports updated data to a
> temporary sheet. Below shows the general layout of the temporary
> sheet, with DOC being the key. I've already got a macro that copies
> the DOC, RAT, and Notes from the main workbook and imports the new
> DOC numbers from the external workbook. And the macro adds a VLOOKUP
> formula to the second RAT and NOTES columns. The macro then
> copies/pastes the updated DOC, RAT, and Notes columns back to the
> main workbook. And it works well.
Why does the formatting not copy/paste with the rest of the data?
> My users have a main workbook that imports updated data to a
> temporary sheet. Below shows the general layout of the temporary
> sheet, with DOC being the key. I've already got a macro that copies
> the DOC, RAT, and Notes from the main workbook and imports the new
> DOC numbers from the external workbook. And the macro adds a VLOOKUP
> formula to the second RAT and NOTES columns. The macro then
> copies/pastes the updated DOC, RAT, and Notes columns back to the
> main workbook. And it works well.
>
> BUT, users add personal color coding to their DOC, RAT, and Notes
> cells, and it's important to be able to carry over the color coding
> with the value that's looked up.
>
> Example sheet layout:
>
> DOC RAT Notes DOC RAT Notes
>
> Is that possible? Thanks!
>
> Frank
--
> BUT, users add personal color coding to their DOC, RAT, and Notes
> cells, and it's important to be able to carry over the color coding
> with the value that's looked up.
>
> Example sheet layout:
>
> DOC RAT Notes DOC RAT Notes
>
> Is that possible? Thanks!
>
> Frank
Garry
Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
Claus Busch
Jul 8, 2015, 3:42:15 AM7/8/15
to
Hi Frank,
Am Tue, 07 Jul 2015 23:49:05 -0400 schrieb Phrank:
> BUT, users add personal color coding to their DOC, RAT, and Notes
> cells, and it's important to be able to carry over the color coding
> with the value that's looked up.
can you find out a reason for the formatting you could use in a formula?
If so, you could use cinditional formatting.
Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
Am Tue, 07 Jul 2015 23:49:05 -0400 schrieb Phrank:
> BUT, users add personal color coding to their DOC, RAT, and Notes
> cells, and it's important to be able to carry over the color coding
> with the value that's looked up.
If so, you could use cinditional formatting.
Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional
Phrank
Jul 8, 2015, 6:10:40 AM7/8/15
to
Hi Garry and Claus. That's unfortunate.
The formatting does copy/paste with the rest of the data into the
first RAT and NOTES columns on the temporary sheet. However, the
formatting then goes away when the VLOOKUP is applied to second RAT
and NOTES columns, all of which gets copied back to the main workbook,
but without the formatting.
As for Conditional Formatting, the RAT could possibly work (RAT =
Rationale), so that could be standardized with some work. But NOTES
are text that are wide and varied and dependent upon the single
DOCuent entry at the time.
Any other thoughts on how I might help users make this happen?
Thank you!
Frank
The formatting does copy/paste with the rest of the data into the
first RAT and NOTES columns on the temporary sheet. However, the
formatting then goes away when the VLOOKUP is applied to second RAT
and NOTES columns, all of which gets copied back to the main workbook,
but without the formatting.
As for Conditional Formatting, the RAT could possibly work (RAT =
Rationale), so that could be standardized with some work. But NOTES
are text that are wide and varied and dependent upon the single
DOCuent entry at the time.
Any other thoughts on how I might help users make this happen?
Thank you!
Frank
Claus Busch
Jul 9, 2015, 3:30:17 AM7/9/15
to
Hi Frank,
Am Wed, 08 Jul 2015 06:10:25 -0400 schrieb Phrank:
> Any other thoughts on how I might help users make this happen?
can you provide us two workbooks with examples of the original data and
formatting and the expected output?
Am Wed, 08 Jul 2015 06:10:25 -0400 schrieb Phrank:
> Any other thoughts on how I might help users make this happen?
formatting and the expected output?
Phrank
Jul 9, 2015, 6:01:28 AM7/9/15
to
Yes, but I won't be able to get that until this evening. I'll post
then.
And I've had a thought. My initial thought was to use VLOOKUP, and I
posted here in the programming forum because I figured it would
involve something more than just a formula. I'm wondering if a
Copy>Find>Offset>Paste loop would do the trick? Here's what I'm
thinking in pseudo code:
In the temporary worksheet (CommentLoader)
- Start loop
- Look at first value in Column A and put the value in a variable
- Offset and copy the 2 adjacent cells in columns B & C
- Search in Column D for the variable loaded from column A
- Offset one column, and PasteAll
Unless I'm mistaken, that should bring the values and the formats from
columns B and C over to the appropriate location in columns E and F.
Does that sound right?
I can post the workbooks this evening. Thanks Claus.
Frank
then.
And I've had a thought. My initial thought was to use VLOOKUP, and I
posted here in the programming forum because I figured it would
involve something more than just a formula. I'm wondering if a
Copy>Find>Offset>Paste loop would do the trick? Here's what I'm
thinking in pseudo code:
In the temporary worksheet (CommentLoader)
- Start loop
- Look at first value in Column A and put the value in a variable
- Offset and copy the 2 adjacent cells in columns B & C
- Search in Column D for the variable loaded from column A
- Offset one column, and PasteAll
Unless I'm mistaken, that should bring the values and the formats from
columns B and C over to the appropriate location in columns E and F.
Does that sound right?
I can post the workbooks this evening. Thanks Claus.
Frank
Claus Busch
Jul 9, 2015, 6:09:02 AM7/9/15
to
Hi Frank,
Am Thu, 09 Jul 2015 06:01:13 -0400 schrieb Phrank:
> Unless I'm mistaken, that should bring the values and the formats from
> columns B and C over to the appropriate location in columns E and F.
>
> Does that sound right?
I had a similiar idea. But I must see the workbooks to find out if it is
working.
Am Thu, 09 Jul 2015 06:01:13 -0400 schrieb Phrank:
> Unless I'm mistaken, that should bring the values and the formats from
> columns B and C over to the appropriate location in columns E and F.
>
> Does that sound right?
working.
Phrank
Jul 15, 2015, 5:27:58 AM7/15/15
to
I keep getting sidetracked and forget to send myself the workbook from
work. I'll do that this morning when I get in.
work. I'll do that this morning when I get in.
0 new messages