LinkBack URL
About LinkBacks
i have two cells, which should contain same value but one in text format another in fraction format. Only then excel sheet works, otherwise part of the excel sheet doesn't work. I want only one cell to be drop down in among those two. Please help
i have two cells, which should contain same value but one in text format another in fraction format. Only then excel sheet works, otherwise part of the excel sheet doesn't work. I want only one cell to be drop down in among those two. Please help
you really have to attach a sample as per the yellow banner., and say what you are expecting. It is very unclear. What does "then only excel sheet works" mean? Is there a formula that is not evaluating?
Yes it doesn't evaluate a formula if both cells are in same Number format, they have to be in different formats.Attachment 843781 PFA
PLease see yellow banner at top of page on how to attach a workbook ... with examples of expected results.
Last edited by JohnTopley; 09-25-2023 at 05:54 AM.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
You were asked for a WORKBOOK, not a picture.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
in 'formulas from handbook' sheet , the cells M1 and Q1 should be same value everytime, so essentially i need to put drop down in only one cell to control both cells, but i want them to be outputting in different format, M1 in 'text' format and Q1 in 'fraction' format, i mean number format. I've attached the file, please go through and help.
Thanks to whoever has helped and will be helping
Administrative Note:
We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so. Cross-posts are allowed but you must provide a link to your posts on other sites.
Please see Forum Rule #7 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/
(Note: this requirement is not optional. As you are new here, I shall do it for you this time: https://www.mrexcel.com/board/thread...ormat.1245756/)
Sorry but I don't undrrstand the issue: a drop-down in M will get the corresponding value in Q based on formula in Q
sorry i posted in the mrexcel link as well.
the right side of the sheet does some calculations based on the value in Q1, i want the same calculations done based on the value in M1(same value needed to be in different number format for the sheet to work., because i want same value in both M1 and Q1, i don't want to use two drop downs but only one.
in other words, if i change the drop-down in M1 , i want the drop-down in Q1 also change correspondingly, but i want them to be in different number formats. M1 in text format , Q1 in fraction format.
See sheet1: is this what you want?
Select from K1 and you get correct data validation in M and Q
Using Named Ranges
OR
=IFERROR(VLOOKUP($M2,INDIRECT($K$1&"_tbl"),5,0),"not found")
Last edited by JohnTopley; 09-25-2023 at 11:41 AM.
you got little bit close, the problem is in M2 and Q4, I want the same value, not inverse, by selecting only one drop down
and if i paste you formula in 365 online web application, it doesn't work, it shows 'not found' regardless of which drop down i select.
I do not understand: with UNC, select 4.5 in M2 and the Q4 equivalent from your table is 2/9 Using VLOOKUP - why do you say inverse ?: TELL us what you expect the Q result to be.
I have no knowledge of 365 in any form so I cannot help on 365 Online issue.
The Q4 should be same 4.5 as in m2, but in different number format, which is 9/2
if i used "=INDIRECT($K$1 &"_M")" in data validation, in the drop-down I am getting content from column D, but I want to get content from column B. Please help , thanks
Look at the named ranges e,g, "UNC_M" and change as required.
i don't understand, where to look for the named ranges? in data validation or in the sheet? if so where?
Formulas ribbon > Name Manager.
Thanks, i found it, But even after all this it still doesn't fix my original initial problem.
Then I think you are stuck because you have so far failed to communicate to us exactly what you want. I am still not clear at all.
1st problem: to forums1.xlsx in this attachment, in "formulas from handbook" sheet, in Q1 if i select from drop down "1/4", and in AG5 i want sqrt(Q1), i am getting sqrt of 45017 instead, what is causing this problem? i want only sqrt of 1/4, not 45017. Similarly if i select 5/16 from drop-down, i'm getting sqrt(42491) instead. Please help and thanks in advance.
2nd problem: right now i have M1 and Q1 as inputs which is in total two inputs. Since i want in both M1 and Q1 same value i want to control both the cells by inputting only M1(only one cell). i just want them to be in different "Number Format" ie if you right click on the cell, it shows format cells right , that number format, one in text and other one in fraction. If this is still not clear , please let me know.
So if someone selects 1 in M1, you want Q1 automatically to change to 1 as well - is that it? If so, then why have a drop-down at all in Q1? Remove it and put the formula =M1 instead.
I can do that easily, but i want them to be in different number format, and how about the 1st problem i asked?
I'm not interested in the first problem - that wasn't the query you opened this thread for, and really needs a thread of its own with a suitable title.
The two cells cannot be identical and in a different number format at the same time. You still have not shown us what you want. Mock up a very simple workbook with a set of scenerios (maybe four or five) that show how you want the two cells to interact with each other as data in the feeder cell changes. You are saying the same thing over and over again - repeating the same thing won't make us understand it any better, no matter how loudly you say it! So SHOW us.
Unless you provide a clear visual representation of what you want, you are highly unlikely to get any resolution to this issue.
First problem is because Excel is treating 1/4 as a date (01/04/2023) as is 5/16 (01/05/2016)
To resolve, add column C in "List2" with decimal equivalent of the value in B: so 1/4 = 0.25, 5/16 =0.3125 etc and then use a VLOOKUP or equivalent to get the value from column C based on the value in Q1.
I gave you an anwswer to the M1/Q1 problem in post #13 using a "lookup" formula (as per above).
Last edited by JohnTopley; 09-30-2023 at 05:04 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks