Find and collect text or data from and to another Excelworkbook

[trueanswer]

Hi,

We are using an application that creates a spreadsheet when doing a search in a database. Let us call this spreadsheet autocreated.xls.
Today we have to complete this spreadsheet manually by using a reference guide excel spreadsheet. Let us call this fixed.xls.

Problem:
To complete this autocreated.xls with text and values we manually have to copy and paste this information from the fixed.xls spreadsheet.
We would like to atomate this.

Question:
In a spreadsheet, is there a command-line one can type in a cell to trigger a search in another workbook to find a datavalue and when found the command-line continues to collect other text or data from another cell on the same row?

For example, we need a command-line to...
Find value 44150 in column A in fixed.xls and collect text from the D column on the same row as 44150.

Thanks in advance!
True

[StephenR]

That sounds eminently feasible, but I suggest you attach a sample workbook illustrating both files.

[trueanswer]

Thanks for a reply!
Yes I hereby attach the two example files.

Thanks again
True

[StephenR]

Just to be clear, are you expecting the user to enter a cell no. and then the details are copied to H9:H11 or should the destination be specified too? I can't see any relation between the input and output.

EDIT: perhaps something along these lines (assumes Fixed workbook is open)

Please Login or Register  to view this content.

[trueanswer]

Right, sorry
I have updated the autogenerated document since the Cell No should match the Cell No in the other document.

Yes I guess the Fixed workbook needs to be open, thats fine.

The cell No is already in the document and if it automatically could get the 'Cellname' and x and 'y koord' values added from the fixed.xls document, that would be great!

Since I am new to programing excel, can you please explain how to put in this code into the document?

Thanks A LOT!

[StephenR]

Actually, you don't need a macro. You could use these formulae in the autogenerated file

H9: =VLOOKUP($F9,[Fixed.xls]Blad1!$A$5:$K$33,4,FALSE)
I9: =VLOOKUP($F9,[Fixed.xls]Blad1!$A$5:$K$33,8,FALSE)
J9: =VLOOKUP($F9,[Fixed.xls]Blad1!$A$5:$K$33,9,FALSE)

and copy down as necessary.

[trueanswer]

Hi StephenR!

I tried your formula today but received this error msg:
"The formula you typed contains error."
and Excel highlighted this portion of your formula "$F9,[Fixed.xls]Blad1"
I do have both doc opened at the same time.

One wondering, in your formula, what function does the value 4, 8 and 9 have?

I think there will be a problem if the formula is written in H9, I9 and J9 cause these cell's are targets of the data that automatically should be imported from fixed.xls by the formula.

I explain again, for example, if 44103 comes up in the autogenerated xls when generated, your formula should find the 44103 in the fixed.xls document and send the values "Cellname", "x - koord" and "y - koord" of the same row in return to the autogenerated.xls from fixed.xls.

Thanks a lot for taking your time!
Much appreciated!

Kind regards
True

[StephenR]

True - try this. Press the button in the Autogenerated file. You need to make sure the Fixed file has been saved and is open (though code could be modified to open it too if it were closed).

[trueanswer]

WOW amazing!
That worked fine
Bowing before you excel master
Thanks a lot!

[trueanswer]

Hi Stephen.R

Things have slightly changed in the fixed.xls dokument. Now we need to collect data from two different cells that belong to eachother. So the formula needs to know both values to be able to collect the data from a specific row.
First the 'automated.xls' has to match its own CNO and LNO headers with the CNO and LNO headers in the fixed.xls. When it successfully match the row it should send back the values of x-koord and y-koord to the automated.xls file.

Hope you see this thread

Best regards
True

[StephenR]

Please can you illustrate in your example files? Not quite sure what you mean.