Sourcing data from another excel workbook

[arunj]

Every week I generate a consolidated inventory report which is formatted as displayed in the attachment with file name "datasourceformat.xls". Is it possible to create a worksheet that I can direct to source the data from the current weeks consolidated inventory report file and generate the data found in column A and column Y in the attachment with file name "ConsolidatedInventoryUsage-1.xlsx"?

[Richard Buttrey]

Hi,

Apart from the format/layout the two sheets are essentially the same.
Can you explain the overall process from first principles. i.e. what you start with and what the end goal is and explain how you currently get from one to the other. Don't automatically assume that the current method is the best way. It may be but there are often different approaches in Excel.

AT the moment I'm assuming you start with the Consolidated Inventory workbook and end up with the datasourceformat workbook.
If so the first question is, why not use an additional sheet in the consolidated Inventory Workbook to show the stuff you currently have in the datasource workbook?

The next question is where does the Consolidated Inventory come from. Is it derived from some back office system which churns out a csv file that you import into Excel?

[arunj]

Hi,

Thank you for the reply. So the back office system generates a file that I then export to excel which becomes the datasourceformat.xls. I am starting with datasourceformat.xls NOT consolidated inventory usage. I think the names I gave the files was misleading. Sorry.

[arunj]

So the process I am trying to automate is as follows:
1. Selecting higher of actual or ideal usage and multiplying this by 1.3 and then subtracting the ending inventory (refer to cell W7 in consolidatedinventoryusage-1).
2. Divide this number by the pack size of the item (refer to cell X7 in consolidatedinventoryusage-1) using the index in column Z and AA.
3. If x7 is negative then I don't have to order the item so Y7=0. If X7 is positive I need to order the item so Y7 is equal to X7 rounded up to the nearest whole number.

[arunj]

So what I require is a worksheet that I can direct to the current weeks datasourceformat.xls file to generate the data in column Y of ConsolidatedInventoryUsage-1.

[Richard Buttrey]

Hi,

I still don't understand why you need a second workbook since it'e essentially the same as the datasource you start with but with added columns. Why not just add the additional columns to the datasource?

And thinking ahead when you may want to derive other analysis have you considered first getting the data into a normalised 2 dimensional database table where each column field is a UNIQUE TYPE of thing.

For instance you seem to have blocks of data one for each Team Member. A normalised database would have a column for 'Team Member' which would contain the names, along with all the other columns you currently have - plus of course the new ones that don't have data in the back office system. And if you ever wanted to analyse statistics by Date then you'd have an additional column for the Date.

Is it possible to get a flat file of data out of the back office system. The layout you currently have looks more like a .prn Print file. Most self-respecting office systems allow users to define the format they would like to export. Even if your office system can't do this it would pay dividends to have a pre-processing macro that takes the current layout and builds the flat file.

[arunj]

The datasource spreadsheet I start with will contain different data on a week to week basis. Only the format will remain the same. So I need a generic spreadsheet template which I can direct to the current weeks datasource spreadsheet file to pull the data I need to make my calculations.

[arunj]

I am trying to do this to organise the information more effectively. The original datasource spreadsheet contains some information that is not required and some information which is required for calculations but is not required for presentation. So I am trying to eliminate this information from the final file I need to present.

[Richard Buttrey]

I understand that but if the format remains the same it seems to me that all you need is a blank template workbook that just contains the macros to perform the task.

The macros would prompt you to first select the current week's file with a standard Windows File Open dialog window. Once the file was selected the macros would then add any additional/calculated information and delete any stuff that's not wanted, leaving you with what I think you you currently describe as the consolidated workbook.

Is this not the simplest way to end up with what you want rather than trying to fit your original data into an existing layout?

[arunj]

Yes. I think what you described is what I am trying to achieve. So, I need a workbook that let me select the current weeks datasource file and then I only need the information from column A and column Y displayed in the consolidated workbook.

[Richard Buttrey]

So just to clarify are you saying that you end up with just two columns, the existing A and the calculated Y column which will be a column B if all the other columns are deleted?

[arunj]

Yes. That is correct.

[Richard Buttrey]

Hi,

