If a cell contains Text, return a money value example 300 in next coloum

D

DarrenBurke

New Member
Joined
May 6, 2022
Messages
29
Office Version
  1. 2016
  2. 2007
Platform
  1. Windows
Good Morning All.

I have this sheet, Fundraisers, I am trying to give a cost of making the snack hamper.
I have applied conditional formatting to coloum J to only show Snack hampers , i have conditional formatted other text using 3 semi colon in format ;;; this hides the contents of the cells that dont have snack hampers

I have a sheet called Data. ( I use for creating dropdown list) In the sheet i have a named range called "SnaHamp" all the different types of snack hampers are in the named range

The range of hampers all cost 300 to make, these are then sent to customers that have made a donation to our care centre for abused women and children as a Thank You.

What i am try to do ... If coloum J6 to J144 contains text for the hampers (named range is SnaHamp) then K6 to K144 should return a cost to make this of "300"

Attached is a small l2bb and a snapshot of the named range on my Data sheet

I hope its a clear enough description :)
Thank you and God bless

GetdatafromFundRaisers1.xlsm
BCDEFGHIJK
5DATEREFNOCANVASSEDCOLLECTEDCOMMBTFCUSTOMERHAMPERH/Cost
607-JulMF355ZAMBESI GLASS&ALUMINIUM
706-JulMF354R 550,00R 550,00R 550,00CASHJMR TRANSPORT SOLUTIONS(PTY)LTD
806-JulMF351R 200,00R 200,00R 200,00CASHGRAYTEX METALS
906-JulMF352R 100,00R 100,00R 100,00CASHCAR CLINIC
1006-JulMF353R 550,00R 550,00R 550,00CASHB&R MOTOR ENGINEERING
1105-JulMF344R 550,00R 550,00R 550,00CASHTOTAL CAMPBELLS
1205-JulMF350CASHAUDREY PETZERFOOD PARCELL
1304-JulMF349R 100,00R 100,00R 100,00CASHCARLOS DA COSTA
1404-JulMF345R 200,00R 200,00R 200,00CASHLANDYTECH
1504-JulMF346R 200,00R 200,00R 200,00CASHBUSSINES EXPRESS
1628-JunMF333R 500,00R 500,00R 500,00CASHMUGG AND BEAN
1728-JunMF334R 100,00R 100,00R 100,00CASHM & M HYDRAULICS CC
1823-JunMF328R 1 000,00R 1 000,00R 1 000,00CASHLINDSAY SAKER VW ALBERTONBILTONG /RED WINE
1922-JunMF324R 200,00R 200,00R 200,00CASHDP WOODWORKS
2022-JunMF325R 400,00R 400,00R 400,00CASHMAINLY GOLF (PTY) LTD
2122-JunMF326R 1 000,00R 1 000,00R 1 000,00EFTFEDERAL CLEARING&FORWARDING COMPANYBILTONG/FRUIT JUICE
2222-JunMF327R 600,00R 600,00R 600,00WIMPY ARCON PARK
2322-JunMF321R 100,00R 100,00R 100,00CASHATTACK GLAS WERKE
2422-JunMF322R 600,00R 600,00R 600,00CASHNOBLE MOTOR AND CONTROL
2522-JunMF323R 600,00R 600,00R 600,00CASHJMR TRANSPORT SOLUTIONS(PTY)LTDBILTONG/WINE
2621-JunMF1R 200,00R 200,00R 200,00EFTNORTH LAWNMOWERS
2721-JunMF1R 500,00R 500,00R 500,00EFTMARK MELTZER
2815-JunMF286R 200,00R 200,00R 200,00CASHWAYNE ROUX
2914-JunMF1FAME FENCE(PTY)LTD
3014-JunMF318R 200,00R 200,00R 200,00CASHNORTH LAWNMOWERS
3113-JunMF316R 200,00R 200,00R 200,00CASHROSE LIEBENBERG
3213-JunMF314R 200,00R 200,00R 200,00CASHSCOTSMAN ICE SYSTEMS
3308-JunMF301R 200,00R 200,00R 200,00CASHLUV MOR MUSIC
3408-JunMF303R 200,00R 200,00R 200,00CASHIRENE SAUNDERS
3508-JunMF304R 100,00R 100,00R 100,00CASHLYNS PRE-SCHOOL
3607-JunMF299R 400,00R 400,00R 400,00CASHGRAYTEX METALS
3707-JunMF296R 400,00R 400,00R 400,00CASHJMR TRAILERPARTS GAUTENG(PTY)LTD
3807-JunMF297R 400,00R 400,00R 400,00CASHISA VAN SCHALKWYK
3907-JunMF298R 4 800,00R 4 800,00R 4 800,00EFTALMECH PTY LTD
4002-JunMF287R 500,00R 500,00R 500,00CASHSCOTSMAN ICE SYSTEMS
4101-JunMF292R 100,00R 100,00R 100,00CASHCAR CLINIC
4231-MayMF288R 550,00R 550,00R 550,00EFTMARK MELTZERSNACK FRUIT JUICE
4331-MayMF289R 500,00R 500,00R 500,00EFTAUTOBAR
SalaryData
Cells with Conditional Formatting
CellConditionCell FormatStop If True
K6:K144Cell Valuecontains ""textNO
D6:D144Cell ValueduplicatestextNO
D6:D144Cell ValueduplicatestextNO
J6:J144Cell Valuecontains "TA"textNO
J6:J144Cell Valuecontains "THANK YOU LETTER"textNO
J6:J144Cell Valuecontains "DELIVER HAVEN DOCS"textNO
J6:J144Cell Valuecontains "24X TICKETS"textNO
J6:J144Cell Valuecontains "2X TICKETS"textNO
J6:J144Cell Valuecontains "RAFFLE TICKET"textNO
J6:J144Cell Valuecontains "TAX"textNO
J6:J144Cell Valuecontains "TAX INVOICE"textNO
H6:H144Cell Valuecontains "BTF"textNO
H6:H144Cell Valuecontains "BTF"textNO
H6:H144Cell Valuecontains "EFT"textNO
E6:G144Cell Valuecontains "0.00"textNO
 

