VBA needed to get my formulas off the sheet. The cells need to edited from time to time

DarrenBurke

New Member
Joined
May 6, 2022
Messages
29
Office Version
  1. 2016
  2. 2007
Platform
  1. Windows
Hi Guys and Ladies

Yes its me... Japanese thought process going on (lets make it better).
I have a Vlookup formula in worksheet "(Key) Lookup" that looks at worksheet "(Key) Dataset" for the data from the row. Based on the Date.

The main tool in these 2 worksheets is Simple. The Begin Date and the End Date. By changing these dates the data populates correctly from "(Key) Dataset".....This works 110 percent

What would you like to do? I hear ya say.
I would like to not have formulas in the cells. I know i could protect the worksheet, but sometimes the amount collected by the driver is more... or sometimes less and needs to be changed.
(FYI Haven Care Cente, Telesales phone for donations, to help abused women and children,
the telesales ladies get a percentage of what they collect as a salary hence these 2 sheets. I am the Computer guy and i said i will try and create something to help )

here is the data that automatically fills from another macro

"(Key) Dataset"
LookupIn RangeDateRefNumCanvasedCollectCommEFTCustomerHamperHamp/CostPenalties
1Y2022/07/29MF387TALLISMAN HIRE BENONIRETURN PUMP
2Y2022/07/29MF394R100,00R100,00R100,00CASHM & M HYDRAULICS CCTAX INVOICE
3Y2022/07/29MF391R600,00R600,00R600,00CASHNWK LTDTAX INVOICE
4Y2022/07/29MF392R200,00R200,00R200,00CASHSPARKLING DRY CLEANERSTAX INVOICE
5Y2022/07/29MF393R200,00R200,00R200,00CASHBUSSINES EXPRESSTAX INVOICE
6Y2022/07/27MF386R300,00R300,00R300,00CASHCATHY MURRAYTAX INVOICE
7Y2022/07/27MF378FAME FENCE(PTY)LTDTAX INVOICE
8Y2022/07/27MF379R200,00R200,00R200,00CASHGRAYTEX METALSTAX INVOICE
9Y2022/07/27MF380R200,00R200,00R200,00CASHLUV MOR MUSIC
0N2022/07/25MF376TALLISMAN HIRE BENONITAX INVOICE
0N2022/07/21MF374R1 000,00R1 000,00R1 000,00EFTISA VAN SCHALKWYKTAX INVOICE
0N2022/07/21MF375R500,00R500,00R500,00EFTMARK MELTZERTAX INVOICE
0N2022/07/19MF339R2 200,00R2 200,00R2 200,00EFTTHE VENUETAX INVOICE
0N2022/07/19MF372R3 850,00R3 850,00R3 850,00EFTSANTOVA LOGISTICSTAX INVOICE
0N2022/07/19MF369R550,00R550,00R550,00EFTESTCOURT HYPER CELLARTAX INVOICE
0N2022/07/19MF370R550,00R550,00R550,00EFTAMERICAN FANATICSTAX INVOICE
0N2022/07/19MF371SANTOVA LOGISTICSTAX INVOICE
0N2022/07/19MF360R400,00R400,00R400,00EFTLYLE SADDLERTAX INVOICE


The formula on this page looks at the above Sheet and returns the data correctly. Iam trying to get the data and then not show formulas, hope i explained it ok. Thank you all for the help

SALARY ADVICESaturday, 30 July 2022Michael Ferreira
Begin DateEnd Date
Jul 27, 2022Jul 31, 2022MaxRec9
LookupDateRefNumCanvasedCollectCommEFTCustomerHamperHamp/CostPenalty
12022/07/29MF387R0,00R0,00R0,00TALLISMAN HIRE BENONIRETURN PUMP
22022/07/29MF394R100,00R100,00R100,00M & M HYDRAULICS CC
32022/07/29MF391R600,00R600,00R600,00NWK LTD
42022/07/29MF392R200,00R200,00R200,00SPARKLING DRY CLEANERS
52022/07/29MF393R200,00R200,00R200,00BUSSINES EXPRESS
62022/07/27MF386R300,00R300,00R300,00CATHY MURRAY
72022/07/27MF378R0,00R0,00R0,00FAME FENCE(PTY)LTD
82022/07/27MF379R200,00R200,00R200,00GRAYTEX METALS
92022/07/27MF380R200,00R200,00R200,00LUV MOR MUSIC


Kindest regards Hive MInds and Thank you
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Hi Guys and Ladies

