Repeated set of 4 columns x320. Each set holds info on performing artists. Need to link

[Raymundo100]

I have a workbook database set up to input up to 320 artists details for a festival. Main information is contact details, performance day and time, and selections from a shopping list of 400 products for their accommodation.

I need to pull the details for each artist onto a sheet for each stage each day. 9 stages, 4 days max 14 artists per day per stage. I have linked all the individual details by setting a allocated number based on stage/day/slot e.g. Thursday has a value of .1, main stage a value of 1, slot one a value of 10 so it gives code 11.1. And then used HLOOKUP to pull the data from the relevant column.

Issue comes when I get to the shopping list. Of the 400 items on the list they may only have 10 items, maybe 50... for each artist I have input the quantity for each item they have requested. I used a separate column to give any item cell with input a value of 1, and another column to add the values of it's value and the cells above it in the list to give a count value for items added.

Now I need to link the stage play list sheets to draw just the shopping list for each artist. I have a reference for the artist, and have the columns with quantity to do an item count so can do IF/OR option for products. What I don't know how to do is run either a VLOOKUP or an INDEX given the starting column moves around the table depending on where the info is. I have tried to simplify it by adding in the starting cell reference to then input this into a formula but don't know how to.

For example I know that cell D39 is the top left cell of the first artists shopping list. If I could add this into a =INDEX formula then I could make it work. Any help appreciated!

Raymundo

[Pepe Le Mokko]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Raymundo100]

Hi Pepe,

Thanks for getting back to me. Please see attached.

Sheets:
Data - Drop down reference/info i need
INFO - How all artist data is input
Shopping List - Summary of total items across all lines
AL Summary template - Draft for first data pull sheet where I'm having the issue. Will need to duplicate this sheet into multiple days/stages. 4 days, 9 stages, 15 slots......

I added a note into the AL Summary sheet explaining the challenge!

Regards,

Raymundo

[Raymundo100]

Can't seam to upload file. Keeps coming back with an error. Might be too big. Will try and reduce size now

[Raymundo100]

Hope this works

[JeteMc]

If I understand correctly then it would seem as if the following could be pasted into cell B20 and copied down on the AL Sheet Format sheet:

Please Login or Register  to view this content.

If the formula doesn't display the expected results, (1,2,3 then blanks) then please update the file attached to post #5 with manually included expected results along with an explanation of why you expect those results.
Let us know if you have any questions.

[Raymundo100]

WOW! That is well beyond my skill set!

It is finding the number in the correct column but I need it to show the information to the right of the number. And then fro the cell to the right of this formula I need to repeat it showing the value 2 columns to the right. How can I add that in?

Thank you so much!!!!

[Raymundo100]

Here is the updated sheet with expected/hoped for results

[JeteMc]

Try pasting the following into cell B20, then drag the fill handle over to cell C20:

Please Login or Register  to view this content.

Let us know if you have any questions.

[Raymundo100]

Thank you so much that is working perfectly.

I've added in the full 320 artists with 4 columns each and it doesn't seam to work towards the end. Does the range in the formula need increasing. Is that the (624) number?

Thanks so much for this it's going to make life so much easier!

[Raymundo100]

Ignore me it works perfectly!!! Thanks so much

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.