Attachments

  • SnaNamedRange.JPG
    SnaNamedRange.JPG
    33.2 KB · Views: 6
  • SnackHampValue1.JPG
    SnackHampValue1.JPG
    240.7 KB · Views: 6

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
From what I can understand, try this in cell K6 and copy down.

Excel Formula: 
=IF(J6="","",VLOOKUP(J6,SnaHamp,3,0))
 
Upvote 0
Peter_SSs said:
From what I can understand, try this in cell K6 and copy down.

Excel Formula: 
=IF(J6="","",VLOOKUP(J6,SnaHamp,3,0))
Click to expand...
Hi Peter, Thank you for your prompt response.
I have copied the snack hampers on to the current page from my data page, just as text..lol easier to type

its doing some thing, i copied it down and it gives n/a but when i type the hamper in it changes to REF, so somefing is happening.

K6 should show the cost of the hamper from J6 ... they all have a value of 300
Thank you Peter
Snap included
 

Attachments

  • cost.jpg
    cost.jpg
    206.6 KB · Views: 8
Upvote 0
Cannot do much with a picture. If the following does not resolve it, can you post a small set of sample data, the named range and the expected results with XL2BB so that we can copy and test with the same data and layout that your have.

Here is some of my sample data etc. showing layout, named range, formula etc.

22 07 09.xlsm
JK
5HAMPERH/Cost
6 
7 
8 
9 
10 
11 
12FOOD PARCELL300
13 
14 
15 
16 
17 
18BILTONG /RED WINE200
19 
20 
21BILTONG/FRUIT JUICE100
SalaryData
Cell Formulas
RangeFormula
K6:K21K6=IF(J6="","",VLOOKUP(J6,SnaHamp,3,0))
Named Ranges
NameRefers ToCells
SnaHamp=Data!$D$8:$F$12K6:K21


22 07 09.xlsm
DEF
8FOOD PARCELL300
9BILTONG /RED WINE200
10BILTONG/FRUIT JUICE100
11BILTONG/WINE500
12SNACK FRUIT JUICE6
Data
 
Upvote 0
Hi Peter, sorry for late reply ... we have loadshedding twice sometimes thrice a day in south africa.
My value was in coloum 2 and changed yours from 3 to 2 and works like a charm. Thank you

I have a macro that clears the sheet, how would i code the formula into vba for the range k6:k144

this is the code i have for the others that autofil, but with out a formula ?

VBA Code: 
aDateValue = Sheets("MichaelF").Range("B4:B142").Value
aRepValue = Sheets("MichaelF").Range("C4:C142").Value
abcValue = Sheets("MichaelF").Range("F4:F1429").Value
aRepcode = Sheets("MichaelF").Range("D4:D142").Value
aPuDate = Sheets("MichaelF").Range("Q4:Q142").Value
'anAddress = Sheets("MichaelF").Range("H4:H500").Value
'aSuburb = Sheets("MichaelF").Range("I4:I500").Value
'anArea = Sheets("MichaelF").Range("J4:J500").Value
anAmount = Sheets("MichaelF").Range("Q4:Q142").Value
anAmount1 = Sheets("MichaelF").Range("Q4:Q142").Value
anAmount2 = Sheets("MichaelF").Range("Q4:Q142").Value
PaymentT = Sheets("MichaelF").Range("O4:O142").Value
SnackHamp = Sheets("MichaelF").Range("S4:S142").Value

'MichaelValue
Sheets("SalaryData").Range("B6:B144").Value = aDateValue
Sheets("SalaryData").Range("C6:C144").Value = aRepValue
Sheets("SalaryData").Range("D6:D144").Value = aRepcode
'Sheets("CodeT").Range("F7:F20").Value = aPuDate
Sheets("SalaryData").Range("F6:F144").Value = anAmount1
Sheets("SalaryData").Range("G6:G144").Value = anAmount2
'Sheets("CodeT").Range("H5:H500").Value = aSuburb
Sheets("SalaryData").Range("I6:I144").Value = abcValue
Sheets("SalaryData").Range("E6:E144").Value = anAmount
Sheets("SalaryData").Range("H6:H144").Value = PaymentT
Sheets("SalaryData").Range("J6:J144").Value = SnackHamp


Range("E146").Formula = "=SUM(E6:E144)"
Range("F146").Formula = "=SUM(F6:F144)"
Range("G146").Formula = "=SUM(G6:G144)"
Range("K146").Formula = "=SUM(K6:K144)"
Range("H146").Formula = "=SUM(H6:H144)"
Range("F148").Formula = "=SUM(G146-K146-F149-F150-F151-F152)"

Range("B4").Select

Application.ScreenUpdating = True



End Sub

Thank you for your help Peter
God Bless
 
Upvote 0
DarrenBurke said:
Hi Peter, sorry for late reply ... we have loadshedding twice sometimes thrice a day in south africa.
My value was in coloum 2 and changed yours from 3 to 2 and works like a charm. Thank you

I have a macro that clears the sheet, how would i code the formula into vba for the range k6:k144

this is the code i have for the others that autofil, but with out a formula ?

VBA Code: 
aDateValue = Sheets("MichaelF").Range("B4:B142").Value
aRepValue = Sheets("MichaelF").Range("C4:C142").Value
abcValue = Sheets("MichaelF").Range("F4:F1429").Value
aRepcode = Sheets("MichaelF").Range("D4:D142").Value
aPuDate = Sheets("MichaelF").Range("Q4:Q142").Value
'anAddress = Sheets("MichaelF").Range("H4:H500").Value
'aSuburb = Sheets("MichaelF").Range("I4:I500").Value
'anArea = Sheets("MichaelF").Range("J4:J500").Value
anAmount = Sheets("MichaelF").Range("Q4:Q142").Value
anAmount1 = Sheets("MichaelF").Range("Q4:Q142").Value
anAmount2 = Sheets("MichaelF").Range("Q4:Q142").Value
PaymentT = Sheets("MichaelF").Range("O4:O142").Value
SnackHamp = Sheets("MichaelF").Range("S4:S142").Value

