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]

Sorry went off a little half ****ed - just couldn't believe I could help someone….though now wish I knew about MS query…..hehe

OK well I'll try and neaten it up a bit for you column A is just a list of numbers so in cell A1 just enter 1 in cell A2 enter 2 and so on. Use the autofill. Column B is where you enter whether or not it is East or west. This is a manual entry field where you will input data.

ok now enter the cells as follows. These aren’t formula's I'm just writing what each cell should contain.
C1:EAST,
D1:WEST,
C2:IF(C1="east",IF($B2="east",1,0),IF($B2="east",C1+1,C1)),
D2:IF(D1="west",IF($B2="west",1,0),IF($B2="west",D1+1,D1)) E2:HLOOKUP(B2,C:D,A2,FALSE) ,
F2:CONCATENATE(B2,E2)

Then use the autofill as needed.......everything should now look like this!

A B C D E F
1 East/west EAST WEST CODE CONCATENATE
2 East 1 0 1 East1
3 East 2 0 2 East2
4 west 2 1 1 west1
5 east 3 1 3 east3
6 west 3 2 2 west2
7 East 4 2 4 East4
8 East 5 2 5 East5
9 west 5 3 3 west3
10 west 5 4 4 west4
11 west 5 5 5 west5
12 East 6 5 6 East6
13 East 7 5 7 East7
14 west 7 6 6 west6
15 west 7 7 7 west7
16 west 7 8 8 west8
17 East 8 8 8 East8
18 East 9 8 9 East9
19 west 9 9 9 west9
20 East 10 9 10 East10
21 west 10 10 10 west10
22 west 10 11 11 west11


So now you have in column F a completely unique reference for each line of data dependant on the entry in the east/west cell. Now if you enter the rest of the data from column G onwards, whatever that may be, you have a lookup point for your individual sheets.

So in column A you would list all the East deliveries you would want to lookup (namely EAST1, EAST2, EAST3…etc) and Vlookup the rest of the data.
Of course it looks like you've sorted it using the admittedly better method….still I just wanted to prove my way works too…..if only for us muppets who are resricted to if functions….LOL

 

[MarkAndrews]

Sounds like MS Query may help you at some stage

It's worth trying to learn it from the internet (like i did) & use this board for help

Mark

 

[xcellnoob]

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

Does this disable the background refresh? I'm gonna' check it out, but just curious.

 

[MarkAndrews]

If you want to refresh your query using VBA

Replace "SQL Query" with the sheet name where your query is

Range A5 is the first line where the query starts

I hide all my query sheets too - what they dont see doesn't hurt them

    Sheets("SQL Query 2").Select
    Range("A5").Select
    Selection.QueryTable.Refresh BackgroundQuery:=False

 

[MarkAndrews]

Or you could Hide the sheet, then unhide (using VBA) then

ActiveWorkbook.RefreshAll

Then hide your sheets which contain the query

 

[xcellnoob]

Sounds good. One more quick question.....
If I have a sheet that contains an automatic refresh query that fills in say columns a,b,c. Then columns D through F are user inputs based on the info in the columns A-C. Is there any way to link those together so that when the query refreshes, it keeps the data together? Say that one of the columns (A) is a date and the user changes the date in the table the query is based on. That will realign the query because it is sorted on the date. So, is this possible? Did I even make sense?

 

[MarkAndrews]

I dont think it can be done, as the query is volatile (It changes every time)

What i would do is create another table, which contain the A B C info (i'm guessing these are names or account numbers), then put the user entry on this page, then on the query page enter a vlookup to populate this information in D E F

(Does this make sense)

If you > Edit Query on the Data > Get external data > edit query there is a box marked "Fill down Formuals adjacent to data, the vlookup will match the user inputs to these cells (where your copy of A B C is) when the query is refreshed (automatically) using your vlookups in D E F