Topic Wiki

Rule 1: Did you try A.I? Right before you click post, give it a quick run. Most excel questions can be answered very quickly, thoroughly, and clearly via chatgpt. Wrong answer, or having trouble with the correct wording of the prompt? Post for help

=================
For cool Excel spreadsheet template to keep track of all Credit Card stuff, see here
« Last edited by Jojo202 on March 01, 2024, 12:09:44 AM »

Author Topic: Excel Help and Problems  (Read 287408 times)

Offline Zevi16

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Mar 2015
  • Posts: 2161
  • Total likes: 78
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #340 on: August 21, 2015, 06:31:23 AM »
=HYPERLINK("C:\path\to\your\folder","My Folder")

With friendly name
btw on open office its not a comma its a semi colon and no extra quotations . like this =HYPERLINK("C:\path\to\your\folder";My Folder)

Offline jackofall

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Nov 2012
  • Posts: 772
  • Total likes: 45
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #341 on: August 31, 2015, 01:14:23 PM »
I have rows of data which occurred at different times. Column C has the time stamp, any formula i can put in Column D which will give a number based on the order these events occurred. For example 30 events occurred in June each on separate day can a formula calculate that the row that has June 10th is the 10th item in chronological order?

TIA
If you focus on being a boss, you will never be a leader. Leaders lead people to growth and improvement.

Offline mancunian

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jul 2008
  • Posts: 1297
  • Total likes: 17
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #342 on: August 31, 2015, 01:16:06 PM »
You want to return which row 'June 10' shows in?
Use MATCH

=MATCH('June 10', RANGE, 0)

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3810
  • Total likes: 826
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #343 on: August 31, 2015, 01:27:38 PM »
I have rows of data which occurred at different times. Column C has the time stamp, any formula i can put in Column D which will give a number based on the order these events occurred. For example 30 events occurred in June each on separate day can a formula calculate that the row that has June 10th is the 10th item in chronological order?

TIA
That would be RANK():
https://support.office.com/en-us/article/RANK-function-6a2fc49d-1831-4a03-9d8c-c279cf99f723

I believe that's what you're asking for.

Offline jackofall

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: Nov 2012
  • Posts: 772
  • Total likes: 45
  • DansDeals.com Hat Tips 2
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #344 on: August 31, 2015, 01:36:55 PM »
If you focus on being a boss, you will never be a leader. Leaders lead people to growth and improvement.

Offline Zevi16

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Mar 2015
  • Posts: 2161
  • Total likes: 78
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Problem
« Reply #345 on: September 02, 2015, 02:28:09 AM »
Is there a way to change the name of the columns, instaed of 'a' 'b' 'c'. I made a row at the top and i froze it but the problem is, whenever i add data and sort by a-z it gets put to the bottom and the data in that row won't stay frozen, only the actual row (now with random data)

Offline lubaby

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5335
  • Total likes: 736
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Problem
« Reply #346 on: September 02, 2015, 07:51:57 AM »
Is there a way to change the name of the columns, instaed of 'a' 'b' 'c'. I made a row at the top and i froze it but the problem is, whenever i add data and sort by a-z it gets put to the bottom and the data in that row won't stay frozen, only the actual row (now with random data)
Unfreeze the row. Make a table with all your data in it.

Shortcut is CTRL + T (highlight all your data, then do CTRL + T. If all your date is in columns/rows next to each other, you should be able to just click on a my box, then CTRL + T should do the trick automatically.)

Makes it much simpler for sorting and filtering as well.
« Last Edit: September 02, 2015, 07:55:44 AM by lubaby »

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3810
  • Total likes: 826
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #347 on: September 02, 2015, 10:08:00 AM »
Is there a way to change the name of the columns, instaed of 'a' 'b' 'c'. I made a row at the top and i froze it but the problem is, whenever i add data and sort by a-z it gets put to the bottom and the data in that row won't stay frozen, only the actual row (now with random data)
Unfreeze the row. Make a table with all your data in it.

Shortcut is CTRL + T (highlight all your data, then do CTRL + T. If all your date is in columns/rows next to each other, you should be able to just click on a my box, then CTRL + T should do the trick automatically.)

Makes it much simpler for sorting and filtering as well.
Don't use the Sort A to Z/Z to A shortcuts.

