Need formula to follow a specific order, please help.

[Darker0ne]

Hello everyone,

I have 3 Sheets, one is a template, other is a Data file i recieve everyday other is a merge between those two files, ill explain in detail.
A simple example of my template file:

Name Phone Gender Product

A simple example of my Data file:

Abc 123 Male Z Def 456 Male X Ghi 789 Female C Jkl 101 Female V

And a simple example of how the Merge file should be:

Name Phone Gender Product Abc 123 #BlankCell##BlankCell# Male #BlankCell# #BlankCell# #BlankCell# Z Def 456 #BlankCell# #BlankCell# Male #BlankCell# #BlankCell# #BlankCell# X Ghi 789 #BlankCell# #BlankCell# Female #BlankCell# #BlankCell# #BlankCell# C Jkl 101 #BlankCell# #BlankCell# Female #BlankCell# #BlankCell# #BlankCell# V

The merge file is " importing " data from the other two files, problem is when i drag it down, it looks like this instead:

Name Phone Gender Product Abc 123 #BlankCell# #BlankCell# Male #BlankCell# #BlankCell# #BlankCell# X Jkl 101 #BlankCell# #BlankCell# Female #BlankCell# #BlankCell# #BlankCell# V

I can "lock" the values of the static fields of the template, using $A$1, for example.
But i need the drag down formula to follow the order of the data file, not the order it's using right now.

Can someone please help me with this ?
I don't know if i managed to explain myself properly, if you have any questions, please ask.

Thank you, have a nice day !

Note: I'm sorry, i don't know how to simulate blank cells in this table to place my values at the right position... #BlankCell# is, well, a Blank Cell

[alansidman]

You might have better success at getting a response if you attach a sample workbook.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Darker0ne]

TestWorkBook.xlsx

Hello again,

As alansidman sugested i uploaded a example WorkBook.

Sheets :

File

Is where i paste the file i recieve and it formats the fiels like i want.

FileWithFormulas

Is the sheet arranged with the formats i need, and how i need them to be.

PasteSpecialValues

I copy "FileWithFormulas" and Paste Special / Values, so i get a table with the correct formats/results, but without formulas.
Is also the sheet i import values to "FinalResult"

Template

Is the sheet where i import to "FinalResult" the template for the client, so i have to fill in data on those 3 lines, and each 3 lines represent a single client.

FinalResult

Is the merge/import data of "PasteSpecialValues" and "Template" in it's final state.
Where each 3 lines represent a client, although, in "PasteSpecialValues" each line represents a client.
That's why it goes wrong, and why i am here to ask you guys for help.

When i select the data, and dropp it down, line 1 of "PasteSpecialValues" is ok, but next aren't, because formula jumps to the line it was droped, so, line 4/5/6 of "FinalResult" are lines 4/5/6 of "PasteSpecialValues", and i want it to be just line 2.


I hope i managed to explain myself in a better way this time, thanks!

[Darker0ne]

Please !

Can someone help me with this issue ?!

[kvsrinivasamurthy]

Pl see attached file with new formulas to enable the dragging of A2:K4 range.

[Darker0ne]

Works perfectly !
Can't thank you enough.

Is possible to explain the formula please ?

Many thanks !

[kvsrinivasamurthy]

Thanks for the compliments.
Pl Mark thr thread SOLVED.
In the formula
=INDIRECT("PasteSpecialValues!A"&2+INT((ROW(A1)-1)/3))
When pasted to next Cell, there is shift of 3 rows. ROW(A1) will be ROW(A4).
But value of
INT((ROW(A4)-1)/3)
Increases by 1 only.That is what is required.

INDIRECT formula converts String To Range.
For Step by step check
Formulas --> Evaluate Formula --> Evaluate.

[Darker0ne]

how i make thread solved please ?