Search for a file and bring back data to master spreadsheet

Hi All,

To set up the problem, I have a folder that contains files that are all named numerically, ex. 08-100, 08-101, etc. Each file is identical in format but contains different data, ex. cell B1 is alway "material weight", cell B2 is always "estimated man hours" and new files are added weekly.

I am trying to set up a master spreadsheet that all I have to do is enter the file name (08-102) in the first column, and the second column will return the data in a specific cell of that file.

Thanks

Hi there,

Here's an approach that's fairly flexible and should get you started on the right track.

Enter data in worksheet cells as follows:

A2 The path to your data workbooks
B2 The name (including .xls) of the specific data workbook from which you wish to retrieve data
C2 The name of the worksheet from which you wish to retrieve data
D2 The address of the cell from which you wish to retrieve data

Now select the cell into which you wish the data to be retrieved and run the following code:

Please Login or Register  to view this content.

The selected cell should then display the appropriate data.

If this approach is suitable, it can be significantly enhanced, e.g. by providing workbook names, worksheet names etc. in dropdown lists in cells B2, C2 etc. above.

Hope this is helpful - please let me know how you get on.

Regards,

Greg M


P.S. Note to Moderator - it might be more appropriate to have this posted in the Programming group.

Greg,

I've set up two sample worksheets to see if I could make it work. Could you take a look at cell F7 in the "Job Cost Summary". I edited the code you wrote in your response using different cells so that they are in a row instead of a column so that I could eventually drag down, however, it appears that I didn't enter your code properly. I'm new at this type of programming.

Job Cost Summary.xls

08-100.xls

Thanks!!

Natasha

P.S. Also new to this forum so I hope the attachements work...

Hi Natasha,

I've made a change to your Job Summary Workbook and attached it.

The formula isn't entered directly into cell F7 by the user, it's entered using a VBA routine. The code I showed you has to be inserted in a VBA code module and then executed - I've done this and provided a "Retrieve Data" button, just click on it to execute the code and the data will be retrieved.

One point to note, in the workbook you posted, you had entered "Sheet 1" as the worksheet name - in fact, the actual name of the worksheet is "Sheet1" (without the blank space).

Now, as written, the application will retrieve data from only one cell which you specify. If you want to perform this action for several cells (i.e. to have several rows on your summary worksheet), give me an idea of what you're likely to require and I'll see what I can do.

Three questions:

Are all of the 08-100 series of workbooks located in the same folder?

Approximately how many workbooks are involved?

Would it be helpful to be able to refer to an 08-100 folder by a more user-friendly name (e.g. Maintenance, Stationery)?

Hope this helps - please let me know how you get on.

Regards,

Greg M

Greg,

Cool - love the button. My big problem is that I don't know VBA.

To answer your questions:

Yes, all the 08-100 series workbooks are in the same folder, as well as the summary workbook.

