Move between sheets & keep a running total

  • Thread starter Thread starter miker1999
  • Start date Start date
M

miker1999

Hello all,
I have a workbook with 4 sheets:
Sheet 1 = OPEN positions
Sheet 2 = just FILLED positions
Sheet 3 = current ACTIVE positions
Shhet 4 = Total # allowed for positions

I have a formula that adds the # in OPEN + # of current ACTIVE t
ensure that it equals # allowed. Once a position is filled, I move i
to FILLED (where I store my long list of every position ever filled).
How can I accomplish 2 things:

Move the entire row of data from OPEN sheet to FILLED sheet when
switch the data in column A (from 'OPEN' to 'FILLED')...is this
change event? I would like the data to move automatically and onl
past the values, not the formulas.

Second, I would then like to add '1' to the position in the Curren
ACTIVE sheet so that I can keep the running total.

Help...I am very stuck.
Thanks,
Mik
 
Maybe this formulas set-up would provide
some ideas as a possible alternative approach ..

Assume you have

In Sheet: All
---------------
("All" is a working sheet where you would list ALL positions,
whether "OPEN" or "FILLED", and update the flag "OPEN" to "FILLED",
or vice-versa accordingly from time-to-time)

Running down in say, cols A and B would be data such as ...

OPEN Post1
OPEN Post2
OPEN Post3
FILLED Post4
FILLED Post5
OPEN Post6
etc

In 2 empty cols to the right, say in cols F and G:

Put in F1: =IF(A1="OPEN",ROW(),"")
Put in G1: =IF(A1="FILLED",ROW(),"")

Select F1:G1 and copy down as many rows as there is data

In Sheet: Open (For "OPEN" positions)
------------------
Put in A1:

=IF(ISERROR(MATCH(SMALL(All!$F:$F,ROW()),All!$F:$F,0)),"",OFFSET(All!$A$1,MA
TCH(SMALL(All!$F:$F,ROW()),All!$F:$F,0)-1,COLUMN()-1))

Copy A1 across to B1**,
then copy down say, as many rows as there is data in "All"

**If you have more data cols than just cols A and B in Sheet "All",
just copy A1 across as many cols as there are data cols, then fill down

The above will auto-extract only all the "OPEN" positions from sheet "All"

In Sheet: Filled (For "FILLED" positions)
------------------
Likewise ..

Put in A1:

=IF(ISERROR(MATCH(SMALL(All!$G:$G,ROW()),All!$G:$G,0)),"",OFFSET(All!$A$1,MA
TCH(SMALL(All!$G:$G,ROW()),All!$G:$G,0)-1,COLUMN()-1))

Copy A1 across to B1,
then copy down say, as many rows as there is data in "All"

This will auto-extract only all the "FILLED" positions from sheet "All"
 
Maybe this formulas set-up would provide
some ideas as a possible alternative approach ..

Assume you have

In Sheet: All
---------------
("All" is a working sheet where you would list ALL positions,
whether "OPEN" or "FILLED", and update the flag "OPEN" to "FILLED",
or vice-versa accordingly from time-to-time)

Running down in say, cols A and B would be data such as ...

OPEN Post1
OPEN Post2
OPEN Post3
FILLED Post4
FILLED Post5
OPEN Post6
etc

In 2 empty cols to the right, say in cols F and G:

Put in F1: =IF(A1="OPEN",ROW(),"")
Put in G1: =IF(A1="FILLED",ROW(),"")

Select F1:G1 and copy down as many rows as there is data

In Sheet: Open (For "OPEN" positions)
------------------
Put in A1:

=IF(ISERROR(MATCH(SMALL(All!$F:$F,ROW()),All!$F:$F,0)),"",OFFSET(All!$A$1,MA
TCH(SMALL(All!$F:$F,ROW()),All!$F:$F,0)-1,COLUMN()-1))

Copy A1 across to B1**,
then copy down say, as many rows as there is data in "All"

**If you have more data cols than just cols A and B in Sheet "All",
just copy A1 across as many cols as there are data cols, then fill down

The above will auto-extract only all the "OPEN" positions from sheet "All"

In Sheet: Filled (For "FILLED" positions)
------------------
Likewise ..

Put in A1:

=IF(ISERROR(MATCH(SMALL(All!$G:$G,ROW()),All!$G:$G,0)),"",OFFSET(All!$A$1,MA
TCH(SMALL(All!$G:$G,ROW()),All!$G:$G,0)-1,COLUMN()-1))

Copy A1 across to B1,
then copy down say, as many rows as there is data in "All"

This will auto-extract only all the "FILLED" positions from sheet "All"
 
Back
Top