In numbers, is there a way to use the column A cells from the table with color to create a drop down in a cell in the b&w table so that the data in the other cells (the watts @ STC for example) in the color table will populate into a third table?
Pages-OTHER, Mac OS X (10.7.5)
Not possible with Numbers without some fancy scripting. You can provide feedback to Apple by selecting the menu item "Numbers > Provide Numbers Feedback"
Thank you. Can you help me with an If/Then on this? If the cell says CSI then I need to see if the cell next to it says 245 or 250 and then populate the cell in another table with a field from the colored table above....Right now there are three manufacturer options and a total of 4 model options to populate. I will need to get this one down because each option has six data fields that need the If/Then.
I'm sure that my explanation is not as clear as it could be....do you have the option to chat?
Many thanks!!
Nope. No clear to me. 😉
the if function is simple by itself so let's start there:
the function is is:
i(<condition>, <return when true>, <return when false>)
you can enter an if function in a cell by typing "=" then "if("
after that you need to know what is the boolean condition you want to test... that's what an "if" is you cehck to see if some condition which can be true or false is true.
so to see if a cell contains "CSI" you would enter:
"=if(A1="CSI", "YES", "NO")
this function would return "YES" when a cell contains the exact text "CSI" otherwise it returns "NO"
Elissa,
Please try stating exactly what you want rather than trying to say how you want us to solve the problem. This may work better as I don't know what "the problem" is. Also you must be very descriptive when refering to tables... for example saying:
cell in another table with a field from the colored table above
is ambiguous, to me, becuase both tables are colored and both are actually above the post.... or do you mean the top table in the image you posted. You could be clearer indicating the "green" or "orange" table.
Here goes: in the Orange table are a list of manufacturers. Each manufacturer has at least one option. I need to be able to populate 1 cell in a table 4 (not shown) with data form column 3 (model number), then based on that model number I will need to populate 4 cells in table 4 with the data from the orange table in columns 5, 6, 7, 8 (Isc, Imp, Voc and Vmp)
So my thinking so far with what I learned last Friday (I don't know how to insert "and")is this: =IF(A1 =CSI and B1 =245, C1, IF(A1 =CSI and B1 =250, C1 etc...)) Is this getting close?
Thanks so much!!!
Elissa
Elissa,
The syntax for the AND function is:
AND(test-expression, test-expression…)
In your expression, you would write:
=IF(AND(A1 =CSI, B1 =2450, C1, IF(AND(A1 =CSI,B1 =250), C1 etc...))
You can find this answer yourself by opening the Function Browser. You can open it with View > Show Function Browser, or by clicking on a cell in the table then choosing Show Function Browser from the Function Icon on the Toolbar, or by clicking on the Function Browser Icon on the Format Bar after typing an equal sign in a cell.
Regards,
Jerry
Hi Elissa,
This looks like a classic LOOKUP situation.
Here's a skeleton view of the three tables involved.
Table 1 is your input table, with values selected in B3 and C3.
Panels is your orange-striped table, with location labels (eg. C4) used in lieu of re-typing the actual data.
Table 4 represents a set of cells in your Table 4, into which the requested data is rtransferred.
Panels is treated as a Lookup table, with the identifiers in column A as the search values.
Table 4::A2 contains the formula below:
=VLOOKUP(Table 1 :: $B3&" "&Table 1 :: $C3,Panels :: $A:$G,COLUMN()+2,0)
Syntax: VLOOKUP(search-for,search-where,result-column,match type)
Table 1 :: $B3&" "&Table 1 :: $C3 constructs the search-for string by concatenating the contents of Table 1::B3, a single space, and the contents of Table 1::C3. Result: "CSI 255"
Panels :: $A:$G tell Numbers the lookup table is columns a through G of Panels, Numbers searches for "CSI 255" in the firt column of the Lookup table.
COLUMN()+2 COLUMN() returns the number of the column containing the formula (1), +2 adds 2 to this value to tell the formula to return the value from the third column of the Lookup table (column C). As the formula is filled right, the result of Column increases, and the formula continues to return results from thecorrect column.
0 (or FALSE) means don't accept a 'close-match. If the exact search-for value is not found, the formula will return an error message.
To use the eror message, the whole formula is enclosed in and IFERROR statement:
=IFERROR(VLOOKUP(Table 1 :: $B3&" "&Table 1 :: $C3,Panels :: $A:$G,COLUMN()+2,0),"---")
The results of this version may be seen in Row 3 of Table 4, where the formula is looking for the values in B4 and C4 of Table 1.
Regards,
Barry
Elissa,
I just noticed that I left out a right parenthesis, just after the "2450". I should have written:
In your expression, you would write:
=IF(AND(A1 =CSI, B1 =2450), C1, IF(AND(A1 =CSI,B1 =250), C1 etc...))
But, I think you got the gist of it.
I wasn's sure what "CSI" means, but I figure you do. It should be either a cell address or a literal string, in quotes, or a value.
Jerry
I wasn's sure what "CSI" means, but I figure you do. It should be either a cell address or a literal string, in quotes, or a value.
Crime Scene Investigation ( 😉 )... or this:
That made all the difference, Jerry....I had fortunatly figured it out. I did look at the function dropdown and explanations, it was just a bit more complicated that was addressed in the numbers help and I had done a bit of research online as well. I just had to build a framework to put the information in, having never used a function like this before. Thank you for all your help!
Haha, Wayne....yes, it's the abbreviation for Canadian Solar....I really appreciate all your help. I got through the first one and then had constructed a good framework of understanding in my head so that I could move forward with the rest of them very quickly....Your help was integral to my understanding!
Thanks Barry, since the original tables are preconstructed and are already layers and layers deep, using a lookup didn't seem like it would work in this situation...
I am back to needing to use the VLookup function and can't figure it out....here's what I'm trying to do:
Let's say there are 4 tables:
1. Input
2. Cover
3. Data
4. Output
Table 1 has two options. In Table 2 an option is chosen using a pop-up menu. The data to go in cell A1 in table 4 will come from Table 3 --which data depends on which option is chosen in Table 1.
Any help on how to use lookup here would be most appreciated!
Elissa
-
Hi Elissa,
Your scenario seems pretty similar to that described in my earlier post.
1. Input —> Table 1
3. Data —> Panels is the data table.
4. Output —> Table 4
2. Cover —> no equivalent.
(From your current description, Cover does not appear to be involved in the process of choosing which data to display.)
Would a re-reading of that post lead to more specific questions?
Regards,
Barry
Hi Barry,
I am really needing some help with this lookup function. I have a table called the Sale Cover Sheet. There are two cells with information in them that will determine the data that goes into the cell in the Data Sheet. How do I use a lookup to place data (from the Data Sheet) in a cell in the Circuit Sheet based on both data points in the sale cover sheet?
Here is a visual:
Sale Cover Sheet..the two cells with the input data show"CSI" and "245" (don't know why this is bold and large...sorry)
Data Sheet: I will need to populate 4 cells with the data Isc, Imp, Voc and Vmp
This is the circuit sheet that has data in it now from If/Then statements, but I must convert to Lookup functions. There are multiple different scenarios where I need to create these Lookups for this spreadsheet and it's definitely complex. Any help is much appreciated!
Elissa
Use cells from another table in a Drop Down Menu
