Convert cell addresses in JSON format

vilas desai
3StarLounger
Posts: 307
Joined: 16 Mar 2011, 09:33

Convert cell addresses in JSON format

Post by vilas desai »

Hello Experts,

Please see the attachment for reference. Sheet 2: In Col C, I have names for "parameter",
in Range D2:P2, I have names of "files" and in Range D3:P40 I have cell addresses.
What I would like to achieve is in sheet 1 as an example.

Please help
Best regards
Vilas Desai
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Convert cell addresses in JSON format

Post by HansV »

I'm afraid I don't even begin to understand what you want.
Best wishes,
Hans

vilas desai
3StarLounger
Posts: 307
Joined: 16 Mar 2011, 09:33

Re: Convert cell addresses in JSON format

Post by vilas desai »

Ok, sorry about that, Hans
Let me try again please.

The important objective besides other, is to convert cell addresses in the "M14" format to "row":14,"column":13 format

There are 40 parameters arranged in col C3:C40 (Fluid Name, Fluid Phase,.......Pressure Loss @ Meter Min Flow)
There are workbook names in range D2:P2 (CVBLV, CVBFY,........Transmitters)
So this is like a 2D table. Parameter Vs File names.

in cell D2 of the worksheet 2, we have address P13 that corresponds to Parameter=Fluid Name in the file CVBLV.
Below is how I want to describe this situation.

in worksheet 1,
cell A2 = file name, in this case, it is CVBLV
Cell B2 = [{"parameter":"Fluid Name","row":13,"column":16}] meaning "parameter" by name "Fluid Name" has a value in cell P13 of workbook CVBLV

If I could explain this successfully then I will continue with the rest, which is o same lines.

Best regards
Vilas Desai

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Convert cell addresses in JSON format

Post by HansV »

One of the reasons I didn't understand is that Sheet2!C4 contains "Fluid Name" but on Sheet1, you use "Fluid Process". Etc.
I now assume that was misleading.
See Sheet3 in the attached version.

CommonFieldsInDS_R1.xlsx
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

vilas desai
3StarLounger
Posts: 307
Joined: 16 Mar 2011, 09:33

Re: Convert cell addresses in JSON format

Post by vilas desai »

My Bad. Apologies.

FYI, when I do "Enable Editing" i get an error #Name? So the formula is getting corrupted. Anything wrong I am doing Hans. Thanks

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Convert cell addresses in JSON format

Post by HansV »

Which version of Excel are you using?
Best wishes,
Hans

vilas desai
3StarLounger
Posts: 307
Joined: 16 Mar 2011, 09:33

Re: Convert cell addresses in JSON format

Post by vilas desai »

Excel 2016.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Convert cell addresses in JSON format

Post by HansV »

Is a VBA solution OK?
Best wishes,
Hans

vilas desai
3StarLounger
Posts: 307
Joined: 16 Mar 2011, 09:33

Re: Convert cell addresses in JSON format

Post by vilas desai »

Yes no Problem

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Convert cell addresses in JSON format

Post by HansV »

See the attached version. It is now a macro-enabled workbook (.xlsm)

CommonFieldsInDS_R1.xlsm
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

vilas desai
3StarLounger
Posts: 307
Joined: 16 Mar 2011, 09:33

Re: Convert cell addresses in JSON format

Post by vilas desai »

Thanks a lot, Hans

vilas desai
3StarLounger
Posts: 307
Joined: 16 Mar 2011, 09:33

Re: Convert cell addresses in JSON format

Post by vilas desai »

Hello Hans,

I was trying to use the above file and stuck at two places.
1. n sheet 1, Cell B2, I dont see the end brackets ... Flower / Square both.
2. How do I run the macro after making changes in sheet 2?

Please advise'
Best regards
Vilas Desai

vilas desai
3StarLounger
Posts: 307
Joined: 16 Mar 2011, 09:33

Re: Convert cell addresses in JSON format

Post by vilas desai »

Please ignore point 1

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Convert cell addresses in JSON format

Post by HansV »

1. The end brackets should be present, but Excel may not display all characters unless you turn on Wrap Text.
2. Insert the following line below Function GetParams(Param As String) As String:

Code: Select all

    Application.Volatile
The results in column B on Sheet1 should now be updated automatically if you change something on Sheet2.
Best wishes,
Hans