'MichaelValue
Sheets("SalaryData").Range("B6:B144").Value = aDateValue
Sheets("SalaryData").Range("C6:C144").Value = aRepValue
Sheets("SalaryData").Range("D6:D144").Value = aRepcode
'Sheets("CodeT").Range("F7:F20").Value = aPuDate
Sheets("SalaryData").Range("F6:F144").Value = anAmount1
Sheets("SalaryData").Range("G6:G144").Value = anAmount2
'Sheets("CodeT").Range("H5:H500").Value = aSuburb
Sheets("SalaryData").Range("I6:I144").Value = abcValue
Sheets("SalaryData").Range("E6:E144").Value = anAmount
Sheets("SalaryData").Range("H6:H144").Value = PaymentT
Sheets("SalaryData").Range("J6:J144").Value = SnackHamp


Range("E146").Formula = "=SUM(E6:E144)"
Range("F146").Formula = "=SUM(F6:F144)"
Range("G146").Formula = "=SUM(G6:G144)"
Range("K146").Formula = "=SUM(K6:K144)"
Range("H146").Formula = "=SUM(H6:H144)"
Range("F148").Formula = "=SUM(G146-K146-F149-F150-F151-F152)"

Range("B4").Select

Application.ScreenUpdating = True



End Sub

Thank you for your help Peter
God Bless
Click to expand...
Hi Peter, solved above, recorded a new macro named HamperValue with formula and copied down, then applied .......Inserted.... Call HamperValue to the end of code that loads the page data
all working :-)

Thank you
 
Upvote 0
Peter_SSs said:
Fair enough - but it certainly looked like the third column in your posted image. ;)

View attachment 68961
Click to expand...
Morning Peter, yes you are correct, i was trying many things when i sent my code through, that part works like a champion. Thank you.

Still working on same project at the moment. and need some advise, I have the PICKUP sheet. (as seen in image)
The 3 sets of data as is captured to the sheet "Database Building" without a problem.

However, sometimes there is not always 3 customers pickup information to complete the sheet. One or two are filled in and the Process button is clicked.
This then saves the data to "Database Building" sheet no problem.
PROBLEM.
If only one is filled in i have two blank rows in my database. (this causes blank lines the salary advise slip.... Headache.) Been like this for many years and was always manually deleted.
I want to fix this.
I would like to remove the blank lines and keep the formula in coloum A. Concantination of the telephone number
I will add the code and an image.

