Transfer data from survey tab to an input tab

Hi Guys,

I'm very new to VBA so I was wondering if you could help .

What I'm trying to do

- I'm trying to extract data from the Survey Tab to the input tab.

Rules


- extract data from column F, G and H in the survey tab then transfer this to column A,B and C in input form. For example, country data will populate the country tab in the input form.
- In the survey form extract text in the dark orange cell(s) and transfer into the relevant item column (input tab). For example, Café is added onto the united kingdom, london row. At the same time if united kingdom, london is repeated in the survey then create a new row for another 'items' and issues 'identified'. For example, we have two rows for Building and Café.
- I want to only add RAG status data from the Overall RAG:2 column in the survey tab. ignore the other RAG status column.
- i would like to ignore the first 5 columns on the survey tab. However everything else should be transfers into the input tab.


Example attached.

Thanks a lot!

Here's a possible start...

Please Login or Register  to view this content.

...but it isn't clear where you want the RAG2 columns to go on the Input form.

Thank you so much, this is definitely a great start. Regarding RAG2 in the survey tab I would like this transferred into the input tab. For example, united kingdom, london is amber therefore an orange 'A' should appear in the week 29-May since this is the latest week. At the same time United states, new york is green therefore a green G should appear in the input tab for the 29-May.

- I'm not sure how updating future weeks will work. For instance after the week of the 29th May is the 5th of June. I will add a new column in the input tab after each passing week. Is it possible to add a new condition so that excel picks up when we are on a new week? The data changed for that week will be the RAG status, items and issues identified.

thanks again

If you insert columns in the middle of the form, you will break the code. If you can, I would suggest adding a column for the date and one for the RAG2 code.

Thanks!

I'm not sure I'm following very well, apologies. You are saying I should create a new column in the survey tab titled date where i will have '29-May' in for instance. Then excel would be able to pick up data corresponding with the correct date?

See my attachment in post #4. I have one column with the name of the survey (from the worksheet's tab) and one with the first letter of the RAG2 code that is also conditionally formatted to change color.

I get it, thanks a lot. I'll try it out and get back you soon. It may take me a few tries!

Is it possible to adjust the code so that it is flexible enough to accept new columns in the survey related to the items and issues identified whilst picking up the rag status. Although there are 10 columns related to questions and answers minus the overall RAG (1), sometimes I can get surveys with additional columns.

Try it with this change...

Please Login or Register  to view this content.

Thank you so much, and finally in the event that they answered yes or no depending on the question. Is it possible to do the following:

- if 'No' is identified in the item cell (dark orange) whilst the question (light orange-right of each respective item cell) contains 'no' return data (in the question cell). For example, if cell I2 in the 29 May Survey tab contains 'No' and the question in J contains 'No' then return data (in the question cell). Otherwise if the item cell contains 'No' whilst the answered cell contains 'Yes', dont return data.

A bit confused... can you post a workbook depicting this along with a resulting input form?

Please see attached. In this case no data will be returned into the input form for 'canteen' and 'other'. In this case they answered the opposite of the question's criteria, no and yes respectively. Cafe would be returned because yes is in the question criteria. Office will be returned because no will be in the question criteria.
Hope this makes sense, thanks a lot!

Still confused...

Cafe would be returned because yes is in the question criteria.
Office will be returned because no will be in the question criteria.

Why would both a yes or a no be the criteria to return data?
...or are you trying to confuse those who fill out the surveys?

Let's try this...

Please Login or Register  to view this content.

Hey, I tried installing the new software. It doesn't seem to be working.

It works for me...

Sample 2 is close to the what I'm looking to achieve. However, when I changed column S to Yes, the item(Other) and issue identified (few pens) was not returned. Canteen/few spoons was not returned which is correct. I think a slight adjustment and the code would be perfect.

For the code to work, the yes and no placement needs to be consistent - all either under the "Item" or under the question

Third time's the charm...

Please Login or Register  to view this content.

Apologies again I'm having trouble making the code work, I'm a newbie at VBA. Could you please attach it to the workbook.

See attached...

The code is copied to a standard code module. Alt+F11 to open the VBA Editor, then from the VBA Editor Menu select Insert and then Module.

Its working very well! However when I changed L3 in survey to Yes the output did not fully work. Column F and G in the input tab were left empty. Could you please assist.

The associated question (M1) for L3 is looking for a "Yes", so it would not add "Office" to the form.

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