Posts by jigar987

Re: Extract Text From Cell

thanks

Re: Calculating A 3d Plane Of Best Fit And Mean Radius Of Curvature

Dear All,

sorry i added my reply in this thread but i cant find out for the same & i can't open new post.
i think i m coming here after long time so i cant do it.

i m coming here for my excel help for following formula

SUMPRODUCT(($A10<=Data!$V$3:$V$168)*($B10>=Data!$V$3:$V$168)*(Aavruti!C$4=Data!$W$3:$W$168)*(Aavruti!C$5=Data!$B$3:$B$168),(Data!$V$3:$V$168))

above formula work perfectly but give result in sum.

but i need result in no. of count. like following formula

SUMPRODUCT(($A10<=Data!$V$3:$V$168)*($B10>=Data!$V$3:$V$168)*(Aavruti!C$4=Data!$W$3:$W$168)*(Aavruti!C$5=Data!$B$3:$B$168),count(Data!$V$3:$V$168))

above formula give error...

please anyone help me....

thanks in advance.

Re: Drill-down Sheets In New Files (pivot Table)

Hi iwrk4dedpr,

Thanks for the reply...

It's work perfect .

Thanks

Hi All,

I want Pivot Table Drill-down result in New excel file .
--------------------------------------------------
when I have a pivot table, When I double click any data cell, the result comes in the new sheet(Drill-Down) of the same file BUT I want the same result to come in a New Book (a new excel sheet) and not in the same file.

Waiting for your reply.

Re: Automatic Updating Web Site Status In Cell

Hi All,

Thank you all for reply...

I got the solution from mikerickson and Wigi VBA Code.....

I made a new file copy 100 link from original file and i used mikerickson code for the new file and run the macro and gone for the break....when I came back and saved the excel file, I had all the working websites open in the Internet explorer and the non working websites said to be "bad address" in the excel file and they didnt open in the internet explorer

this is working for me..though I had to close all the explorer windows by ctrl+alt+del

Thanks and Thanks your for reply...and nice suggetion.....

I really appreciate to all the member of OZGRID.COM

Thanks

Re: Automatic Updating Web Site Status In Cell

Hi ,

if There is a no formula for it then any VBA Code for it?

Thanks

Re: Automatic Updating Web Site Status In Cell

Hi royUK,

I have 2000 web page address in column A

Out of some page are correct and some page are incorrect .

Like : http://puzzlehacker.com/1.html is correct (if click on this link then you can go this page) but http://puzzlehacker.com/1.htm is incorrect (if click on this link then you can't go on this page and msg is "The page cannot be found " or " The page cannot be displayed")

now i don't want to click each and every page and i want to know that link is right or not right ... and this result show in B column which formula can be use in column B or any vba code for it?

i think now u can understnad my prob....

thax

Re: Automatic Updation Web Site Status In Cell

Hi Norie,

Thanks for reply,

Let me explain :

In a Xcel sheet, in the coulmn A there are lots of website names (links to the website and their subpages). So as you know, we can click on any website name and this way we can go the webpage and see if the website is there or if the name is incorrect, we will find that the page cannot be displayed. This is a general practice and this can be done when the website names are less. But I have 2000 website names and I have to figure out that out of this, how many are correct and how many are incorrect.

For example. A1=www.yahoo.com (now if we click on this cell, we will go to yahoo homepage in the Internet Explorer) but now if in A2=www.tahoo.com (now if we click on this, we will get that the page cannot be displayed).

So I dont want to click on every of these 2000 websites and see if they are correct or not, instead, there has to be some formula that can be used to check the same. And that is what I am looking for.....

Thanks and waiting for the reply.

Re: Automatic Updating Web Site Status In Cell

Hi RoyUK,

Thanks for your reply, but i guess u didn't understand my question .

I have list of website addresses and their web pages in the excel sheet, and I want to know their status (active or not valide) with out clicking and going to website or web page.

Hope i make sense and wish u understand my question.

Thanks

Hi All,

Cell A1 value is: http://www.yahoo.com B1 Value should automatically show "Possible"
Cell A1 value is: http://www.icallindia.net B1 Value should automatically show "Possible"
Cell A1 value is: http://www.jigarparekh.html B1 Value should automatically show "notvalid"

I need a formula which can automatically see the status of the website address and updated in B column. which means that one dose not need to go to the website page to see if it is correct or not.

I have lot of website in the one sheet and i don't want to go all this website and check if they are correct. I want the status of this website in b column once when i have the website name already in the A column.

waiting for the revert.

Re: Date Formula

Thank you Biz and filo 65

I got the answer...

thanks

Hi All,

following is the figar mention in the cell.
A1 : [COLOR="Red"]4[/COLOR] (number format)
following is the date mention as per the MM/DD/YY format
A2: 01/07/07
Question is to have A3 (Cell) as next date with the number 4 after the date mention in A2 , which is date :02/[COLOR="red"]04[/COLOR]/07 in the above example.

need the formula for cell A3 .

example 2
A1:[COLOR="red"]31[/COLOR] or [COLOR="red"]30[/COLOR]
A2:01/31/07
A3: 02/[COLOR="red"]28[/COLOR]/07 (30 and 31 This are End of the month date then next end month date is [COLOR="red"]28[/COLOR] feb 07)

Example 3

A1:[COLOR="red"]23[/COLOR]
A2:12/15/07
A3: 12/[COLOR="red"]23[/COLOR]/07 (Result)

Example 4

A1:[COLOR="red"]14[/COLOR]
A2:12/15/[COLOR="Navy"]07[/COLOR]
A3: 01/[COLOR="red"]14[/COLOR]/[COLOR="navy"]08[/COLOR] (Result)

Waiting for an urgent reply......

Re: Extract Text From Cell

Thanks ...... Thanks a lot...................

Greatly apprieciated.

Re: Extract Text From Cell

Quote from shg

Here's the first two:

=DATE(LEFT(A1,4), MID(A1,5,2), MID(A1,7,2))

=MID(A1,10,2)/24 + MID(A1,12,2)/1440 + MID(A1,12,2)/86400

Thanks Shg, My problem is sloved.

both are working best.

thanks for reply.

thanks wigi.

Re: Function- Left, Right, Mid &amp; Len

Hi wigi,

Thanks for reply,

I know about text to columns and after formating but that result are as follows:

In Cell B1: 20070322 not right
In Cell C1: 023047 not right
In cell D1: 002035 Right (OK)
In cell E1: (111)-222-3333 Right (ok)

but i want result are as following :

In Cell B1: 03/22/2007 Date(MM/DD/YYYY)
In Cell C1: 02:30:47 Time([HH]:MM:SS)

Thanks,

Hi All,

In Cell A1 Value is: 20070322_023047_002035_1112223333

above code is like Date(YYYYMMDD)_Time(HHMMSS)_Agent#_Phone#

I want result As per Following:

In Cell B1: 03/22/2007 Date(MM/DD/YYYY)
In Cell C1: 02:30:47 Time([HH]:MM:SS)
In cell D1: 002035
In cell E1: 111-222-3333

Hope you can understand my question .....

Waiting for reply.

ASAP.

Re: Undo The Column Hide &amp; Stop On Delete Sheet

Hi all,

My problem is solved, because one user know my shared password and he was deleted my sheet after unshared .

i tried to delete sheet in shared mode but it is not possible in xl2000.
then i found the person. and problem is solved.

thank you all the members of this website...

thanks

Re: File Size

Hi rookie,

Try it following :

1. Deleted blank columns and rows from all the sheets and save it again
2. press F5 select special -> "blank" option and clear content
3. if there was a pic in you file then remove pic from your file from all the sheets.
4. if you are useing name define then clear it.

also you can search how to reduce your file size in ozgrid.com

and try that option..

hope this will help you.
http://www.ozgrid.com/Excel/ExcelProblems.htm
-Thanks & Regards,
Jigar parekh

Re: Remove .xls From Header

Hi foxxy1,

kindly find attached file and see the print preivew. there is no .xls shown in xl200.
if u have any query please revert

thanks and regards,

Re: Undo The Column Hide &amp; Stop On Delete Sheet

Hi Mac,

I know about protecting the workbook.
my file is in shared mode and 6 to 7 user use this file some sheet is protected and some is unprotect.
if i will protect the workbook then user can Hide the column and change the width in protecting workbook.

and i don't want to protect some sheet because in shared mode some macro is not working that'swhy i want to do this by VBA. any solution ?