Long winded, but basically "remove empty rows in database but keep formula in coloum A
Included code on "Database Building" page.
Thank you for your help Peter.
Michael Pickup.xlsm
ABCDEFGHIJKLMNOPQRSTUVW
1A/ CODENUMNUMDATE CREATEDREFNUMPICK-UP DATECOMPANYBUILDINGSTREET ADDRESSSUBURBTOWNCROSS ROADCONTACT 1CONTACT 2TEL NUMBERFAX NUMBERREPRESENTATIVEDONATQTYDELIVER PRODUCTA/ CODENUMNUMF/CODE
201254823542022/07/07MF3552022/07/08ZAMBESI GLASS&ALUMINIUMMONTANA VALUE CENTRE151 CALLIANDRA STRMONTANAPRETORIATIBOUCHINACORINRECEPTION012MICHAEL FERREIRAGlass1TAX INVOICE0125482354
301190826542022/07/06MF3542022/07/07JMR TRANSPORT SOLUTIONS(PTY)LTD4 BORAX STREETALRODEALBERTONMEV HEYSTECKASK FOR HER CASH011CASHMICHAEL FERREIRA550,001TAX INVOICE0119082654CASH
401179336952022/07/06MF3512022/07/07GRAYTEX METALS375 KRUGER STREETSTRIJOMPARKRANDBURGBRINAS PER JASON011CASHMICHAEL FERREIRA200,001TAX INVOICE0117933695CASH
508297256472022/07/06MF3522022/07/07CAR CLINIC49 SHAFT ROADKNIGHTSGERMISTONJANNIEINA082CASHMICHAEL FERREIRA100,001TAX INVOICE0829725647CASH
607613931262012/10/18MF10152012/10/22THE BRAZEN HEADRIVER CRESENT CENTREC/O BETHALWITBANKMANDELANICK CHRISOCHOFFBERNI076MICHAEL FERREIRA150,001TICKETS0761393126
701328260782012/10/18MF10142012/10/22NU MID GLASSAGTER POSKANTOOR26 SADC STRMIDDELBURGPATDASSIE013MICHAEL FERREIRA200,001TAX INVOICE0132826078
808233963282012/10/17MF10132012/10/18ANTON LOMBAARD INC.OAK AVENUE OFFICE PARK372 OAK AVENUEBTF PAYMENTFERNDALEANTON LOMBAARDLIZ082MICHAEL FERREIRA400,001SHERRY HAMPER0823396328
901375276532012/07/04MF8622012/07/05LOWVELD COMPRESSOR SERVICESATLAS COPCO14 OLD PRETORIA ROADVINTONIANELSPRUITFRANCOISMARALIZE013MICHAEL FERREIRA600,0014 X BOOKS TICKETS0137527653
1001191859612008/01/01MF2092019/04/03LARMESH EXPANDED METALS07 TOP RDANDERBOLTBOKSBURGROBERTKIRST011MICHAEL FERREIRA500,001TAX INVOICE0119185961
1101189447142008/01/01MF2102008/01/03CRYSTAL SPIRITSLAKEFIELD SQRLAKEFIELD AVELAKEFIELDBENONI0CHANTEL0011MICHAEL FERREIRA100,001TAX INVOICE0118944714
12 2008/01/021
13 2008/01/031
1408233963282012/10/17MF10132012/10/18ANTON LOMBAARD INC.OAK AVENUE OFFICE PARK372 OAK AVENUEBTF PAYMENTFERNDALEANTON LOMBAARDLIZ082MICHAEL FERREIRA400,001SHERRY HAMPER0823396328
1501375276532012/07/04MF8622012/07/05LOWVELD COMPRESSOR SERVICESATLAS COPCO14 OLD PRETORIA ROADVINTONIANELSPRUITFRANCOISMARALIZE013MICHAEL FERREIRA600,0014 X BOOKS TICKETS0137527653
1601191859612008/01/01MF2092019/04/03LARMESH EXPANDED METALS07 TOP RDANDERBOLTBOKSBURGROBERTKIRST011MICHAEL FERREIRA500,001TAX INVOICE0119185961
17 2008/01/021
1801254823542022/07/07MF3552022/07/08ZAMBESI GLASS&ALUMINIUMMONTANA VALUE CENTRE151 CALLIANDRA STRMONTANAPRETORIATIBOUCHINACORINRECEPTION012MICHAEL FERREIRAGlass1TAX INVOICE0125482354
1901190826542022/07/06MF3542022/07/07JMR TRANSPORT SOLUTIONS(PTY)LTD4 BORAX STREETALRODEALBERTONMEV HEYSTECKASK FOR HER CASH011CASHMICHAEL FERREIRA550,001TAX INVOICE0119082654CASH
20 
21 
22 
23 
24 
25 
26 
27 
28 
29 
30 
31 
32 
33 
34 
35 
36 
DATABASE BUILDING
Cell Formulas
RangeFormula
A1:A36A1=CONCATENATE(T1,U1,V1)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C:CCellcontains a blank value textNO
Q2:Q13Cell Value="0.00"textNO
C:CCellcontains a blank value textNO
 

Attachments

  • PickupSheet.JPG
    PickupSheet.JPG
    249.9 KB · Views: 6
Upvote 0
Sorry, forgot this bit, I found doing research. I works but removes the formula from coloum A,.... need to keep that
VBA Code: 
Sub sbDelete_Rows_IF_Cell_Is_Blank()
Dim lRow As Long
Dim iCntr As Long
lRow = 500
For iCntr = lRow To 1 Step -1
    If Trim(Cells(iCntr, 1)) = "" Then
        Rows(iCntr).Delete
    End If
Next
End Sub

God Bless
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,825
Messages
6,181,191
Members
453,021
Latest member
pingpong7117

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