Dear All,
Good Evening!!!!
I want to know whether it is possible to refer a cell value for a certain range
Ex: I want to populate A5 cell value in the range B5 to B38 & A35 cell value for a range from B35 to B73
Dear All,
Good Evening!!!!
I want to know whether it is possible to refer a cell value for a certain range
Ex: I want to populate A5 cell value in the range B5 to B38 & A35 cell value for a range from B35 to B73
Please Login or Register to view this content.
Cheers!
Deep Dave
Thanks for your reply.
Can we have a formula for this. Just with a curiosity
You will have to select the range B5:B38, then use the formula =A5 and hit Ctrl+Enter
Its populating the values beside these cells ex: B5=A5, B6=A6 etc?
These cells should stick to that particular cell. Can we don this?
I am sorry, the formula should have been =$A$5 not just =A5
So B35 to B73 also will be populated with the same value which is in A5 rather than A35
How could we achieve this? Any formula will be appreciated please.
You will have to select the range B5:B38, then use the formula =$A$5 and hit Ctrl+Enter
You will have to select the range B35:B73, then use the formula =$A$35 and hit Ctrl+Enter
Thanks for your inputs. But I dont want to do that manually or with VBA because I have already set the formula in my report in which this is the only thing I'm doing manually which I want to achieve with formula but couldnt able to find the exact formula which do this.
I am not able to understand you requirement..
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
I've attached the file in which A1 will have Location1, A2:A22 will have models beside which I want location name to be refered for this range, A23 will have location2, A24:A44 will have same models which are under location1 beside which I want to refer location2
Issue is I just want to get this refered with formula with a simple drag & I'm not aware of VBA I need it with formula
Put this in B2 and drag down
=IF(LEFT(A2,8)="Location","Y",INDEX($A$1:$A$44,MAX(IF(ROW(A2)>IF(LEFT($A$1:$A$44,8)="Location",ROW($A$1:$A$44),""),IF(LEFT($A$1:$A$44,8)="Location",ROW($A$1:$A$44),""),""))))
Or
=IF(LEFT(A2,8)="Location",CHAR(87+COUNTIF($A$1:A2,"Location*")),INDEX($A$1:$A$44,SMALL(IF(LEFT($A$1:$A$44,8)="Location",ROW($A$1:$A$44),""),COUNTIF($A$1:A2,"Location*"))))
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Last edited by NeedForExcel; 11-20-2015 at 02:22 AM.
Thanks for your time!!! I understood that this can be done with formula
Please refer below link in which I've an attached file in which I need like this.
http://www.excelforum.com/excel-form...f-columns.html
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks