Hey All,
What I am after is essentially something that will allow me to seperate out quotes into seperate spreadsheets based on the Region codes as in the attached document.
As someone generates a new quote number in the master sheet (they just manually enter the next number) then it assigns the entire data column to the correct worksheet in the next available row based on the region input which is either VIC, NSW, SA, etc.
Can someone help me here?
Cheers,
Brendan
wow you file is so big for no reason......
your backgroundworksheet should not be required
if you delete that sheet your file shrinks to 130kb instead of 6mb!
can you show an example of what you actually want done...your file is just a blank template i dont know what you want done where
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Hi,
what i am after is the sample entry data as attached to automatically copy/be linked into the appropriate worksheet tab underneath.
So when someone enters either VIC, SA, or other options, the data entered updates in quote numerical order based on ref number for each state...
have tried some stuff but getting nowhere. made it blank because i thought it would be easier.
Appreciate the reply.
Cheers,
Brendan
but also, the master data needs to stay as is in numerical order based on ref # as entered.
sorry missed updating the specific tabs. Please see the attached.
Thanks again.
ok your master sheet is where you enter the data manually (including your ref#) and you want each tab to draw off master sheet automatically?
Yep that's correct.
Cheers.
Brendan
ok
your code for region
will it be your sheet name?
ive only did the rows up to 50 to keep the size of the file smaller
i made it run off the sheet name to make the coding easier to create new sheets
you could probably make it a bit faster if you just have the code region somewhere on the sheet instead
instead of having
MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,100) in every single cell needlessly
the formula that drives everything is CSE formula
Formula:
Please Login or Register to view this content.
hey thanks for this, much appreciated.
Is there something i can do for if there are two revisions ie: rev a and rev b but same quote number? currently if i enter quote number 1234 for example then put rev a, the below enter same number with rev b, it changes both in the worksheets for rev b.
Wondering if there is something i can do to get around that?
Cheers
didnt know that was part of criteria but sounds plausible
Will have a look on monday
ive changed the structure a little bit
the main code has been moved out to helper column in V which you can hide
cell V1 now contained the sheetname formula
Formula:Please Login or Register to view this content.
cell V8 contained the workhorse of the helper column and ultimately how the whole sheet times in together
Formula:Please Login or Register to view this content.
returns the row reference from master sheet
regardless of ref# rev# etc, so you can have REV A B C D ETC it does not matter
all it does is count and return in sequence (very important it is in sequence in comparison to master sheet) based on Region only
ie if there are 10 VIC it will return 10 lines of VIC
1 to 10 in order of how it appears in master sheet
The INDEX formula
Formula:Please Login or Register to view this content.
runs against the header in row 7
bare this in mind when you are amending the sheet
all formulas have small arrays to keep the file size down
you may need to extend your real sheet to make it work for you
you can also make file smaller by deleting blank information for individual sheets
Hi, thanks for this.
Can i just drag all the formulas out for however many cells i require, or will i need to modify anything if i drag them out for say, 3000 cells?
Cheers.
sorry had to wait until i came back into office
For your master sheet
you can go down as many lines as you want no issues
on the state sheet
you need to amend the formula in column V
V1 does not need to change
from v8 down
Formula:Please Login or Register to view this content.
change A37 and D37 to how many rows you have used in your master sheet
you will also need to amend the index formula to extend the number of rows used in master sheet
Formula:Please Login or Register to view this content.
change 101 to 3000
alternatively used named range
Formula:Please Login or Register to view this content.
state = 'Master Sheet'!$A$5:$A$3000
Formula:Please Login or Register to view this content.
Msheet ='Master Sheet'!$A$4:$U$3000
replace 3000 in both to be how many rows you have filled in master sheet
Last edited by humdingaling; 08-10-2014 at 08:37 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks