Data call function? Not sure...

I don't know what you mean by a "data call type function". Attach a sample workbook and explain what you would like to do with the data (e.g. have a BEFORE and AFTER sheet that shows clearly what you want to achieve).

Pete

Ok I had to copy paste to do this but I'm looking for a way to do this without copy pasting. The first five sheets are for separate projects, the sixth is where I'd like to see all the sheets consolidated so that I can sort them by vendor to make ordering easier on myself. Thanks.

Ordering Worksheet.xlsx

Okay, I'm just about to go out, but I'll check it out later on.

Pete

See attached file for a formula-based solution.

In each of the 5 subsidiary files, I have used column H as a helper column (coloured blue), with this formula in H4:

Formula:

Please Login or Register  to view this content.

When this is copied down, it sets up a sequential numbering of the records. Hyphens are used to indicate how far it has been copied (to row 24 in each sheet), so you can easily copy this down further in each sheet if necessary. Cell H1 in the first sheet is set to zero, but H1 in the other sheets looks back at the previous sheet to find the last number used, with a formula like this (in the second sheet):

Formula:

Please Login or Register  to view this content.

so in effect those sequential numbers run in sequence through the 5 sheets.

In the Master sheet I have listed the five sheet names in column K, and summarised the last number used in each sheet in column L - note that cell L2 must be set to zero. Then this formula in H4 of the Master sheet:

Formula:

Please Login or Register  to view this content.

gives the sheet name, and this one in I4:

Formula:

Please Login or Register  to view this content.

gives the row number of where each sequentially-numbered record can be found. It is then quite straightforward to retrieve the data in column A of the appropriate sheet, using this formula in A4:

Formula:

Please Login or Register  to view this content.

Note that some column A and some column D entries are missing in the subsidiary sheets, hence the need to test for IF(INDEX() = "" ...

Similar formula in columns B to F retrieve data from the appropriate columns of the sheet and row given in columns H and I. The formulae from A4 to I4 can then be copied down as far as is required (row 46 in the example file, though you can copy further if you have more data) - the hyphens in column I help to show how far.

Note that this is fully self-adjusting, so if you add a new record to the first sheet, say, then all the records on the Master sheet will move down automatically in order to accommodate it.

If you are satisfied that the formulae have been copied down far enough to accommodate future data, then you can hide all the helper columns (blue). I've left your Sheet6 in the file so you can check the results, but this can be deleted when you are satisfied that the file does what it is meant to do.

Hope this helps.

Pete

This works pretty elegantly. Thanks for your help on this Pete. I was not expecting this much help, perhaps a friendly pointer to info to teach myself so again a big Thank You.

Well, you took the trouble to attach a workbook, so I was able to test out the formulae, and as I then had a working model it was easy enough to attach it back to you. Glad to be able to help, and I hope you can follow the logic of the formulae.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, I would like to inform you that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete