Posts by yjoshi

Re: Auto Updating Cell In Relation To Input Of Another Cell

Welcome to OZGrid...
while your question is very clear... i am not sure what you want to achieve by getting X against all these repeated values?

Is it for some kind of conditional formatting?

Re: Skipping Cells To Add Data

Its not clear how are you posting the data to the table? is it via VBA code?
While you are posting the data, you can use named range or other VBA methods to identify the last filled cell and enter the new data into the next row.

Please attach the worksheet or paste your code and we can help.

This is a very simple function, so don't worry it will be solved in minutes if you give right information.

Re: Custom Date Series Class Schedule

I tried an approach comfortable to me.. that is range name.

1. I entered date with Monday (3/24) in A1
2. While i was in cell A2, i created a range name (Menu -> Insert -> Name -> Create)
with name = mon_wed and refers to =
[frc]=IF(WEEKDAY(Sheet1!A1)=2,Sheet1!A1+2,Sheet1!A1+5)[/frc]
3. Now from A2 to A10 i just entered formula =mon_wed
(actually i just pressed F3 and selected the range name)... bang... i am done

Hope this helps

Edit: I have attached a worksheet for your testing.. hope this helps[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]BTW, you can easily make edits to the formula in the range name to get the desired results as mentioned in your thread... its just left to when you add how many days

Re: Lookup Based On Column & Row Criteria

Need more details... also as per the forum guidelines, please don't assume the answer!!!

I would recommend Pivot table (if the data is spread across worksheets, i will consolidate using pivot table consolidation and then pull out reports based on the same.

If you can provide a sample dummy worksheet we can solve it quickly

Re: Dynamic Range Without Counting False

I have another suggestion.
you can hide the false value, by putting it in if loop so you can only count the available values and restrain the range.

Re: Return Text Based On Cell Percentage

I interpreted the question differently than Dave... not sure if this helps
I thought you are asking how to change text based on cell value in percentage...

[frc]=IF(A1<0.3,"Less than 30%",IF(A1<0.6,"Less than 60%","Greater than 60%"))[/frc]

Re: Consolidate data from multiple sheets into 1

Firstly I will suggest, organize your data in a bit better manner.

Your cost center sheets has too much data that is not required....
like quarterly results, subtotals... etc.

You can still take this and write a code to delete the unwanted data (rows / columns) at one go.

Next... you can use the pivot table feature of "multiple consolidation ranges".

I tried it and almost got what is required... you will need to copy the pivot table paste as value and do the additional formatting... which should not take much time.

See the file attached...

Re: Search for values

I know you are expecting a vba code help... but lets try to think out of box for a moment....

have you used the google desktop utility?
http://desktop.google.com

If you do not intend to do any updates programatically and just open these files.... i think this is best... i tried doing it....

gave 10000 .xls in the search criteria and it searched about 28 files from my pc...

I know the VBA way of searching is still pending... will try to put the code tomorrow.. had worked on something like this previously... but its good if this helps you

Re: Disabling automatic conversion of X-X copied data into date in Excel

Not an exact answer to your question, but this can be a good workaround...

See if it helps you.

Try importing the webpage as data -> get external data -> New Web Query:
1. Enter the url
2. select tables only
3. select full html formatting

and say OK.

You will get the entire data as it is.

I tried your link. Have uploaded the sheet... let me know if it was of any use.

Re: Test for empty cell

The problem is that, when you erase data with space bar, the value is not "" but is " " includes spaces. so good way is to trim the spaces and try again.

It worked for me... let me know if it works for you.

Sub test()
    If ActiveCell.Value > "" And Trim(ActiveCell.Offset(0, 2).Value) = "" Then
    ActiveCell.Offset(0, 3).Value = ("Check this line")
    With ActiveCell.Offset(0, 3).Font
        .Name = "Verdana"
        .Size = 8
        .ColorIndex = 5
        .Bold = True
    End With
    End If
End Sub

could repruduce your problem....

I have a solution that works ...

1. Select the range in excel
2. Press Shift Key and Open the Edit Menu
3. You will see "Copy Picture" ...
4. Select bitmap in the new window(type)
5. Open word and say paste.... it works

Would you like to tell how big is your table?

or put a zip file?

Do you want to just convert it to degrees C or even have operations on that?

I will suggest using format to do this.
Follow the steps....
1. Select the range where you want the format, and which contain numbers
2. Menu -> Format -> Cells (OR press Ctrl + 1)
3. Select Number Tab
4. Select Catagroy = Custom
5. in Type ... enter General" then press Alt + 0176 and leave the Alt button.
You will see the degree sign. Now enter C"
in short create format of General"<degree sign>C"
6. Close the format window.

You will see that all the numbers are formatted to Degree Celcius....

You can also do operations on the same, or even create graphs out of this data.

See the sheet attached.

I think your question was a bit vague .. thats why no replies.

I have tried to answer it .. see if this helps.

Private Sub CommandButton1_Click()
Dim rng As Range
Dim cel As Range
Dim cp As Range
Dim sht As String
With Sheets("Raw_Data")
    Set rng = Intersect(.Range("F:F"), .UsedRange)
End With
For Each cel In rng
    If cel.Value = "" And cel.Offset(0, -5).Value <> "" Then
    sht = cel.Offset(0, -4).Value
    Set cp = Sheets(sht).Range("A65536").End(xlUp).Offset(1, 0)
    cp.Value = cel.Offset(0, -5).Value
    cp.Offset(0, 1).Value = cel.Offset(0, -3).Value
    cp.Offset(0, 2).Value = cel.Offset(0, -2).Value
    cp.Offset(0, 3).Value = cel.Offset(0, -1).Value
    cel.Value = "Yes"
    End If
Next


End Sub

Also see the live demo.. file attachedl

You will always see it only when you click on the cell drop down.

By default, if you appliy a validation to a blank cell... you will see a blank cell... and only when you click on drop down (or press Alt + down key) you will see a list.

In case you are seeing a value, you have probably entered it.

Try pressing delete... your value will vanish and you will see list only when you want it !

You can do this by using New Database Query, using excel file.

I tried it with 6 sheets Jan -> Jun and it did work for me.

I have recorded the macro of the procedure...

The key here is to change the query to "SELECT * from ...... Union All Select * from .... "

see the code below......

Sub Macro1()
    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=Excel Files;DBQ=D:\Yogendra\Excel\Excel_Database\consolidate\Apr.xls;DefaultDir=D:\Yogendra\Excel\Excel_Database\consolidat" _
        ), Array("e;DriverId=790;MaxBufferSize=2048;PageTimeout=5;")), Destination:= _
        Range("A1"))
        .CommandText = Array( _
        "SELECT *" & Chr(13) & "" & Chr(10) & "FROM `D:\Yogendra\Excel\Excel_Database\consolidate\Jan`.`C$` `C$`" & Chr(13) & "" & Chr(10) & "union all" & Chr(13) & "" & Chr(10) & "SELECT *" & Chr(13) & "" & Chr(10) & "FROM `D:\Yogendra\Excel\Excel_Database\consolidate\Feb`.`C$` `C$`" & Chr(13) & "" & Chr(10) & "union all" & Chr(13) & "" & Chr(10) & "SELECT *" & Chr(13) & "" & Chr(10) & "FROM `D:\Yogen" _
        , _
        "dra\Excel\Excel_Database\consolidate\Mar`.`C$` `C$`" & Chr(13) & "" & Chr(10) & "union all" & Chr(13) & "" & Chr(10) & "SELECT *" & Chr(13) & "" & Chr(10) & "FROM `D:\Yogendra\Excel\Excel_Database\consolidate\Apr`.`C$` `C$`" & Chr(13) & "" & Chr(10) & "union all" & Chr(13) & "" & Chr(10) & "SELECT *" & Chr(13) & "" & Chr(10) & "FROM `D:\Yogendra\Excel\Excel_Database" _
        , _
        "\consolidate\May`.`C$` `C$`" & Chr(13) & "" & Chr(10) & "union all" & Chr(13) & "" & Chr(10) & "SELECT *" & Chr(13) & "" & Chr(10) & "FROM `D:\Yogendra\Excel\Excel_Database\consolidate\Jun`.`C$` `C$`" _
        )
        .Name = "Query from Excel Files"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With


End Sub

As told earlier, this is the code generated automatically.... if you want you can also try recording one and then modify as you wish

Hope this helps. Let me know if this does not work.

I think Roy's Idea is really cool.

Let me know if you are unable to implement it with my sheet, in case you have opted for using my sheet.

thanks...

file here... forgot earlier

Hi,

I think I know what you exactly want.

You have 4 activities and you want to have a stopwatch for each one to start and stop the things.

Also you must be interested in making analysis and good charts out of it.

I have tried whatever i could think you want.

Please see the zip file and let me know.

BTW .. Thanks for your query... it was a good exercise.

Deniel (Derk),

I uploaded my sheet without seeing your update.
Actually this was eating my head whole night and had thought of the solution at night as well. I just implemented it and uploaded without seeing yours.

Anyway.... both of us are on similar lines, with minor adjustment in techniques.

Thanks