How to Add/ Remove HYPERLINKS in Excel

Posted by ADMIN on 06:22
Extreme Excel explains how to Add and remove Hyperlinks in an Excel Workbook.  Using hyperlinks you can link to web URLs or other sheets in excel. For this tutorial, we will be using an Excel Macros as our example.

 How to Add Hyperlink?

 There are two ways of adding Hyperlinks:

 1. Using HYPERLINKS function
2. Using Excel Macro

HYPERLINKS Function

 Let's say: A1 contains = "Hello World" and you want to apply link "http://www.extremeexcelsolutions.weebly.com" on clicking it

 In cell A2, write Formula :  =HYPERLINKS("http://www.extremeexcelsolutions.weebly.com",A1)

 Output :  A2  =  Hello World

Using Excel Macro (To ADD & REMOVE HYPERLINKS)

 See this video to learn how to implement it using Excel macros

How to rearrange data using Excel macro?

Posted by ADMIN on 13:08
Learn how to write macros in MS Excel. This is a small demonstration to discuss it's capabilities.

This macro gives idea of writing macros to rearrange data in excel sheet in any format. The commands and tricks used in this tutorial are very useful in writing such macros.








Hit LIKE button if you feel that this tutorial was useful.. 

This is very important to know your interest so that we may improve or upload more such tutorials.

Enable Macros in Excel

Posted by ADMIN on 13:01
How to Enable Macros in Excel 2007/2010 ?

1. Go to Excel Options












2. Click on Trust Center.









3. Click Trust Center Settings.








Enable all macros and click OK.

Calender using Excel Formulas

Posted by ADMIN on 13:00
How to create this interactive calender using Excel Formulas??

- with buttons to choose year and month.




Online WEBINAR




Answer :   Download File

If A1= Month Number (1-12)   And      A2= Year 

Then use these formulas: 

D12=IF(WEEKDAY(DATE(A1,A2,1))=1,1,"")   

E12=IF(WEEKDAY(DATE(A1,A2,1))=2,1,IF(ISERROR(D12+1),"",D12+1))   

F12=IF(WEEKDAY(DATE(A1,A2,1))=3,1,IF(ISERROR(E12+1),"",E12+1))  

G12=IF(WEEKDAY(DATE(A1,A2,1))=4,1,IF(ISERROR(F12+1),"",F12+1)) 

H12=IF(WEEKDAY(DATE(A1,A2,1))=5,1,IF(ISERROR(G12+1),"",G12+1)) 

I12=IF(WEEKDAY(DATE(A1,A2,1))=6,1,IF(ISERROR(H12+1),"",H12+1)) 

J12=IF(WEEKDAY(DATE(A1,A2,1))=7,1,IF(ISERROR(I12+1),"",I12+1)) 

D13=J12+1 

E13=D13+1 

F13=E13+1  

G13=F13+1 

H13=G13+1 

I13=H13+1 

J13=I13+1 

F16=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,E16),E16+1,""),"")

G16=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,F16),F16+1,""),"")

H16=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,G16),G16+1,""),"")

I16=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,H16),H16+1,""),"")

J16=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,I16),I16+1,""),"")

D17=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,J16),J16+1,""),"")

E17=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,D17),D17+1,""),"")

F17=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,E17),E17+1,""),"")

G17=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,F17),F17+1,""),"")

H17=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,G17),G17+1,""),"")

I17=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,H17),H17+1,""),"")

J17=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,I17),I17+1,""),"")

D14=J13+1

E14=D14+1

F14=E14+1

G14=F14+1

H14=G14+1

I14=H14+1

J14=I14+1

D15=J14+1

E15=D15+1

F15=E15+1

G15=F15+1

H15=G15+1

I15=H15+1

J15=I15+1

D16=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,J15),J15+1,""),"")

E16=IFERROR(IF(EOMONTH(DATE($D$6,$B$2,1),0)>DATE($D$6,$B$2,D16),D16+1,""),"")  


And the calender is Ready. 

Formulas - Problem Statement

Posted by ADMIN on 10:24
A businessman take orders from a company XYZ and ships the product on weekdays(Mon-Fri). New Orders come all 7 days of week and he ships orders for Saturday and Sunday on Monday.

The shipping takes 2 working days to reach ABC's warehouse and no items reach at warehouse on Saturday and Sunday.

Write formulas for items arriving at warehouse for each day.




Answer:

 

Formula for Can't ship on Sat/Sun
=IF(WEEKDAY(D4)=7,"-",IF(WEEKDAY(D4)=1,"-",IF(WEEKDAY(D4)=2,B5+C5+D5,D5)))
Drag it till right extreme end.

Formula for Arriving at warehouse
=IF(WEEKDAY(F4)=2,B6+C6,IF(WEEKDAY(F4)=4,D6,IF(WEEKDAY(F4)=5,D6,IF(WEEKDAY(F4)=6,D6,""))))
Drag it till right extreme end. 
Subscribe to: Comments (Atom)