LinkBack URL
About LinkBacks
I wish to rename in formula, for example
=IF(Jan!$A1="","",(Jan!$A$2))
I wish to rename Jan to Feb, Mar, Apr...and so on!
Please help.
Thank you.
I wish to rename in formula, for example
=IF(Jan!$A1="","",(Jan!$A$2))
I wish to rename Jan to Feb, Mar, Apr...and so on!
Please help.
Thank you.
Depends what you mean by 'and so on' and how you are operating with this. At face value a simple Find & Replace will achieve the request as stated.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Thanks.
I got it working....by using
=IF(ISBLANK(Jan!$C2),"",Jan!C2)
Is it possible to copy past / rename the formula?
I would like to replace Jan to Feb / Mar / Apr ...so on.
Thanks
If I am using a find and replace, it replaces all Jan to Feb. The formulas are for a month example Jan, then
same for Feb, Mar, Apr...until Dec.
Sorry you've lost me. I don't understand.
Upload an example workbook that shows examples of all permutations of what you start with and what you want to end up with.
do you want to rename of copy to a different cell and pickup the different months
otherwise
you could use a reference cell with JAN in say sheet1!A1
and then use an indirect
=IF(INDIRECT(Sheet1!A1&"!$A1")="","",(INDIRECT(Sheet1!A1&"!$A2")))
now change sheet1!a1 to Jan
see attached
the indirect function will
look in sheet1!A1 and see whats in the cell
and then use that to create the rest of the formula
INDIRECT(Sheet1!A1&"!$A1")
if sheet1!A1 has the word
Sheet2
in it now
indirect would be
INDIRECT(Sheet2&"!$A1")
and when joined becomes
Sheet2!$A1
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Copy down:
=IF(INDIRECT(TEXT(ROWS($A$1:A1)&"/2017","MMM")&"!$A1")="","",INDIRECT(TEXT(ROWS($A$1:A1)&"/2017","MMM")&"!$A2"))
Copy across:
=IF(INDIRECT(TEXT(COLUMNS($A$1:A1)&"/2017","MMM")&"!$A1")="","",INDIRECT(TEXT(COLUMNS($A$1:A1)&"/2017","MMM")&"!$A2"))
Thank you everyone for your help.
I am really very sorry, may be I didn't explain my requirement
clearly, my English is not good.
I have a total of 13 sheets, renamed to Jan, Feb, Mar...upto Dec,
Every sheet has dates from 1st till end of the month.
13th sheet is renamed to Account.
From all 12 sheets (Jan to Dec)...I use "=A1" etc...to get all the data in account sheet,
I have been able to do all that I want to. The only tiresome work is to copy formulas on
the Account sheet. For example in Account sheet I have
"=IF(ISBLANK(Jan!$C2),"",Jan!C2)" ....this formula is in all days in Jan, Feb, Mar..till Dec.
In Account sheet - I would like to copy "=IF(ISBLANK(Jan!$C2),"",Jan!C2)" to "=IF(ISBLANK(Feb!$C2),"",Feb!C2)".
Find and replace is not helpful, I am sure there must be some way to do it.
Please help.
Thank you.
you have 12 sheets jan to dec
on the account sheet - dont you want all the jan-dec infor
or
just 1 month that you can choose
you say
my formula will do thatI would like to copy "=IF(ISBLANK(Jan!$C2),"",Jan!C2)" to "=IF(ISBLANK(Feb!$C2),"",Feb!C2)".
on account sheet in a spare cell , all you need to do is type the month
jan or jul or dec
and the formula will goto that sheet and bring back whatever is in a2 on that sheet
if thats all you want
=IF(INDIRECT(accountsheet!A1&"!$A1")="","",(INDIRECT(accountsheet!A1&"!$A2")))
now in accountsheet cell a1
you can put any month
say mar
and it will goto mar sheet
otherwise
Please upload a small clean sample of your data / workbook (not a picture) to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
We would like to see an example of your data and also a manual mock up of the expected results you want to achieve.
To attach a file to your post,
click "Go advanced" (next to quick post),
scroll down until you see "manage Attachments",
click that and select "Choose File" (top Left corner).
Find your file, click "Open" click "upload" click 'close windows" Top Right. click "Submit Reply"
Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.
Last edited by etaf; 09-23-2017 at 05:13 AM.
Going forward you should seriously consider keeping all your data on a single sheet in a normalised 2 dimensional database range.
One of your columns in this database would of course be a date column which will allow you to summarise/analyse your data by month (and much else) with a Pivot Table.
It is rarely a good idea to spread data (like months, departments, staff, products and many other groups of things which may at first thought seem a natural thing to do), across many sheets.
You are right. I am seriously thinking of making only 2 sheets (1 for data and another for account).
I will post with new issues that I may across. Thank you for your advice, highly appreciate.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks