Optional transfer of data to other workbooks or sheets

hyd1956

New Member
Joined
Jun 26, 2020
Messages
41
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi

I'm new to this and I'm trying to figure out how to get data to transfer to other sheets or workbooks automatically, when certain criteria is met.

I'm able to pull information through using formula's to see what is currently held in any column based on an identifying cell. For example entering the User ID, shows me name, telephone number etc stored in the master file.

I want to be able choose whether to override those cells with new information, but also in some circumstances I may need to move information to a new rows on the master sheet. I think this can probably be done using code for a command button, but I'm lost on exactly how to achieve it. I'm hoping someone here can help please. The basic functions I want from the button is the following:

The button is disabled unless a name is entered in B1
If there is name entered in B1 and B2 is 0 then enable the button.

Clicking the button should look for the next account number in the Master sheet where the status is which has a status of "Available" and the end date is greater than todays date. If nothing is available return a message saying there are no account numbers available.

If an account number is "Available" and the end date is less than todays date, it should then move the account number to a new line below the existing records, generate a new reference number and insert the name, telephone number, email address and date. It should set the last change action to "Created Account" and set the status as "not available".
It should then return a confirmation the action is completed and provide the reference number and the end date to the user.

If B1is entered, B2 is equal to or greater than 1 (meaning there is a match and the status will be assigned or closed) and b5 is 0 then I want to ask a yes or no question
If no is clicked then it should return "You cannot proceed"
If Yes is clicked find the duplicate record only one will have a status of assigned that has an account number attached to it and check if the end date has passed. If the end date has not passed, change the original record to "Closed" whilst adding, the user ID, closed reason as "renewed", Last change reason as "chose to renew" and the date in last change date fields.
It should then generate a new line in master sheet, moving the previous account number down but creating a new reference number, add the customer name + telephone number, email address, add the status as assigned, the user ID, current date and last change reason as "new reference created for renewal".
The user should get confirmation its completed and be provided with reference number and the end date.

Examples of the user and master sheet

[
exceltest.xlsx
ABCDEFGHIJKLMNO
1Enter NameTestNameUser IDmyname
2Number of MPAN Entries3Datetoday Button - see sheet for furher details?
3Enter Phone Number123Add Email Addresstest@test.com
4CombinedTestName | 123
5Name + Phone numer Entries1
6
7
8Name + Phone Check
9Master Sheet Row numberAccount NumberReference NumberNameTelephoneName + Telephoneemail addressStatusend dateReason for changeLast ChangeDate Of Last ChangeLast Change Completed by
10
113123451113TestName123TestName | 123example@email.comAssigned 01/09/2020Requested00000
12#N/A              
13#N/A              
14#N/A              
15#N/A              
16#N/A              
17#N/A              
18000000000/01/1900000000
19Name Check
20Master Sheet Row numberAccount NumberReference NumberNameTelephoneName + Telephoneemail addressStatusendateReason for changeLast ChangeDate Of Last ChangeLast Change Completed by
21
22212341112TestName777122222TestName | 7771222220Assigned 01/09/2020No contactSet to available44009myname00
2361234561116TestName5433TestName | 54330Closed01/01/2020000000
24#N/A              
25#N/A              
26#N/A              
27#N/A              
28000000000/01/1900000000
Sheet2
Cell Formulas
RangeFormula
B2B2=COUNTIF(Sheet3!$C:$C,$B$1)
B4B4=$B$1&" | "&$B$3
B5B5=COUNTIF(Sheet3!$E:DF,$B$4)
B22:O28,B11:O18B11=IFERROR(INDEX(Sheet3!A:A,$A11),"")
A11:A17A11=MATCH($B$4,OFFSET(Sheet3!$E$1,$A10,,20000),0)+$A10
A22:A27A22=MATCH($B$1,OFFSET(Sheet3!$C$1,$A10,,20000),0)+$A10


exceltest.xlsx
ABCDEFGHIJKL
1Account NumberReference NumberNameTelephoneName + Telephoneemail addressStatusend dateReason for changeLast ChangeDate Of Last ChangeLast Change Completed by
212341112TestName777122222TestName | 777122222Assigned 01/09/2020No contactSet to available27/06/2020myname
3123451113TestName123TestName | 123example@email.comAssigned 01/09/2020Requested
4222221114 | available01/05/2020
5123451115 | available
61234561116TestName5433TestName | 5433Closed01/01/2020
7FALSE |
8FALSE |
9FALSE |
Sheet3
Cell Formulas
RangeFormula
B3:B9B3=IF(A3>0,$B2+1)
E2:E9E2=$C2&" | "&$D2



Thank you
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Watch MrExcel Video

Forum statistics

Threads
1,118,773
Messages
5,574,146
Members
412,574
Latest member
shadowfighter666
Top