How to Enter The Value of One Cell if Another Contains a Value

[stuu3270]

Hello Experts,

The attached file shows what I am trying to do...

I am going to make a userform that allows a user to enter details of a vehicle and a driver. That form will enter the relevant detail into rows on the 'Data' sheet starting from B2 - Registration, Make, Model, etc... Only 2 to 6 cars will be entered at any time and a new file will be used for subsequent vehicle entries.

This is for an email to be sent to each vehicle owner with all the details within.

When all the data is entered on the 'Data' sheet, I want to map cells containing data to specific cells on the 'Cards' sheet. I.e. B2 (Registration number) will be copied to E6 on the 'Cards' sheet and so on.

The idea is that if only two vehicles are recorded then only two of the coloured blocks containing formatted data will appear. If a third vehicle is entered I want a third formatted block to appear below the first (as in the example).

I have already worked out how to use conditional formatting to colour the cells according to whether B2 contains any data (indicating there is a row to copy across). I have also worked out how to display nothing if column B contains nothing (no row of data).

What I am trying to do now is copy the text from column A in 'Data' to the merged and centred cell at the top of each coloured block. So, if there is an extra row of data, it will be copied across to the relevant cells, the surrounding cells given a colour then the title (Vehicle 1, vehicle 2...) from column A copied above.

How do I get the title to copy across?
Is there an easier way to achieve any of what I am doing??

Thanks,
Stuart

[stuu3270]

Anybody able to help me?

[Ace_XL]

Not entirely sure what you need...maybe..

In B4
=Data!A2

In H4
=Data!A3

so on and so forth


Also in E6 you could use
=INDEX(Data!B:B,MATCH($B$4,Data!$A:$A,0))

Adjust the column ranges (highlighted) for other fields

Adapat accordingly for other blocks

[stuu3270]

Thanks Ace,

Its early in the morning for me, so the E6 formula hasn't quite hit home yet. I copied it across and it just read B4 again so I'll have to look at that later.

Not sure if my explanation is very clear, sorry.

A userform will populate a row in the 'data' sheet for each vehicle entered.

I then only want data to appear in the 'card' sheet if there is something in the next row on the 'data' sheet. Therefore, everything on 'Card' is effectively hidden or blank until it finds a row of vehicle data from the 'data' sheet.

Formulae in 'Cards' for the relevant cells check for a row of vehicle data then colour the cells in a range and copy the data across to the relevant cells.

The idea is for a user to copy and paste the visible blocks into an email (cannot get the email range function to work at my work place) so I don't want pre-formatted blocks with no data in them to show in the email.

As per your reply...

For the cells containing data on the 'Cards' sheet, I only want them to appear and display the value of Data!B:B, C:C, D:D if there is something in a given row. So what I have kind of works if I program all the 'Cards' cells as I require, then each will stay blank and hidden until a value is entered into B:B on the 'Data' sheet.

As far as the titles in B4, H4, B29, H29 and so on... I only want them to appear when there are details in a further row from 'Data'. I either need to work out how to tell the title cells in 'Cards' to display 'Vehicle 1', 'Vehicle 2', etc if there is data for that card. Or to use the data from Data!A:A only if data has appeared in my hidden cells.