Index Using User input data from a text cell

[Matt53]

Hi All, Newbie to this and in fact any forum, so bear with!

I have searched and searched for an answer to a problem I have with my son in laws car sales accounts workbook. I have produced the workbook with lots of help from MrExcel Videos and the forum (whilst not being a member) and we are happy with the way it works (not sure about the accountants yet!!) but I am now trying to automate some of the data transfers to the ‘New Years Accounts’ workbook. If it were just my son in law then I would simply copy the necessary data straight into the new years spreadsheets but other people have shown an interest in the workbook and I would like it to be automatic

I would like to be able to transfer the “company data”, Cars still in stock at end of year and Bank Balance. In the ‘New Year Accounts’ I have a cell for the user to input the full path to the old workbook and what I am trying to do is import various cell data from the old workbook (preferably not having to open it first). I have read that INDIRECT is not really a satisfactory option but that INDEX could be

Selecting ‘Business Name’ cell and typing the full path into INDEX myself, it works a treat and pulls the business name etc. from last years ‘Company Data’ but when I use the user input data with concatenate I get a #Value error.

I am using Excel 2016 with a little basic knowledge of formula’s but a lot of enthusiasm and pride in how far this workbook has come on.

The basic layout of a few cells is:

Last Years Workbook (AMC With Old Month Data.xlsx):
Company Data Worksheet
Cell K12 = “Business Name”
Cell K14 = “Company Address”
Cell K15 = “Address Line 2”

Etc. You get the picture

New Years Workbook (With Manual Input)
Company Data Worksheet
Cell K6 = (path to Last Years Workbook) C:\Users\Sandie\Documents\Adams\[AMC With Old Month Data.xlsx]
Cell K12 = INDEX(‘C:\Users\Sandie\Documents\Adams\[AMC With Old Month Data.xlsx]Company Data’!$A$1:M$30,12,11) THIS WORKS
Cell K14 = INDEX(‘C:\Users\Sandie\Documents\Adams\[AMC With Old Month Data.xlsx]Company Data’!$A$1:M$30,14,11) THIS WORKS
Cell K15 = INDEX(‘C:\Users\Sandie\Documents\Adams\[AMC With Old Month Data.xlsx]Company Data’!$A$1:M$30,15,11) THIS WORKS

For automatic input I have made:
Cell R12 = Company Data
Cell S12 = $A$1:$M$30 (range of Company Data Spreadsheet)


New Years Workbook (Automatic Input)
Company Data Worksheet
Cell K6 = (path to Last Year’s Workbook)
C:\Users\Sandie\Documents\Adams\[AMC With Old MonthData.xlsx]
Cell K12 = INDEX(“’”&$K$6&””&$R$12&”’!”&$S$12,12,11) Evaluates to #Value


When I Evaluate this formula, the steps are okay until here:
=INDEX(“‘C:\Users\Sandie\Documents\Adams\[AMC With Old Month Data.xlsx]Company Data’!”$A$1:M$30,12,11)

Next step causes the #Value error.

Typing that out I’ve noticed that there are quotation marks after the first bracket and exclamation mark, should they be there?. Perhaps that is the problem and if so how do I get rid of them or is there a far easier, simpler and more straight forward way to achieve what I want to do.

I am trying to steer clear of VBA and Macro's as I know even less about them

I am so sorry to be so long winded but I haven’t a clue how to post screen shots other than copying and pasting a .jpg directly into the text which I gather would be a no-no. Please let me know what I am doing wrong or a better way of doing it.

Many thanks for your patience

Martyn
A Very Frustrated Beginner

 

[mart37]

k12 = index(indirect("'"&$k$6&$r$12&"'"&"!"$s$12;12,11)

 

[Matt53]

Hi mart37,

Thanks for that and it works a treat but is there another way to get the data without having to open the old workbook? Also how can I make the cells with the transferred data only show the result and not the formula?

Sorry, as you can tell I am really overstretching myself here but hey, If at first you don't succeed.....

 

[Matt53]

Hi again,

I think I may have sorted it, although in a rather long winded way and there is probably a much more efficient way of doing this but:

Moved the formula's to what will be a hidden part of the worksheet then made each of the final data cells merge&centred across a couple of cells, recorded a macro to copy and paste the hidden data into the merged cells and it only copied the result not the formula. Created a button for the macro and Woo Hoo happy bunny!

I am sure you VBA guys are probably thinking WTH and if anybody can come up with a VERY easy to understand bit of code to do the same I would be eternally grateful

Thanks to mart37 and anybody who takes the time to read my posts

Martyn