Update Sheet Based on Data In Another Sheet

F

fergusga

New Member
Joined
Jan 6, 2021
Messages
6
Office Version
  1. 365
Platform
  1. MacOS
In my Excel file I have:

- Master Sheet. This is the sheet I am using for analysis, calculations, etc
- Salesforce Data. This is the output from Salesforce and this data changes frequently

I am looking for a way to:

1. Update the corresponding fields in the Master Sheet based on the Salesforce Datasheet each time the data changes. This would be driven by the field '18 Character ID [Acct]' which is the unique reference for a customer account where the account exists in both sheets

2. Where there is a new account in the Salesforce Datasheet that does not exist in the 'Master Sheet' then add that new row to the Master Sheet

The headers in the columns that need to be linked are the same in both sheets and will not change.

Is this possible?
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
Market Segment Analysis.xlsx
BCDEFGHIJKLMNOP
1Current CSMCurrent AM18 Character ID [Acct]Account IDAccount NameAccount ARR Currency Account ARR Historical Brand AffiliationMarket SegmentRevised Market SegmentRevenue Plus SegmentTierEmployee CountBilling State/ProvinceBilling Country
2A C 0013A00001gDtO9QAK0013A00001gDtO91USD$ 1ALarge EnterpriseLarge EnterpriseLarge EnterpriseTier 178000United Kingdom
3BD0013A00001gDtBMQA00013A00001gDtBM2USD$ 2BLarge EnterpriseLarge EnterpriseLarge EnterpriseTier 1260000United States
4A C 0013A00001gDv1jQAC0013A00001gDv1j3USD$ 3CEnterpriseEnterprise - UpperEnterprise - UpperTier 123000United States
5BD0013A00001RkHQkQAN0013A00001RkHQk4USD$ 4ALarge EnterpriseLarge EnterpriseLarge EnterpriseTier 1130000United States
6A C 0013000000VHCMQAA50013000000VHCMQ5USD$ 5BLarge EnterpriseLarge EnterpriseLarge EnterpriseTier 156000United States
7BD0013000000IbfbrAAB0013000000Ibfbr6USD$ 6CEnterpriseEnterprise - UpperEnterprise - UpperTier 19400United States
8A C 0013A00001gDtPnQAK0013A00001gDtPn7USD$ 7ALarge EnterpriseLarge EnterpriseLarge EnterpriseTier 1140000United States
9BD0013A00001gDtMSQA00013A00001gDtMS8USD$ 8BEnterpriseEnterprise - UpperEnterprise - UpperTier 114000United States
10A C 0013000000WFIRGAA50013000000WFIRG9USD$ 9CEnterpriseEnterprise - LowerEnterprise - LowerTier 12100United States
Master Sheet
Cell Formulas
RangeFormula
K2:K10K2=IF([@[Employee Count]]<500,"SMB", IF([@[Employee Count]]<2000,"Mid-Market", IF([@[Employee Count]]<6000,"Enterprise - Lower", IF([@[Employee Count]]<25000,"Enterprise - Upper",IF([@[Employee Count]]>=25000,"Large Enterprise",NIL)))))
L2:L10L2= IF(OR([@[Employee Count]]>=25000,[@[Account ARR]] >=250000),"Large Enterprise", IF(OR([@[Employee Count]]>=6000,[@[Account ARR]] >=175000),"Enterprise - Upper", IF(OR([@[Employee Count]]>=2000,[@[Account ARR]] >=100000),"Enterprise - Lower", IF(OR([@[Employee Count]]>=500,[@[Account ARR]] >=50000),"Mid-Market", IF(OR([@[Employee Count]]<500,[@[Account ARR]] <50000),"SMB", "NIL")))))
 
