Auto-populating

xcellnoob

Board Regular
Joined
Apr 6, 2005
Messages
188
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
Joined
Oct 16, 2006
Messages
9
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
 
Upvote 0

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
Joined
May 2, 2006
Messages
1,963
Personally, Id opt for the validation of East & West & use Query

But its worthwhile considering both options
 
Upvote 0

xcellnoob

Board Regular
Joined
Apr 6, 2005
Messages
188
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!
 
Upvote 0

xcellnoob

Board Regular
Joined
Apr 6, 2005
Messages
188
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.
 
Upvote 0

xcellnoob

Board Regular
Joined
Apr 6, 2005
Messages
188
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?
 
Upvote 0

xcellnoob

Board Regular
Joined
Apr 6, 2005
Messages
188
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?
 
Upvote 0

xcellnoob

Board Regular
Joined
Apr 6, 2005
Messages
188
Actually, how can I get rid of the auto-refresh warning message when a user opens the file? Is this possible?
 
Upvote 0

MarkAndrews

Well-known Member
Joined
May 2, 2006
Messages
1,963
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
 
Upvote 0

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top