Auto-populating

[xcellnoob]

Hey all,
Thanks to some help here, I was able to pull off a dynamic auto-sorting by date. My new task is to populate different sheets with the data. So, I have stores in the east and stores in the west. On the data entry page, the user enters the region. Based on that entry I want the data to be transferred to either the "East" tab or the "west" tab. I still want the info to autosort though.
The formula I'm using for the autosort is:

=IF(ISERROR(VLOOKUP(Inputs!M18,Inputs!$A$6:$Q$32,2,FALSE)),"",(IF(VLOOKUP(Inputs!M18,Inputs!$A$6:$Q$32,2,FALSE)=0,"",IF(N(Inputs!M18),VLOOKUP(Inputs!M18,Inputs!$A$6:$Q$32,2,FALSE),""))))

So, I am trying to put in another IF statement so that if the cell that holds east/west says east, then put that row on the east tab not the west tab. ANy suggestions?

 

[buckley22]

I've done something similar at work but sending it to different sheets for each vehicle in our fleet. My spreadsheet is about 20 mb's so this maybe isn't the tidiest way of doing things but it does work.

In a spare column do a running count down, effectively numbering each cell with the row number

Pick a few columns of free space. do a running count of the number of time east appears with a simple IF function. {IF(B2="EAST",C1+1,C1) where B2 is your east/west cell and the C column is your running count.}

Now do the same for "WEST" in column D

IN a new column (E) run a simple HLOOKUP to return the number of repetitions either EAST or WEST has had {HLOOKUP(A2,B:D,A2,FALSE)

Now in the next column concatenate those two columns {Concatenate(B2,E2)} this with create a completely unique reference for the east and west data.

Next simply run a VLookup searching for the appearance of the 1st east entry, then the second, third, fourth....etc {VLOOKUP(A1,E:X,x,FALSE) - where A1 is simply a list of EAST!, EAST2, EAST3, EAST4....etc and x is the data you want to return}

OK this may look insane but it does work. if you put this in the first 5 columns of your sheet, hide columns A,C,D,E & F, you now have an entry field at the start of your data list that will automatically send a VLOOKUP marker to sheets of your choosing. Like I said it works, I've split delivvery information over sheets by date and vehicle reg and trust me its a lot more complicated.

Any problems just say and I'll try and help as best I can

 

[MarkAndrews]

Personally, Id opt for the validation of East & West & use Query

But its worthwhile considering both options

 

[buckley22]

Problem is I have no idea what MS query is.....*grins*....hence why I have to go all round the houses!

 

[MarkAndrews]

Problem is I have no idea what MS query is.....*grins*....hence why I have to go all round the houses!

Its well worth learning

God bless Google...........

 

[xcellnoob]

Ok, I'm back.
Buckley, I like your approach. It sounds like you are as big a fan as I of Duct tape.
At any rate, Mark....
I still don't see how data validation makes the query dynamic? Can you elaborate a little.
I am currently working on it by building a table and using the database functions to return the data, but I am running into issues there as well.
Thanks for all of the replies and help!

 

[xcellnoob]

Pick a few columns of free space. do a running count of the number of time east appears with a simple IF function. {IF(B2="EAST",C1+1,C1) where B2 is your east/west cell and the C column is your running count.}

Where are you putting that formula? It's not an array formula is it? You started it with {. I'm confused.

 

[xcellnoob]

Alright. I really tried to implement your plan Buckley, but I am coming up really short.
Column A = Numbered list?
Column B = East/WEst?
Column C = Running total East
Column D = Running total West
Column E = ???? I don't completely understand your formula here. I get an NA error.
Column F = Concatenate ......
I am completely lost after this. Help?

 

[xcellnoob]

If you put in a validation & used the parameter, your page results would change to reflect East or West , from your validation selection box

Actually, this is pretty cool. I didn't even need to do that. I just had the query background refresh every 1 minute. This is pretty freakin sweet. Is there any way to go shorter than 1 minute?

 

[xcellnoob]

Actually, how can I get rid of the auto-refresh warning message when a user opens the file? Is this possible?

 

[MarkAndrews]

Actually, how can I get rid of the auto-refresh warning message when a user opens the file? Is this possible?

Edit Query on the Data > Get external data > edit query

Unclick Refresh query