• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

need to pick a data from one of the maintenance sheet and calculate and highlight certain value

A

anuwers

Member
Dear Friends,

I need to help on this. The requirement is very simple, but i wrote breifly

I like to have the VBA code to paste on each sheet of view code to do following activities.
I have more than 80 sheets, which will carry sheet names either with numbers or alphabets or alphanumeric.
Every Sheet has the same format with same header in same columns.
I have maintenance sheet with four columns of Currency / Rate / Value date / Correspondent column. This maintenance sheet will be updated whenever required.
What I required is,
When I place the currency in field D, that currency should be looked up in maintenance Sheet and obtain the related information to copy in respective fields.
Below is the example and I required to have this work in all sheets and cells as required below.
Example:
If cell D2 is typed with currency USD, related information should be picked from maintenance sheet table and to be placed as follows
then I required F2 information(Exchange rate) should be placed
then I required H2 information(VALUE DATE) should be placed
then I required I2 information(Correspondent) should be placed

When I write the E2 as amount 1000 (or) any other amount, then it should multiply with F2 value and place the result in G2
Suppose I write the G2 as amount 1000 (or) any other amount, then it should divide with F2 value and place the result in E2
When I write the currency in D2, then I required the L2 get the date of entry and M2 get the Time of entry
When the G2 value is more than 245000.00 then I required the full line from A2 to M2 to be highlighted with some color.

I have attached a sample as i what expect

Thanks
 

Attachments

  • CheckValidationReport.xlsx
    30.6 KB · Views: 4
Dear Vletm

Thanks for the update. it is working fine. Even the dropped down also OK. Still one of the point below required to solve.

1)Suppose I write the G2 as amount 1000 (or) any other amount, then it should divide with F2 value and place the result in E2
2)Also i like to know where this vba code placed. I like view the code which you made on this excel.

Thanks
 
1) Oh ... that 'divide' case missed. Now it should work too ...
2) Code is in 'ThisWorkbook'
... better now?
ps. Why 80 sheets? Have You tried filtering?
 

Attachments

  • CheckValidationReport.xlsb
    29 KB · Views: 1
Dear vletm

the fix which you provide recently for divide, is not the division. It is still with multiplication. Please advise.

We are having 80 branches, so we need use 80 sheets may be more.

Filtering is working and even typing also working. its ok
 
Yes. Copy Paste works ... but just missed to change * to / ... sorry.
80: If there would be 'branche' with row ... then no need so many sheets.
... remember backups
 

Attachments

  • CheckValidationReport.xlsb
    29.3 KB · Views: 2
Dear Vletm

Thanks for your support.
I required one more query on the above. I need the word "CBC" to be placed in J column, when the amount is more than 245000.00
.
Regarding using of one sheet instead of 80 sheets. Bellow is the clarification and can help if possible.
.
Yes. There are more control required if i need to use one sheet.

B is the column where we key in for the branch no:s
C is the column where we key the control number of branches.

Just to brief you,
Our department receives application to process.
We named each sheet with branch names as 001, 002, 003,004 etc.... and
also some branch named as ABD,TOS, IT1......
.
Control number is the number which is the serial number used by the branches on application. Our 001 branch writes control no: like this on application.
001/00001/2016. Our 002 branch writes control no: like this on application as 002/00001/2016....
This 00001 continued serially for each branches until the year completes.
.
On the B column we type the Branch no: and C column we type the control number.
.
why we do that because we control the applications which should not be duplicated at our end as we receive applications via fax.
and also to monitor the missing control no: while faxing.
.
Similarly if we do in one sheet by writing the mix of branches and mix of control numbers, we need the following control
.
I need the report to show the missing numbers of each branches.
I need the report to show the last numbers in each branch.
I need the report based on the dates which is written on A column. Report format will be the same sheet format.
.
I need to get duplicate indication while keying the data in the sheet for the repeated branch and its control no:. In fact it should not allow the duplicate number to key in.
 
Now "CBC" comes too.

Oh, I just remember that I took about 4*169 textboxes away ... "USD" ...
I hope that You won't need those anymore ... how?

Okay, many things can do manually ... with ~80sheets.
But, of course some of those could let Excel do as automatic.

Ideas ... Questions?
 

Attachments

  • CheckValidationReport.xlsb
    29.8 KB · Views: 4
Thanks for the CBC and other codes.. which will help us a lot.
Thanks once again.
.
What is your opinion in doing one sheet instead of 80 sheets
Shall i write the request in separate thread
 
Dear vletm,

One more requirement after that CBC.

You have given the vba code for more than 245000 for all active sheets.
In one of the sheet named "ABS" required to have more than 485000. Remaining other sheets OK for 245000

Please advise.
 
Dear

Yes. It is showing CBC for more than 485000 in ABS sheet, but displays the color as well
ABS should display color only if more than 485000
Other sheet should display color if more than 245000

Please advise
 
Dear vletm

Last answer for ABS to show the color not working. Even other branches also not working.
Please guide.
 
Dear Vletm,

having of one sheet will respond in INBOX as next phase. Since already we worked for 80 sheets.

Kindly advise on the fix for below

ABS should display color only if more than 485000
Other sheet should display color if more than 245000
 
Dear Vletm,

Adding to the last update on 80 sheets. I required the following.

On the Maintenance Sheet, we update the rate in B2:B30. We required VBA to delete the data in the cells whenever the day changes automatically.

Thanks
 
'Data' from those 80 sheets are calculated with the newest rates.
If someone'll update rates then of course next dates will change too.
Next NEW rows of data will calculate with the newest rates.
Which data need to delete?
Which day changes automatically?
... You/Your company still wants to control many things manually, okay?
 
Every day users updates the new rates in Maintenance Sheet in the b2:b30 of the sheet. Suppose if user forget to update the new rate in maintenance sheet, then other sheets will pick the old rate. Hence i required the maintenance table B2:B30 to be deleted when the date changes in system automatically.
 
Rates will clear if not today's rates ... checking many times.
There would be something else too ...
 

Attachments

  • CheckValidationReport.xlsb
    40.7 KB · Views: 2
Dear Vletm,

Since i am working on 80 sheets, kindly update the code of deleting rate in maintenance sheet in attached XL file.

Also guide how and when the rate will delete from maintenance sheet

Thanks
 

Attachments

  • CheckValidationReportnew.xlsb
    26.7 KB · Views: 1
No matter with how many sheets You're working
I updated codes
and as I wrote:
Rates will clear if not today's rates ... checking many times.
Means EVERY TIME then You will activate ANY SHEET and that will do "AUTOMATIC".
Plus there are few checks that "div/0" won't make huge problems.
 

Attachments

  • CheckValidationReportnew.xlsb
    33.9 KB · Views: 3
You must log in or register to reply here.
Back
Top