Upvote 0
Market Segment Analysis.xlsx
ABCDEFGHIJKLMNOPQRSTU
1Parent AccountCustomer Success ManagerAccount Owner18 Character ID [Acct]Account IDAccount NameAccount ARR CurrencyAccount ARRHistorical Brand AffiliationMarket SegmentAccount Management TierEmployee CountBilling State/ProvinceBilling CountryIndustryExecutivte AlignmentTechnical Team RatingEnd User AssessmentManagement StrengthCustomer Satisfaction LevelRisk Status
2A D0013A00001VTzszQAD0013A00001VTzszGUSD9792SMBTier 4230United StatesManufacturing
3BE0013A00001QJyWJQA10013A00001QJyWJHUSD0EnterpriseTier 43000United StatesHealthcare
4CF0013000000auO59AAE0013000000auO59I USD51118.2Mid-MarketTier 3660United StatesInformation Technology
5A D0013A00001b4UsQQAU0013A00001b4UsQGUSD11900Mid-MarketTier 4500United StatesConstruction
6BE0014V00001j0ae2QAA0014V00001j0ae2HUSD6500SMBTier 415United StatesInformation Technology
7CF0013000001KyyoRAAR0013000001KyyoRI USD8000SMBTier 4110United StatesInformation Technology
8A D0013000001Kz306AAB0013000001Kz306GUSD13880SMBTier 4180United StatesManufacturing
9BE0013000000juo6bAAA0013000000juo6bHUSD6660SMBTier 450United StatesFinancial Services
10CF0013A00001gENffQAG0013A00001gENffI USD6000SMBTier 420United StatesInformation Technology
112A D0013A00001XYdErQAL0013A00001XYdErGUSD0EnterpriseTier 13100SpainConstruction
12BE0013A00001gDrFgQAK0013A00001gDrFgHUSD150000Large EnterpriseTier 4260000United StatesFinancial Services
13CF0013A00001hdVP0QAM0013A00001hdVP0I USD46000Mid-MarketTier 31500United StatesProfessional Services
14A D0013A00001hdTx8QAE0013A00001hdTx8GUSD6500SMBTier 4100USA
15BE0013A00001hdaokQAA0013A00001hdaokHUSD18000SMBTier 3425United StatesHealthcare
16CF0013A00001hdU23QAE0013A00001hdU23I USD7000SMBTier 45United StatesInformation Technology
17A D0013A00001hdcXHQAY0013A00001hdcXHGUSD36000Mid-MarketTier 31999United StatesHealthcare
181BE0014V00001iItmNQAS0014V00001iItmNHUSD22500Mid-MarketTier 31100United StatesHealthcare
19CF0014V00001hg9csQAA0014V00001hg9csI USD7000SMBTier 440United StatesHealthcare
20A D0014V00001hh6CtQAI0014V00001hh6CtGUSD24375Mid-MarketTier 31400United StatesRetail
21BE0014V00001iPE4LQAW0014V00001iPE4LHUSD0Mid-MarketTier 41999CanadaFinancial Services
22CF0014V00001iPDpLQAW0014V00001iPDpLI USD15500SMBTier 4499United StatesInformation Technology
23A D0014V00001iPEw7QAG0014V00001iPEw7GUSD8100SMBTier 4100United StatesProfessional Services
24BE0014V00001iT1hyQAC0014V00001iT1hyHUSD0Mid-MarketTier 41250United StatesHospitality
Salesforce Data
 
