How do I turn a contact list on one sheet into contact "cards" on another.

[mjtrifillis]

As the heading says, I am trying to take data (names, dates, email etc) from one sheet layed out in a traditional table format (but not a table) and have it auto fill on another sheet so that it looks more like a contact card (similar to outlook). I can easily do the =sheet 1!A5 for every entry, but the problem is when i try to copy/ drag down to auto create and fill it doesn't work because the data isn't linear. If you look at the two sheets, Sheet 1 A5 becomes Sheet 2 F1, Sheet 1 f5 becomes Sheet 2 B2. I've tried Vlookup, Offset, Index and perhaps im doing it wrong but cannot make it auto fill once i've done manually filled out 3 contacts.

Michael

 

[dermie_72]

Assuming your card starts at A1 in one sheet and the data also starts in A1 on another sheet (Data Sheet) you could use the following formula and change the columns that its looking for, via the Contact name.

=XLOOKUP(B2,'Data Sheet'!I:I,'Data Sheet'!F:F,"",0)&""

As you populate the Contact names it will populate all of the fields that relate to that contact.

If you link the Customer name In top left to the first name on the list. Then Top right to the next name, next box to the third and finally the fourth name in the box on the right. You will be able to copy the top 4 boxes and paste then down the page and it will automatically pick up all of the names on the list, just keep adding boxes until it's done.

 

[mjtrifillis]

=XLOOKUP(B2,'Data Sheet'!I:I,'Data Sheet'!F:F,"",0)&""

sorry, i dont understand your formula, where did you get I:I and F:F from? Sheet 1 data starts on A5 where Sheet 2 data begins at B2, im including an image of where the source info comes from for the first contact on Sheet 2, i took out the "=" so it would show the cell reference instead of the data. As you can see, the first contacts data on Sheet 2 comes from row 5 on Sheet 1, Contact 2 = Sheet 1 row 6 and so on.
also this seems to open a window to "Update Values: Data Sheet" all my data is in the same document, just on different tabs.

 

[dermie_72]

You didn't have column heading in you data capture so I just assumed that the customer name was in Column I. I named the sheet Data sheet, as sgsin, you didn't show the name of your sheet, I had to assume.
So in effect, Put a customer name thats on the data sheet into the customer card, then do an Xlookup, looking for the customer name in the customer card (That's the B2), searching in the data in the column that contains the customer name (I called that data sheet column I) returning the string that contains the data you're wanting to see, so Colum F for company name ect.

 

[mjtrifillis]

here's what i typed based on your message into sheet 2 B2-- =XLOOKUP(Sheet1!F5,Sheet2!B:B,"",0) the result says #VALUE! in B2 now. Perhaps im not being clear that my data is all in Sheet one and i want it to populate to sheet 2.

 

[dermie_72]

here's what i typed based on your message into sheet 2 B2-- =XLOOKUP(Sheet1!F5,Sheet2!B:B,"",0) the result says #VALUE! in B2 now. Perhaps im not being clear that my data is all in Sheet one and i want it to populate to sheet 2.

This is because you are only looking in one column, you need to look up the array column and a return column. If you have not used Xlookups before, I recommend clicking on the Fx button to the right of the address bar. This will give you the fields you need to fill in to return a value.

 

[mjtrifillis]

This is because you are only looking in one column, you need to look up the array column and a return column. If you have not used Xlookups before, I recommend clicking on the Fx button to the right of the address bar. This will give you the fields you need to fill in to return a value.

View attachment 107450

no idea what cells to click as i've explained that i have tried what i know and failed. I dont really understand the how/ why some of this works.and i feel like you are telling me how to fill in one contact instead of all of them. In short, im asking for the answer because i dont understand. once i see the code i can figure out the rest and adjust as needed.