Either use Custom Sort and check off My data has headers, or add a Filter to the whole range (which automatically makes the top row the column headers and doesn't move them) and sort through the Filter button on the column you want.

lubaby's table suggestion essentially adds the Filter, as well as some formatting. If you want the formatting, the table is quickest due to the shortcut lubaby mentioned.

Offline Live N Learn

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2015
  • Posts: 3799
  • Total likes: 332
  • DansDeals.com Hat Tips 3
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #348 on: September 08, 2015, 12:38:29 PM »
Is there a way to make a macro, in a Google spreadsheet?
".איזהו חכם, הלומד מכל אדם"

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3810
  • Total likes: 826
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #349 on: September 08, 2015, 01:21:32 PM »
Is there a way to make a macro, in a Google spreadsheet?
Yup. The Google equivalent of VBA is called Apps Script.

Offline mancunian

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jul 2008
  • Posts: 1297
  • Total likes: 17
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #350 on: September 08, 2015, 01:30:29 PM »
How can I upload to an FTP location from VBA?

I tried
CMax4.SaveAs Filename:="ftp://username:password@coffeeho.us/channelmax_javafly_ftp_" & Format(Now(), "yyyymmddHhNnSs") & ".txt", FileFormat:=xlTextWindows

but obviously that's too simple to work. Do I need to enable a reference library? Do I need to set paramiters before hand?

Maybe someone here can write me a snippet which can work? I've googled a lot and all the answers are above my paygrade.


thanks

Offline DanH

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Oct 2011
  • Posts: 2181
  • Total likes: 26
  • DansDeals.com Hat Tips 2
    • View Profile
Re: Excel Problem
« Reply #351 on: September 08, 2015, 05:24:02 PM »
How can I upload to an FTP location from VBA?

I tried
CMax4.SaveAs Filename:="ftp://username:password@coffeeho.us/channelmax_javafly_ftp_" & Format(Now(), "yyyymmddHhNnSs") & ".txt", FileFormat:=xlTextWindows

but obviously that's too simple to work. Do I need to enable a reference library? Do I need to set paramiters before hand?

Maybe someone here can write me a snippet which can work? I've googled a lot and all the answers are above my paygrade.


thanks
It's a bit annoying.  googling around can find you an answer.  it seems the easiest is external, i.e. to use winscp
For tech help feel free to Telegram me @DanTechSupp

Offline 1050BU

  • Dansdeals Gold Elite
  • ***
  • Join Date: Sep 2015
  • Posts: 154
  • Total likes: 1
  • DansDeals.com Hat Tips 1
    • View Profile
Re: Excel Problem
« Reply #352 on: October 22, 2015, 08:57:53 AM »
Is there a formal for excel to take one cell and search it against a list, and if it matches then copy the cell next to it?

Offline mancunian

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jul 2008
  • Posts: 1297
  • Total likes: 17
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
Re: Excel Problem
« Reply #353 on: October 22, 2015, 08:58:54 AM »
Match or index match

Offline yoruel

  • Dansdeals Platinum Elite + Lifetime Gold Elite
  • ******
  • Join Date: May 2013
  • Posts: 765
  • Total likes: 11
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: New York
Re: Excel Problem
« Reply #354 on: October 22, 2015, 09:03:12 AM »
Match or index match
or vlookup(lookup,hlookup)

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 3810
  • Total likes: 826
  • DansDeals.com Hat Tips 1
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Problem
« Reply #355 on: October 22, 2015, 10:04:15 AM »
or vlookup(lookup,hlookup)
INDEX/MATCH is cleaner and more effective for a number of reasons.

Is there a formal for excel to take one cell and search it against a list, and if it matches then copy the cell next to it?

If you can be more specific I can give you an example. Here's an example for what I assumed you were asking.

For example, if A1 is what you're looking for, column B (i'll use rows 1-20 as an example) is where you're looking to find it, and column c has the value you want to return, then the formula is:
=INDEX(C1:C20,MATCH(A1,A1:A20,0))

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7355
  • Total likes: 301
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #356 on: October 23, 2015, 10:57:35 AM »
Looking for a formula that would automatically update the date on a sheet from December 31 2014 to December 31 2015 at anytime the sheet is opened after x date.

Offline lubaby

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jun 2010
  • Posts: 5335
  • Total likes: 736
  • DansDeals.com Hat Tips 12
    • View Profile
Re: Excel Problem
« Reply #357 on: October 23, 2015, 11:11:45 AM »
Looking for a formula that would automatically update the date on a sheet from December 31 2014 to December 31 2015 at anytime the sheet is opened after x date.
=IF(TODAY() > 10/20/2015, "12/31/2014","12/31/2015")

May need to tweak it a bit, but that's the gist of it.

Offline myb821

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Mar 2011
  • Posts: 7570
  • Total likes: 28
  • DansDeals.com Hat Tips 2
    • View Profile
  • Location: New York
Re: Excel Problem
« Reply #358 on: October 23, 2015, 11:16:10 AM »
=IF(TODAY()>=DATE(2015,10,25),DATE(2015,12,31),DATE(2014,12,31))

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 7355
  • Total likes: 301
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Problem
« Reply #359 on: October 23, 2015, 11:17:28 AM »
Thanks.
Need this to roll forward every year though!