Upvote 0
Market Segment Analysis.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAO
1Parent AccountCurrent CSMCurrent AM18 Character ID [Acct]Account IDAccount NameAccount ARR Currency Account ARR Historical Brand AffiliationMarket SegmentRevised Market SegmentRevenue Plus SegmentTierEmployee CountBilling State/ProvinceBilling CountryIndustryExecutive AlignmentTechnical Team RatingEnd User AssessmentManagement StrengthCustomer Satisfaction LevelRisk StatusExec Alignment ScoreTechnical ScoreEnd User ScoreManagement ScoreCSAT ScoreRisk Status ScoreTier Change?US State CodeUS Time Zone Whitespace Potential Value Risk ScoreAccount CategoryProposed CSMProposed AMCSM ChangeAM ChangeNotes
2A C 0013A00001gDtO9QAK0013A00001gDtO91USD$ 1ALarge EnterpriseLarge EnterpriseLarge EnterpriseTier 178000United KingdomNO CHANGE#N/A#N/A0.00Maintain AccountEXCHANGECHANGE
3BD0013A00001gDtBMQA00013A00001gDtBM2USD$ 2BLarge EnterpriseLarge EnterpriseLarge EnterpriseTier 1260000United StatesNO CHANGE#N/A#N/A0.00Maintain AccountFYCHANGECHANGE
4A C 0013A00001gDv1jQAC0013A00001gDv1j3USD$ 3CEnterpriseEnterprise - UpperEnterprise - UpperTier 123000United StatesCHANGE#N/A#N/A0.00Maintain AccountGZCHANGECHANGE
5BD0013A00001RkHQkQAN0013A00001RkHQk4USD$ 4ALarge EnterpriseLarge EnterpriseLarge EnterpriseTier 1130000United StatesNO CHANGE#N/A#N/A0.00Maintain AccountEXCHANGECHANGE
6A C 0013000000VHCMQAA50013000000VHCMQ5USD$ 5BLarge EnterpriseLarge EnterpriseLarge EnterpriseTier 156000United StatesNO CHANGE#N/A#N/A0.00Maintain AccountFYCHANGECHANGE
7BD0013000000IbfbrAAB0013000000Ibfbr6USD$ 6CEnterpriseEnterprise - UpperEnterprise - UpperTier 19400United StatesCHANGE#N/A#N/A0.00Maintain AccountGZCHANGECHANGE
8A C 0013A00001gDtPnQAK0013A00001gDtPn7USD$ 7ALarge EnterpriseLarge EnterpriseLarge EnterpriseTier 1140000United StatesNO CHANGE#N/A#N/A0.00Maintain AccountEXCHANGECHANGE
9BD0013A00001gDtMSQA00013A00001gDtMS8USD$ 8BEnterpriseEnterprise - UpperEnterprise - UpperTier 114000United StatesCHANGE#N/A#N/A0.00Maintain AccountFYCHANGECHANGE
10ParnerA C 0013000000WFIRGAA50013000000WFIRG9USD$ 9CEnterpriseEnterprise - LowerEnterprise - LowerTier 12100United StatesCHANGE#N/A#N/A0.00Maintain AccountGZCHANGECHANGE
Master Sheet
Cell Formulas
RangeFormula
K2:K10K2=IF([@[Employee Count]]<500,"SMB", IF([@[Employee Count]]<2000,"Mid-Market", IF([@[Employee Count]]<6000,"Enterprise - Lower", IF([@[Employee Count]]<25000,"Enterprise - Upper",IF([@[Employee Count]]>=25000,"Large Enterprise",NIL)))))
L2:L10L2= IF(OR([@[Employee Count]]>=25000,[@[Account ARR]] >=250000),"Large Enterprise", IF(OR([@[Employee Count]]>=6000,[@[Account ARR]] >=175000),"Enterprise - Upper", IF(OR([@[Employee Count]]>=2000,[@[Account ARR]] >=100000),"Enterprise - Lower", IF(OR([@[Employee Count]]>=500,[@[Account ARR]] >=50000),"Mid-Market", IF(OR([@[Employee Count]]<500,[@[Account ARR]] <50000),"SMB", "NIL")))))
AD2:AD10AD2= IF(AND(K2="Large Enterprise",M2="Tier 1"),"NO CHANGE", IF(AND(K2="Upper Enterprise",M2="Tier 2"),"NO CHANGE", IF(AND(K2="Lower Enterprise",M2="Tier 2"),"NO CHANGE",IF(AND(K2="Mid-Market",M2="Tier 3a"),"NO CHANGE",IF(AND(K2="Mid-Market",M2="Tier 3b"),"NO CHANGE",IF(AND(K2="SMB",M2="Tier 4"),"NO CHANGE", "CHANGE"))))))
AE2:AE10AE2=VLOOKUP(O2,'US State Data'!$A$1:$C$112,3,FALSE)
AF2:AF10AF2=IF(AE2="WA","Pacific",IF(AE2="OR","Pacific",IF(AE2="CA","Pacific",IF(AE2="NV","Pacific",IF(AE2="MT","Mountain",IF(AE2="ID","Mountain",IF(AE2="WY","Mountain",IF(AE2="UT","Mountain",IF(AE2="CO","Mountain",IF(AE2="AZ","Mountain",IF(AE2="NM","Mountain",IF(AE2="ND","Central",IF(AE2="MN","Central",IF(AE2="WI","Central",IF(AE2="SD","Central",IF(AE2="NE","Central",IF(AE2="IA","Central",IF(AE2="KS","Central",IF(AE2="MO","Central",IF(AE2="IL","Central",IF(AE2="OK","Central",IF(AE2="AR","Central",IF(AE2="TX","Central",IF(AE2="LA","Central",IF(AE2="MS","Central",IF(AE2="AL","Central",IF(AE2="FL","Eastern",IF(AE2="GA","Eastern",IF(AE2="SC","Eastern",IF(AE2="NC","Eastern",IF(AE2="TN","Eastern",IF(AE2="KY","Eastern",IF(AE2="VA","Eastern",IF(AE2="WV","Eastern",IF(AE2="IN","Eastern",IF(AE2="OH","Eastern",IF(AE2="PA","Eastern",IF(AE2="MI","Eastern",IF(AE2="NY","Eastern",IF(AE2="VT","Eastern",IF(AE2="ME","Eastern",IF(AE2="NH","Eastern",IF(AE2="MA","Eastern",IF(AE2="RI","Eastern",IF(AE2="CT","Eastern",IF(AE2="NJ","Eastern",IF(AE2="DE","Eastern",IF(AE2="MD","Eastern",IF(AE2="DC","Eastern","None")))))))))))))))))))))))))))))))))))))))))))))))))
AI2:AI10AI2=SUM(Table1[@[Exec Alignment Score]:[Risk Status Score]])/30*100
AJ2:AJ10AJ2=IF(( [@[Potential Value]]>10000),"Expand Account", IF(AND([@[Risk Score]] >50, [@[Potential Value]]<10000),"Retain Account", IF(AND([@[Risk Score]] <=50, [@[Potential Value]]<10000),"Maintain Account", NA() )))
AM2:AN10AM2=IF(AK2=B2,"NO CHANGE","CHANGE")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AM:ANCell Value="NO CHANGE"textNO
AM:ANCell Value="CHANGE"textNO
 
Upvote 0
Apologies, the last two posts of xl2bb are correct, one for each sheet
 
Upvote 0
You must log in or register to reply here.

Similar threads

Kaz09
  • Question
Copy rows from multiple sheets based on data
Replies
1
Views
137
Fluff
Fluff
D
  • Question
Update row data using another worksheet data
Replies
1
Views
144
denz0215
D
B
  • Question
Macro that splits a Master data workbook into multiple workbooks
Replies
0
Views
566
Bobstar
B
H
  • Question
Update master sheet automatically when changes are made to split sheet VBA
Replies
0
Views
317
helplessnoobatexcel
H
I
  • Question
VBA - Copy Sheet from a workbook saved as a date - paste to specifically named sheet in another workbook based on the date
Replies
2
Views
199
Insurance Dave
I

Forum statistics

Threads
1,214,559
Messages
6,120,194
Members
448,951
Latest member
jennlynn

Share this page

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
Back
Top