Two questions : named range display & alternate cell

[shahab47]

Hi Guys

I have a table assigned as a NAMED RANGE called "my_table". How do I display this in a cell, using certain formula? ie. I want a cell to display the text "my_table" if I write certain formula in it. see attached spreadsheet, task#2.

Another question.
How do I select alternate cell if they are named ODD/EVEN as explained in attached spreadsheet.

I have spent quite a few days trying to get to these but no luck.

Any help would be much appreciated. I have attached the spreadsheet with two tabs. task#1 and task#2.

Thanks
Shahab

[Richard Buttrey]

Hi and welcome to the forum.
Is the Task1 table truly representative?

Does the count of all the values in the rows always alternate between 4 & 5 and always offset by one column?
Is the table always a 4 x 9 matrix
Are the characters in column A always and only present in each row. i.e. does 'A' only appear in row 3
Are the characters in row 2 always and only present in each column. i.e. does the 2 in C2 ' only appear in column C

[shahab47]

THanks Richard.

actual table is pretty big. I have attached the pdf. please check page-2 as well. that should answer all your questions.
headers are fixed. A/B/C/D (be aware, some letter are missing like I etc and 1/2/3/4)


only grey cells need to be populated.
Also, the text I mentioned in column-D ie the text "Pin-A2/Pin-A4 etc"..... this text doesnt need to say A2/A4 in it. it can be any plain text. just the text belonging to column C grid should show up in corr column-D. ie if cell C3 = sample, then cell D9 should dispaly "sample" as well.
the text in column C has to be "exactly" as written in the spreadsheet.
hopefully I answer all your questions to help me get an answer.


Shahab

[Richard Buttrey]

Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context. Pictures are rarely any use.

Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.

Pease also note that you should not use PMs to prompt for action. It is against our rules.

[shahab47]

let me try this again...updated the spreadsheet with some more notes. hopefully this would be clear. appreciate any inputs. Thanks.

Shahab

[JeteMc]

As to task 1 paste the following into cell M3 and drag the fill handle down to M38:

Please Login or Register  to view this content.

Paste the following array formula** into cell N3, follow the instructions below for activating, and then double click the fill handle to copy the formula down:

Please Login or Register  to view this content.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

As for task 2 paste the following into cell B13 and then drag the fill handle down:

Please Login or Register  to view this content.

Let us know if you have any questions.

[shahab47]

Hi JeteMc

Thanks for the formulas. This is awesome.

Task#1 works. But I just realized that if I "repeat" the text in the table, the column-M does not work as expected. for example, if I use the text "bla" in both cells C3 and E3,then the pin number for both texts becomes A2. I have attached updated spreadsheet. I didnt realize this to mention earlier. I apologize for inconvenience.

For Task#2, one section works, other doesnt as it seems I didnt specify my query correctly. I wanted to populate the name of the range as well, using some formula, not as a text. I have updated task#2 spreadsheet as well to get a bigger picture.

Would appreciate if you could help me take it to the close. See updated spreadsheet attached.

Thanks
Shahab

[JeteMc]

As to task #1 try the following:
For cell P2 and down:

Please Login or Register  to view this content.

For cell M2 and down:

Please Login or Register  to view this content.

Note that these are regular formulas, just press enter to activate.
I don't have any ideas about task #2, however I'll look back later to see if someone else has resolved it.
Let us know if you have any questions.

[shahab47]

Hi JeteMc

Thanks again...this is great...one last thing about task#1. I am trying to extend the X-axis upto 155 and Y-axis upto 89 entries as shown in attached xls file. I tried to tweak with the formula to extend your range but am unable to do so. If you can help me extend this range in the formula, that would be great. see attached spreadsheet to show the extended range.

Regarding Task#2, I stumbled across this thread. Here the named range was captured somehow. "not exactly" what I want, but maybe that would give a clue? It is using the VBA. I think if there is no other way to do this using just a formula, then I might be ok using VBA code for this, if that helps in finding a solution.
#post5457547 in this excel forum (system wont let me add the URL)
This is the title of the thread : display the name of a named range in a cell of the range itself


Appreciate your help.
Thanks
Shahab

[JeteMc]

Task #1
For cells B100 and down:

Please Login or Register  to view this content.

For cells C100 and down:

Please Login or Register  to view this content.

I don't know VBA so I can't help you there. You may want to ask one of the moderators, I see Richard is a participant in this thread, how to proceed to get help from the VBA contributors.
Let us know if you have any questions.

[shahab47]

Hi JeteMc.

Thanks for getting back to me in quick time. Column-C works perfect all the way up to the last entry. If I depopulate any user entry, it doesnt show up in the formula, which is good and a bonus.

But Column-B is showing up characters I think based on the formula, Is it possible for Column-B to show the contents of "column-A of the entire big table" instead of automatically assigning "next character" value?
X- axis in my spreadsheet goes from 1 to 155 without any jump in between.
But Y-axis has some jumps...like it goes from H to J (jumps "I") and similarly it goes from N to P (jumps "O") and then jumps "S" and "X" and "Z".
And after Z, it starts over from AA to AY....with similar jumps...and then BA to BY and CA to CY and so on....

Appreciate all your help.
Thank you.
Shahab

[JeteMc]

Try pasting the following into cell B100 and copying down to cell B6907:

Please Login or Register  to view this content.

Let us know if you have any questions.

[shahab47]

Thanks JeteMc for quick responses...this does it....Task#1 resolved. I cannot thank you enough for helping me resolve this.

I will wait to see if anybody else can help me with Task#2 here.

Shahab