See if the attached works for you. Maintain the pack size details on sheet 2

[arunj]

When I try to download the file it opens a new browsers with the heading untitled2 and displays a blank screen and no url.

[arunj]

I managed to open the file. It does exactly what I required. Thank you. I will test with another set of data tonight and let you know how it goes. Thank you.

[arunj]

I updated the pack size data but now when I apply the macro it only applies for items in the beginning of the pack size data list. So for example item 4295 Box pizza large does not return a result. Can you explain how I can extend the range? So if I need to edit the pack size data information in the future I will be able to do so.

I am using the attached file stockordetemplate to source the data from the file datasourceformat.

[Richard Buttrey]

Hi,

There is a range name called 'PackSize' which is currently defined as C4:D83. You should change that to C4:D180. Or to give yourself some headroom define it to be as large as you think you'll ever need. e.g C4:D300 or whatever.

Interestingly I originally defined the name as a dynamic one using the OFFSET function so that the packsize name would automatically adjust as you entered new data. However I found that because the relevant formula is copied from the template workbook to the data source workbook the packsize range name does not carry over. That's something of which I wasn't aware so I've learned something too.

The other solution would be to copy the data source details into the template sheet and then run the macro. That would allow the dynamic range name to be used and the results would be in the template workbook.

[arunj]

Where can I change the definition of the range 'PackSize'?

[arunj]

I found the function that allows me to change the range. Thank you.

[Richard Buttrey]

Hi...
Formulas....Name Manager and select the PackSize name.

[arunj]

Can you edit the macro so that the following two conditions apply in the workbook produced from applying the macro?

1. If cell A7 contains an item number from the pack size range and cell B7 does not have a value then let cell B7 equal zero.
2. If a cell in column C contains any value then highlight that cell.

I am using the updated attached file stockordetemplate to source the data from the file datasourceformat.

[Richard Buttrey]

The original requirement was to show just 2 columns, column A and the Pack Size

Are you now saying that you require 3 columns to include the current Column C?
On the data source file you're using there is only one value in column C - the text "Consolidated Summary" in C303. Where would there be other cells with a value in column C?

[arunj]

In the file that is created by applying the macro I want to input data into column C after the file is generated. The purpose is as a manual override. So if in column B it is calculated that I need to order 3 of an item and I however want to order more or less I want to input that data into column C. So, when I input that data I want the cell to be highlighted to draw attention to the fact that the amount to order is different to the calculated amount.

[arunj]

I have attached a file that was generated from running the macro. In cell B23 and C27 I have demonstrated what I require the two conditions to achieve.

[Richard Buttrey]

Doesn't the fact that you enter a number in column C uniquely identify that this item is different to that calculated. I don't see how highlighting it makes any difference.

[arunj]

The purpose of highlighting it is to ensure that the person inputting the stock order into the system does not accidentally input the calculated amount over the manually determined amount. As you say the fact that there is a value in column C should indicate there is a manual override and thus this is the correct amount. However the purpose of highlighting was as a safeguard to draw the attention of the inputter.

[Richard Buttrey]

Hi,

See attached

[arunj]

Thank you. The file you provided satisfies the second condition I described.

However the first condition is not applied.

1. If cell A7 contains an item number from the pack size range and cell B7 does not have a value then let cell B7 equal zero.

In the attached file generated from running the macro I have highlighted the row with item 1924 Basil.
Since Cell A75 contains an item number from the pack list (i.e. 1924) and cell B75 has no value let B75=0.

Could you edit the workbook so it satisfies this function also? Thank you.

[arunj]

I had the opportunity to test the workbook with another set of data tonight and it worked exactly as desired. Thank you Richard. This workbook could potentially save me 75 hours of work every year.

Could you also help me integrate the condition described in the previous post into the workbook? Thank you again.

[Richard Buttrey]

Substitute the formula in X1 of the template with this one

Please Login or Register  to view this content.

[arunj]

Thank you! The formula you provided worked perfectly. You have saved me and potentially others a lot of time.

I have further questions regarding formatting but as they are a bit of a deviation from the title of this thread I will start a new thread.

How do I mark this thread as solved?