There wil be approximately 350+ workbooks involved - all of which are bids for services for 2008. (We had 321 bids for 2007 which is what I'm going by)

The folder that they are all in is called "2008 Bids".

Currently, the summary workbook named "BID LOG 2008" (which is also in the 2008 Bids folder), shows the essential data from each bid, but all the entries are manual. The BID LOG 2008 is not linked in any way to the 350+ bids. My goal is to be able to enter a new bid number in the BID LOG 2008 and have the essential data be retrieved automatically somehow. The purpose is to see at a glance all the current bids that have been submitted and how much they are for comparison. I'm attaching the current BID LOG 2008 so you can see the format and get a better idea of what I'm trying to achieve.

BID LOG 2008.zip


Thank,
Natasha

Hi Natasha,

Ok - I think I see where we need to go!

If I've understood correctly, all of the information for an individual bid (e.g. 08-100) in the BID LOG workbook comes from various cells in the 08-100 workbook, and you want to implement the Retrieve Data facility in the BID LOG workbook.

The only difficulty I'm having is that I can't see the correspondence between the data in the BID LOG entry for 08-100 and the data in the 08-100 workbook you sent me earlier. Maybe the workbook you sent wasn't one for a "real" bid? Can you let me have a typical bid workbook (one whose details are already included in the BID LOG workbook) so that I can see which cells need to be retrieved into the BID LOG workbook?

By the way, I'm assuming that the format of all individual bid workbooks is identical - i.e. that for example, cell B9 always contains the name of the supplier who submitted the bid. Is this assumption correct?

Regards,

Greg M

Greg,

That is correct, the previous bid 08-100 I uploaded earlier was just a demo. The actual bid files are rather large so I will attempt to reduce the size or zip it up or something so I can get it uploaded. The bid workbook contains 9 sheets, one being a summary sheet. Info from the summary sheet is then manaully entered into the BID LOG.

Thanks,
Natasha

Greg,

Here is an actual bid ...


08-126.zip


Thanks,
Natasha

Hi Natasha,

Try the attached workbook for size!

It retrieves Bid Date, Fabrication Cost, Erection Cost, Total Cost, Job Sq Ft, Job Structural Weight and Job Total Weight from the bid workbook.

Select either the Year cell or the Bid No cell (Column A or B) of the bid whose data you want to retrieve, and then press the Retrieve Bid Data button in the top left-hand corner of the worksheet. The above data will be retrieved from the selected bid file. One useful feature is that the cells containing the retrieved data are LINKED to the bid file, so if the data in the bid file are ever changed, the BID LOG workbook will reflect those changes automatically.

The way I checked the operation of the application was to take the bid file you sent me and rename it as 08-135, i.e. a bid no. shown in the BID LOG file but which has no data entered as yet. Then I selected either cell A15 or B15 (either "part" of the bid no.) and pressed the button.

I hope this helps - if there's anything else, let me know and I'll see what I can do.

Please let me know how you get on.

Regards,

Greg M

Greg,

Nevermind! I actually figured it out and added the Project Name and Estimator.

Genius! Again - thank you so much!

Natasha

Hi Natasha,

Feedback like yours is what makes it all worth while for us contributors! You're very welcome and I'm delighted you're so pleased with the workbook.

What's even better for me is the fact that you now understand the working of the application to the extent that you're able to make your own additions to it.

Please let me know if you need any further assistance with this or with other projects.

Nice to have met you, best regards,

Greg

Hi Again Greg,

So I have a what if for you (if you have time)...

What if I have the same scenario EXCEPT the data I am trying to retrieve isn't always in the same cell.

I thought I would try to set up the same data retrieval summary sheet, similar to the BID LOG, for jobs that have been completed that we have now have actual data, which can then be compared to the BID LOG. The problem is that the data that our accounting software program produces shows up in different cells. It's never consistent (and I've called their tech support line and they say they can't fix that).

I'm attaching some sample job costs and the beginning of the Job Cost Summary.

4754.xls

4875.xls

Job Cost Summary.xls

I figured out a way to use vlookup to find the correct cell that the data I want is in by nesting if and vlookup functions. But I'm at a loss on how to merge that with the VBA code you created because the cells had to be consistent.

Thanks,

Natasha

Hi Natasha,

Just got your latest post, but it's now 2:18 am - yawwwwwwwwwwwwwwnnnnnnnnnnn. I'll be going away for a few days but I'd hope to be able to look at it on Tuesday/Wednesday next. I see where you've used LOOKUP etc. to locate the values of the data you're interested in, but maybe if you used a slight modification to determine the ADDRESSES of the cells where the data are located we could plug those addresses into an equivalent of the method we used for the BID LOG workbook?

I'll have a look next week - I don't think it's an insurmountable problem - but if you discover anything in the meantime, please let me know.

By the way, does the inconsistency arise only with respect to the rows involved, or does the inconsistency extend also to the columns?

Best regards,

Greg M

Hi Natasha,

Following on from my last post, are the locations of the data you require always fixed with respect to a particular title cell? E.g. is the cell containing the Fabrication Cost always two cells to the right of a cell containing the text "Total Cost of Fabrication"?

Regards,

Greg M

Greg,

No problem, whenever you get a chance to look at it - it's an ongoing project of mine.

To answer your questions:
*both rows AND columns are inconsistent
*the locations of the data are NOT always fixed with respect to a particular title cell

I've looked at several different job cost outputs and here's what I could find that IS consistent.

The descriptions are always in column B and they are always in the same order from top to bottom starting in row 8.
***But they change rows based on what type of cost is entered on that job. For example, Fabrication Labor then Erection Labor then Materials-Steel & Misc will be in rows 8, 9, and 10 respectively for one project. However on the next, if there is no Erection Labor, Materials-Steel & Misc will move up to row 9.

Regarding the columns where the data is entered, again the order is always the same from left to right starting with the Description in column B, then comes Material Wt, Labor Hours and Actual Cost (in dollars).
***However, Material Wt, Labor Hours and Actual Cost are never in consistent columns, and sometimes column cells are merged so that although the data appears to be in adjoining column/cells, but in actuality they are separated by a column. But even this is inconsistent, sometimes the data is in adjoining columns, sometimes not. The only consistency is that Actual Cost will be in a column to the right of Material Wt or Labor Hours. And FYI, a Description will only have two data entries, Material Wt and Actual Cost, or Labor Hours and Actual Cost.

It was given these very few consistencies that I used a vlookup to find the description (ex. Fabrication Labor), then searched from left to right to find the Labor Hours, then right to left to find the Actual Cost. The problem lies in that the formulas I used are file specific to only one Job Cost report and I know I have to do something in VBA, similar to what you did previously, to find the correct Job Cost file for each new file added.

Pretty convoluted!

Thanks, and have a nice holiday,

Natasha

Greg,

I've attempted to start the VBA code using the one from the BID LOG to at least attempt to set it up. I may be completely off but thought I would give it a try. I've attached it on the off chance it helps in anyway...


Job Cost Summary.xls

Thanks,
Natasha

Greg,

Ignore the attachment in my last post - I have since modified the Job Cost Summary, added the code to retrieve the bid data which ALMOST works (it finds the correct path but gets hung up on finding the right file, I even made sure is was .xlsx and not .xls since the bid data is in MS2007, but to no avail),

and added my ATTEMPT at the code for retrieving the cost data which of course doesn't work.

So here it is for what it's worth...

Job Cost Summary.xls

Thanks,
Natasha

Greg,

I've done a little investigating while you were gone and it appears that there is no VLookup in VBA (?). I also bought some guides on VBA to help me understand the programming better. So I attempted to start the thought process of how the program needs to work like a nested IF/VLookup like I used before. In case it helps any, I'm attaching the flow charts (for what it's worth, maybe not much).

VLookup flow chart.doc

VLookup flow chart 2.doc

Thanks,
Natasha

Hi Natasha,

Thanks for your posts - this reply is just to let you know that I haven't forgotten about your request.

Things have been a bit crazy here, but I'm hoping to be able to take a look at your project on Tuesday/Wednesday.

Sorry for the lack of response from here.

Best regards,

Greg M

Hi Greg,

Thanks!! Just let me know if you need anything from me.

Natasha