Topic Wiki

For cool Excel spreadsheet template to keep track of all Credit Card stuff, see here
« Last edited by shoobi on November 12, 2015, 11:24:07 PM »

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

Offline User6669

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2015
  • Posts: 2890
  • Total likes: 95
  • DansDeals.com Hat Tips 3
  • Gender: Male
    • View Profile
Re: Excel Help and Problems
« Reply #1125 on: January 24, 2018, 11:10:06 PM »
Is it possible to have the "number format" of a cell depend on a =IF?
".איזהו חכם, הלומד מכל אדם"

Offline aygart

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: May 2008
  • Posts: 5817
  • Total likes: 862
  • DansDeals.com Hat Tips 7
    • View Profile
    • Lower Watt Energy Brokers
  • Programs: www.lowerwatt.com
Re: Excel Help and Problems
« Reply #1126 on: January 24, 2018, 11:56:50 PM »
Is it possible to have the "number format" of a cell depend on a =IF?
Conditional formatting
You have the right to remain silent. Anything you say can and will be used to start a religious discussion.

Offline User6669

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2015
  • Posts: 2890
  • Total likes: 95
  • DansDeals.com Hat Tips 3
  • Gender: Male
    • View Profile
Re: Excel Help and Problems
« Reply #1127 on: January 25, 2018, 12:14:25 AM »
Conditional formatting
I tried... I'm looking to do, if A1="1" it's a Percentage, and if A1="2" then it's a Fraction.
« Last Edit: January 25, 2018, 12:17:30 AM by User6669 »
".איזהו חכם, הלומד מכל אדם"

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2565
  • Total likes: 70
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1128 on: January 25, 2018, 01:40:36 AM »
I tried... I'm looking to do, if A1="1" it's a Percentage, and if A1="2" then it's a Fraction.
Has to be 2 separate rules. One with =a1="1" as the formula and formatted as a percentage, and the other with =a1="2" as the formula and formatted as a fraction.

The conditions can be tricky though. If you can't get it to work, can post what the conditions are and I can't try to create the exact formula.

Offline User6669

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2015
  • Posts: 2890
  • Total likes: 95
  • DansDeals.com Hat Tips 3
  • Gender: Male
    • View Profile
Re: Excel Help and Problems
« Reply #1129 on: January 25, 2018, 10:28:43 PM »
Has to be 2 separate rules. One with =a1="1" as the formula and formatted as a percentage, and the other with =a1="2" as the formula and formatted as a fraction.

The conditions can be tricky though. If you can't get it to work, can post what the conditions are and I can't try to create the exact formula.

It needs to check the cell to the right, and format it based on what is says there. E.g. for B4 it should check B5, C4 = C5 etc.

 The condition is, =$B5="Fraction".

The cell value is correct, it just doesn't display the correct format.
".איזהו חכם, הלומד מכל אדם"

Offline skyguy918

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Mar 2011
  • Posts: 2565
  • Total likes: 70
  • DansDeals.com Hat Tips 0
  • Gender: Male
    • View Profile
  • Location: Queens, NY
Re: Excel Help and Problems
« Reply #1130 on: January 25, 2018, 11:56:53 PM »
It needs to check the cell to the right, and format it based on what is says there. E.g. for B4 it should check B5, C4 = C5 etc.

 The condition is, =$B5="Fraction".

The cell value is correct, it just doesn't display the correct format.
Are you saying you got it to work? Or you're describing what you did and it's still not working?

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 8951
  • Total likes: 209
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1131 on: January 26, 2018, 12:14:26 AM »
You can use another column with a function referring to the previous 2 columns.

Like this:
Code: [Select]
=IF(A1="fraction",TEXT(B1,"# ?/?"),TEXT(B1,"0%"))
Where A is the column that  says "fraction" or "percent", and B is the column with the number in it.

This link will give you more instructions in case you want to modify the number formats (ie more decimal places etc) https://support.office.com/en-us/article/TEXT-function-20d5ac4d-7b94-49fd-bb38-93d29371225c
« Last Edit: January 26, 2018, 12:37:30 AM by etech0 »
Workflowy. You won't know what you're missing until you try it.

Offline jose34

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Aug 2016
  • Posts: 414
  • Total likes: 14
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Help and Problems
« Reply #1132 on: February 11, 2018, 05:22:05 PM »
Someone asked me to find all the people that attend events over the course of the year and how many events, they just need numbers not the people names.
Is their anyway to put it the data in the Excel and have excel do the work for me?


