Copy format

[LiAD]

Hi,

I have several spreadsheets where I import data using macros into a series
of tables. Several operations are then performed on the data. The new
outputs plus some of original inputs are then transferred into another set of
tables using match index vlookup etc formulas where they are then re-ordered
and grouped.

By using the macro I copy the data into table set 1, along with the formats
as per original. For example line 1 has red bold text in all cells and the
second line has a green filling in the cells. I will see the red bold and
filling in table set 1.

Table set two will then re-arrange the data from set 1 using the search
functions but it cannot copy the formats.

Is there a way of getting the search function to copy formats as well as
contents?

Thanks
LiAD

 

[Don Guillett]

look at
edit>copy>paste special
to see the options

 

[LiAD]

Thanks,

I was hoping for some way of doing it on auto. As soon as the data is
tranferred from table 1 to 2 by the index/match type fomulas it takes the
format with it.

Do you know how this could be possible without the user needing to push
buttons etc?

 

[Gord Dibben]

If using formulas alone to transfer data you cannot also transfer the cell
format.

Maybe Conditional Formatting will do what you want?

Same CF for each Table?


Gord Dibben MS Excel MVP

 

[LiAD]

CFs change change position according between tables. Example starting in
cell A1 going to E5 there is a list of items such as

Table 1 contains

A B C D E
3 X 3 R 2/4/09
5 E 4 Y 5/6/08 (imagine that Y is black italic
text)
1 Z 9 P 4/6/09 (imagine Z is red bold text and P
has yellow filling)
4 F 1 T 2/9/09 (F is green text, 1 is red filling
and T grey filling)
2 E 8 E 2/6/07

In table two I then would like to order the above data in ascending order
according to column A so the outputs would be, (using index and match to fill
the table)

A B C D E
1 Z 9 P 4/6/09 (imagine Z is red bold text and P
has yellow filling)
2 E 8 E 2/6/07
3 X 3 R 2/4/09
4 F 1 T 2/9/09 (F is green text, 1 is red filling
and T grey filling)
5 E 4 Y 5/6/08 (imagine that Y is black italic
text)

The position of the coloured text, filling etc will change between different
sheets and every time the file gets updated.

Is this possible?
Thanks