Yes its me... Japanese thought process going on (lets make it better).
I have a Vlookup formula in worksheet "(Key) Lookup" that looks at worksheet "(Key) Dataset" for the data from the row. Based on the Date.

The main tool in these 2 worksheets is Simple. The Begin Date and the End Date. By changing these dates the data populates correctly from "(Key) Dataset".....This works 110 percent

What would you like to do? I hear ya say.
I would like to not have formulas in the cells. I know i could protect the worksheet, but sometimes the amount collected by the driver is more... or sometimes less and needs to be changed.
(FYI Haven Care Cente, Telesales phone for donations, to help abused women and children,
the telesales ladies get a percentage of what they collect as a salary hence these 2 sheets. I am the Computer guy and i said i will try and create something to help )

here is the data that automatically fills from another macro

"(Key) Dataset"
LookupIn RangeDateRefNumCanvasedCollectCommEFTCustomerHamperHamp/CostPenalties
1Y2022/07/29MF387TALLISMAN HIRE BENONIRETURN PUMP
2Y2022/07/29MF394R100,00R100,00R100,00CASHM & M HYDRAULICS CCTAX INVOICE
3Y2022/07/29MF391R600,00R600,00R600,00CASHNWK LTDTAX INVOICE
4Y2022/07/29MF392R200,00R200,00R200,00CASHSPARKLING DRY CLEANERSTAX INVOICE
5Y2022/07/29MF393R200,00R200,00R200,00CASHBUSSINES EXPRESSTAX INVOICE
6Y2022/07/27MF386R300,00R300,00R300,00CASHCATHY MURRAYTAX INVOICE
7Y2022/07/27MF378FAME FENCE(PTY)LTDTAX INVOICE
8Y2022/07/27MF379R200,00R200,00R200,00CASHGRAYTEX METALSTAX INVOICE
9Y2022/07/27MF380R200,00R200,00R200,00CASHLUV MOR MUSIC
0N2022/07/25MF376TALLISMAN HIRE BENONITAX INVOICE
0N2022/07/21MF374R1 000,00R1 000,00R1 000,00EFTISA VAN SCHALKWYKTAX INVOICE
0N2022/07/21MF375R500,00R500,00R500,00EFTMARK MELTZERTAX INVOICE
0N2022/07/19MF339R2 200,00R2 200,00R2 200,00EFTTHE VENUETAX INVOICE
0N2022/07/19MF372R3 850,00R3 850,00R3 850,00EFTSANTOVA LOGISTICSTAX INVOICE
0N2022/07/19MF369R550,00R550,00R550,00EFTESTCOURT HYPER CELLARTAX INVOICE
0N2022/07/19MF370R550,00R550,00R550,00EFTAMERICAN FANATICSTAX INVOICE
0N2022/07/19MF371SANTOVA LOGISTICSTAX INVOICE
0N2022/07/19MF360R400,00R400,00R400,00EFTLYLE SADDLERTAX INVOICE


The formula on this page looks at the above Sheet and returns the data correctly. Iam trying to get the data and then not show formulas, hope i explained it ok. Thank you all for the help

SALARY ADVICESaturday, 30 July 2022Michael Ferreira
Begin DateEnd Date
Jul 27, 2022Jul 31, 2022MaxRec9
LookupDateRefNumCanvasedCollectCommEFTCustomerHamperHamp/CostPenalty
12022/07/29MF387R0,00R0,00R0,00TALLISMAN HIRE BENONIRETURN PUMP
22022/07/29MF394R100,00R100,00R100,00M & M HYDRAULICS CC
32022/07/29MF391R600,00R600,00R600,00NWK LTD
42022/07/29MF392R200,00R200,00R200,00SPARKLING DRY CLEANERS
52022/07/29MF393R200,00R200,00R200,00BUSSINES EXPRESS
62022/07/27MF386R300,00R300,00R300,00CATHY MURRAY
72022/07/27MF378R0,00R0,00R0,00FAME FENCE(PTY)LTD
82022/07/27MF379R200,00R200,00R200,00GRAYTEX METALS
92022/07/27MF380R200,00R200,00R200,00LUV MOR MUSIC


Kindest regards Hive MInds and Thank you
Sorry this is the formula in C5 ...just below the date. it gets draged down and then to the right
=IFERROR(VLOOKUP($B5;'(Key) Dataset'!$A$1:$M$147;MATCH('(Key) Lookup'!C$4;'(Key) Dataset'!$A$1:$M$1;0);0);"")
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,874
Members
452,363
Latest member
merico17

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