Struggling to move & link data without access

**dave45** · 04-12-2012, 08:30 PM

Hi Im trying to do something that I could do easily in access but we can no longer use it where I am. So I can only use Excel & my brain doesnt seem to like it. Its to extract data from a survey

The full survey data would be in one sheet for example

name|age|hair|weight| ever seen UFO| where | how big
Dave| 23 |none|23.67| Y

The data will be updated each week in a master data sheet on a spreadsheet to a maximum of 30,000 rows but only an estimated 70 will choose Y for ever seen a UFO.
So I would want to have a formula that (in another sheet) copy's the full row of data if any cell in that column is Y.

I'm struggling with that

but if I could throw in something else I have added 2 blank row at the end. The perfect result for the survey would be if the the new sheet with only the Y rows showing would allow these blank cells to be updated & that new data to link back to these same cells in the master data so the master sheet can be analysed properly

Like I say it would be pretty simple in access but I'll open it up to you excel experts to show me Excel is equally up to the job
Thanks

**dangelor** · 04-15-2012, 08:02 AM

You could use Data|Advanced Filter to copy data to another sheet. Automating it isn't difficult...

Please Login or Register  to view this content.

**dave45** · 04-15-2012, 12:43 PM

Hi that looks good but I'm struggling on the criteria, if column D is the column that potential will have the Y in it, how do you write that as the criteria.

Thanks again I can think of many other things I should have used advanced filter for

**dave45** · 04-15-2012, 01:44 PM

It ok Ive worked that one out
It works but I dont think I can use it as the blank columns in the new filtered sheet would be filled in, as the data in the master data sheet is updated daily when the filter is re-run that data will be lost as it will bring back the blanks from the original. Is there any way to filter & link the cells automatically so the new filtered data when information is input in the blank cells updates the same blank cells in the master data. Sorry if that doesnt make sense.
It all needs to be automated as it will be run by guys with less excel knowledge than me.

Basically in access I would have appended the new data into a table with blank columns each day then put a query to say show those that have Y in that column but have blank columns at the end, have a form to update those columns then when the query is refreshed they will no longer be blank so will not show & the master data will be fully updated.
Its quite difficult to put this in excel terms as its not a really a database but I have to use it as one.

**bebo021999** · 04-15-2012, 01:45 PM

Try it in attachment.

**dave45** · 04-15-2012, 02:48 PM

Thats some formula youve got there.
but again it relies on the master data large list,as you have in sheet 1 being updated & then just being shown in the smaller list in sheet 2.

I would need something like you have here dragging the required data from sheet 1 into a smaller more manageable table sheet 2 but then that table allowing to be filled in & it be linked and able to update the cells in sheet 1 that are blank. As excel relies on 1 way linked formulas & not 2 way linked cells I dont think its up to the job.
I tried to use the Microsoft query in the get external data but it doesn't read it as a database or a table

Thanks anyway