# Auto-populating

#### xcellnoob

##### Board Regular
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:
Code:
``=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

##### New Member
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

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

#### MarkAndrews

##### Well-known Member
Personally, Id opt for the validation of East & West & use Query

But its worthwhile considering both options

#### buckley22

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

#### MarkAndrews

##### Well-known Member
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

#### xcellnoob

##### Board Regular
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

##### Board Regular
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

##### Board Regular
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

##### Board Regular
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

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

#### MarkAndrews

##### Well-known Member
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

Replies
0
Views
232
Replies
1
Views
117
Replies
1
Views
160
Replies
3
Views
232
Replies
1
Views
122

1,186,330
Messages
5,957,253
Members
438,295
Latest member
nm005

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back