Offline lubaby

  • Dansdeals Presidential Platinum Elite
  • ********
  • Join Date: Jun 2010
  • Posts: 3901
  • Total likes: 118
  • DansDeals.com Hat Tips 12
  • Gender: Male
    • View Profile
  • Location: New York
  • Programs: Hertz PC, National EE
Re: Excel Help and Problems
« Reply #1133 on: February 11, 2018, 05:26:28 PM »
Someone asked me to find all the people that attend events over the course of the year and how many events, they just need numbers not the people names.
Is their anyway to put it the data in the Excel and have excel do the work for me?
Sure, wanna post some sample data so we can take a look? Should be a pretty simple formula..
Once the game is over, the king and the pawn go back in the same box.

Offline jose34

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Aug 2016
  • Posts: 414
  • Total likes: 14
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Help and Problems
« Reply #1134 on: February 11, 2018, 05:33:52 PM »
                        
Something like in this in a Excel file, This sample is only four names per column put I am dealing with like 20 events of 20- 100 people each.


   5-Apr         6-May         7-Jun   
                        
   Jack    Mitchel      Yehuda    Haim      Avi   Yakubov
   Kathrine    Katz      Lary    Yeuda      Chevy   Katz
   Ron    Mathew      Mitch    Paul      Paul   Loe
   Yehuda    Haim      Pat    King      Mitch   Paul

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 6829
  • Total likes: 148
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Help and Problems
« Reply #1135 on: February 11, 2018, 06:30:17 PM »

Something like in this in a Excel file, This sample is only four names per column put I am dealing with like 20 events of 20- 100 people each.


5-Apr6-May7-Jun

Jack MitchelYehuda HaimAviYakubov
Kathrine KatzLary YeudaChevyKatz
Ron MathewMitch PaulPaulLoe
Yehuda HaimPat KingMitchPaul
First make a unique list per event by copying and  removing dup's, then Countif(a2:a100,"jack") ?

Offline Centurion

  • Dansdeals Lifetime Platinum Elite
  • *******
  • Join Date: Jun 2012
  • Posts: 1296
  • Total likes: 2
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: New York / Jerusalem
  • Programs: spg plat,avis pres ,hertz plat ,AAexcutime, delta plat,hilton gold, united
Re: Excel Help and Problems
« Reply #1136 on: February 20, 2018, 05:35:26 PM »
having a hard time copying hyperlinks from one book to another
it looks good ( blue and underlined) but the only way to create hyperlink is by hitting enter twice on each line ( hundreds of lines)
https://www.americanexpress.com/ ( it creates a link here but in excel it doesnt)
first its black then i can make it blue and underline.

thank you
you can only make a first impression ONCE

Offline churnbabychurn

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Jul 2012
  • Posts: 6829
  • Total likes: 148
  • DansDeals.com Hat Tips 0
    • View Profile
  • Location: Lakewood
Re: Excel Help and Problems
« Reply #1137 on: February 20, 2018, 06:04:32 PM »
having a hard time copying hyperlinks from one book to another
it looks good ( blue and underlined) but the only way to create hyperlink is by hitting enter twice on each line ( hundreds of lines)
https://www.americanexpress.com/ ( it creates a link here but in excel it doesnt)
first its black then i can make it blue and underline.

thank you
You need to refresh all cells in the column. (same as double clicking each).

To do this select the column and do a text to column function. Set the the delimitor as nothing so it won't actually shift. Just refresh in place.

Offline MasterAmex

  • Dansdeals Platinum Elite
  • ****
  • Join Date: Dec 2015
  • Posts: 280
  • Total likes: 4
  • DansDeals.com Hat Tips 0
    • View Profile
Re: Excel Help and Problems
« Reply #1138 on: February 21, 2018, 03:00:31 PM »
is there a separate thread for Google sheets, or its a "all in one"?

Offline etech0

  • Dansdeals Lifetime Presidential Platinum Elite
  • *********
  • Join Date: Dec 2013
  • Posts: 8951
  • Total likes: 209
  • DansDeals.com Hat Tips 1
    • View Profile
  • Location: not lakewood
  • Programs: DDF
Re: Excel Help and Problems
« Reply #1139 on: February 21, 2018, 03:10:03 PM »
is there a separate thread for Google sheets, or its a "all in one"?
Google Sheets help
Workflowy. You won't know what you're missing until you try it.